この章では、Oracleメモリー・キャッシュにメモリーを割り当てる方法とこれらのキャッシュの使用方法について説明します。Oracleメモリー・キャッシュを適切にサイズ設定して効率的に使用すると、データベースのパフォーマンスが大幅に向上します。システムのメモリーは、自動メモリー管理を使用して管理することをお薦めします。ただし、システムのメモリー・プールは、この章の手順に従って手動で調整できます。
この章には次の項があります。
関連項目: Oracleデータベースのメモリー・アーキテクチャの詳細は、『Oracle Database概要』を参照してください。 |
Oracleはメモリー・キャッシュ内およびディスク上に情報を格納します。メモリー・アクセスは、ディスク・アクセスよりはるかに高速です。ディスク・アクセス(物理I/O)は、メモリー・アクセスに比べ、時間がかかります(通常は約10ミリ秒)。また、物理I/Oでは、デバイス・ドライバやオペレーティング・システムのイベント・スケジューラのパス長のために必要なCPUリソースも増加します。このため、頻繁にアクセスされるオブジェクトに対するデータ・リクエストは、ディスク・アクセスではなくメモリー・アクセスで実行するほうが効率的です。
パフォーマンスの目標は、必要なデータがメモリー内にある可能性を高くしたり、必要なデータを取り出すプロセスをさらに効率的にし、できるだけ多くの物理I/Oオーバーヘッドを削減することです。
この項では、次の項目について説明します。
パフォーマンスに影響を与える主なOracleメモリー・キャッシュは次のとおりです。
共有プール
ラージ・プール
Javaプール
バッファ・キャッシュ
ストリーム・プール・サイズ
ログ・バッファ
プロセス・プライベート・メモリー(ソートやハッシュ結合に使用されるメモリーなど)
システムのメモリーは、自動メモリー管理を使用して管理することを強くお薦めします。自動メモリー管理により、Oracle Databaseでは、インスタンス・メモリーを自動的に管理およびチューニングできます。自動メモリー管理は、ターゲット・メモリー・サイズ初期化パラメータ(MEMORY_TARGET
)と最大メモリー・サイズ初期化パラメータ(MEMORY_MAX_TARGET
)を使用して構成します。Oracle Databaseでは、システム・グローバル領域(SGA)とインスタンス・プログラム・グローバル領域(インスタンスPGA)の間で必要に応じてメモリーを再分配し、ターゲット・メモリー・サイズに合ったチューニングを行います。任意のメモリー・プール・サイズを設定する前に、Oracle Databaseの自動メモリー管理機能の使用を検討してください。メモリー割当てを構成する必要がある場合は、メモリー・アドバイザを使用したメモリー管理も検討してください。
関連項目:
|
自動共有メモリー管理を使用すると、SGAの構成が簡単になります。自動共有メモリー管理を使用するには、SGA_TARGET
初期化パラメータをゼロ以外の値に設定し、STATISTICS_LEVEL
初期化パラメータをTYPICAL
またはALL
に設定します。SGA_TARGET
パラメータの値は、SGA専用にするメモリーの容量に設定する必要があります。自動SGA管理では、システム上のワークロードに応じて次のメモリー・プールにメモリーが適切に配分されます。
データベース・バッファ・キャッシュ(デフォルト・プール)
共有プール
ラージ・プール
Javaプール
ストリーム・プール
自動的にチューニングされるこれらのメモリー・プールをゼロ以外の値に設定すると、その値が自動共有メモリー管理における最小レベルとして使用されます。アプリケーション・コンポーネントが最小限のメモリーで正常に機能できる場合は、最小値に設定します。
SGA_TARGET
は動的パラメータであり、Oracle Enterprise Managerの「メモリー・パラメータ」の「SGA」ページからSGAサイズ・アドバイザにアクセスするか、V$SGA_TARGET_ADVICE
ビューを問い合せてALTER SYSTEM
コマンドを使用して変更できます。SGA_TARGET
は、SGA_MAX_SIZE
初期化パラメータの値以下に設定できます。SGA_TARGET
の値の変更により、自動的にチューニングされたメモリー・プールが自動的にサイズ変更されます。
関連項目:
|
インスタンス起動時に値を0に設定して動的にSGA_TARGET
を無効にする場合、自動共有メモリー管理は無効になり、各メモリー・プールには現在の自動チューニングされたサイズが使用されます。必要であれば、DB_CACHE_SIZE
、SHARED_POOL_SIZE
、LARGE_POOL_SIZE
、JAVA_POOL_SIZE
およびSTREAMS_POOL_SIZE
の初期化パラメータを使用して、各メモリー・プールのサイズを手動で変更できます。「キャッシュ・サイズの動的な変更」を参照してください。
次のプールは手動でサイズ設定されるコンポーネントで、自動共有メモリー管理の影響は受けません。
ログ・バッファ
その他のバッファ・キャッシュ(KEEP
、RECYCLE
および他の非デフォルト・ブロック・サイズなど)
固定SGAおよびその他の内部割当て
これらのメモリー・プールを手動でサイズ設定するには、DB_KEEP_CACHE_SIZE
、DB_RECYCLE_CACHE_SIZE
、DB_nK_CACHE_SIZE
およびLOG_BUFFER
初期化パラメータを設定する必要があります。これらのプールに割り当てられたメモリーは、自動共有メモリー管理で、自動的にチューニングするメモリー・プールの値を計算する際に、SGA_TARGET
に使用可能な総量から差し引かれます。
システムで自動メモリー管理または自動共有メモリー管理が使用されていない場合、共有プール、ラージ・プール、バッファ・キャッシュおよびプロセス・プライベート・メモリーのサイズを動的に再構成することを選択できます。次の各項では、キャッシュ・サイズ設定の詳細を説明します。
DB_CACHE_ADVICE
、JAVA_POOL_SIZE
、LARGE_POOL_SIZE
、LOG_BUFFER
およびSHARED_POOL_SIZE
などの初期化構成パラメータを使用して、これらのメモリー・キャッシュのサイズを構成できます。これらのパラメータの値も、ALTER SYSTEM
文で動的に構成できます(起動後に静的に設定されるログ・バッファ・プールおよびプロセス・プライベート・メモリーを除く)。
共有プール、ラージ・プール、Javaプールおよびバッファ・キャッシュのメモリーは、グラニュル単位で割り当てられます。SGAサイズが1GBより小さい場合、グラニュル・サイズは4MBです。SGAサイズが1GB以上の場合、グラニュル・サイズは16MBに変化します。グラニュル・サイズは、インスタンスの起動時に計算されて固定されます。このサイズは、インスタンスの存続期間中は変化しません。
SGAで現在使用されているグラニュルのサイズは、ビューV$SGA_DYNAMIC_COMPONENTS
によって表示できます。それと同じグラニュルのサイズがSGAのすべての動的コンポーネントで使用されます。
SGAの総サイズは、SGA_MAX_SIZE
パラメータの値まで拡張できます。SGA_MAX_SIZE
が設定されていない場合は、必要であれば、1つのキャッシュのサイズを減らして、そのメモリーを別のキャッシュに再割当てできます。SGA_MAX_SIZE
は、全コンポーネントの集計にデフォルト設定されています。
注意: SGA_MAX_SIZE は、動的にサイズ変更できません。 |
インスタンスで使用できる最大メモリー量は、インスタンス起動時にSGA_MAX_SIZE
初期化パラメータで決定されます。SGA_MAX_SIZE
は、すべてのメモリー・コンポーネント(バッファ・キャッシュや共有プールなど)の合計よりも大きいサイズに指定できます。指定しない場合、SGA_MAX_SIZE
は、これらのコンポーネントで使用される実際のサイズにデフォルト設定されます。すべてのコンポーネントで使用される合計メモリーよりも大きい値にSGA_MAX_SIZE
を設定すると、別のキャッシュのサイズを小さくせずにキャッシュ・サイズを動的に大きくできます。
関連項目: 動的SGAの管理の詳細は、オペレーティング・システムのマニュアルを参照してください。 |
次のビューは、動的サイズ変更操作に関する情報を提供します。
V$MEMORY_CURRENT_RESIZE_OPS
には、現在進行中のメモリー・サイズ変更操作(自動および手動)に関する情報が表示されます。
V$MEMORY_DYNAMIC_COMPONENTS
には、動的にチューニングされたメモリー・コンポーネントの現在のサイズ(SGAおよびインスタンスPGAの合計サイズなど)に関する情報が表示されます。
V$MEMORY_RESIZE_OPS
には、最新800件の実行済メモリー・サイズ変更操作(自動および手動)に関する情報が表示されます。これには現在進行中の操作は含まれません。
V$MEMORY_TARGET_ADVICE
には、MEMORY_TARGET
初期化パラメータのチューニング・アドバイスが表示されます。
V$SGA_CURRENT_RESIZE_OPS
には、現在進行中のSGAサイズ変更操作に関する情報が表示されます。動的SGAコンポーネントの拡張または縮小操作があります。
V$SGA_RESIZE_OPS
には、最新800件の実行済SGAサイズ変更操作に関する情報が表示されます。これには現在進行中の操作は含まれません。
V$SGA_DYNAMIC_COMPONENTS
には、SGAの動的コンポーネントに関する情報が表示されます。このビューでは、起動後のすべての実行済SGAサイズ変更操作に基づく情報が要約されます。
V$SGA_DYNAMIC_FREE_MEMORY
には、今後の動的SGAサイズ変更操作で使用可能なSGAメモリーの量に関する情報が表示されます。
関連項目:
|
メモリーを構成する場合、キャッシュをアプリケーションの必要性に適したサイズに設定します。逆に、アプリケーションのキャッシュの使用率をチューニングすると、リソース要件を大幅に削減できます。Oracle Databaseメモリー・キャッシュを効率的に使用すると、ラッチ、CPU、I/Oシステムなどの関連リソースに対する負荷も軽減できます。
最高のパフォーマンスを得るために、次のことを考慮してください。
オペレーティング・システムおよびデータベース・リソースを最も効率的に使用するように、キャッシュを最適に設計する必要があります。
Oracleメモリー構造に対するメモリーの割当ては、アプリケーションの要求を最もよく反映する必要があります。
既存のアプリケーションに対する変更または追加を行う場合は、変更されたアプリケーションの要求を満たすためにOracleメモリー構造のサイズ変更が必要な場合があります。
アプリケーションがJavaを使用する場合、Javaプールのデフォルト構成を変更する必要があるかどうかを調べる必要があります。Javaのメモリー使用量の詳細は、『Oracle Database Java開発者ガイド』を参照してください。
大半のオペレーティング・システムでは、次のことを考慮することが重要です。
ページングは、新しいページをメモリーにロードできるようにするため、オペレーティング・システムがメモリー常駐ページをディスクに転送する場合に行われます。多くのオペレーティング・システムは、実メモリーに格納しきれない大量の情報を収容するために、ページングを行います。大半のオペレーティング・システムでは、ページングはパフォーマンスを低下させます。
オペレーティング・システムのユーティリティを使用して、オペレーティング・システムを調べ、システム上に多数のページングがあるかどうかを確認します。ページングが多数ある場合は、システム上の総メモリー量が、メモリーを割り当てたすべてを保持できるほど十分に大きくない場合があります。システム上の全体のメモリーを増やすか、割り当てたメモリー量を減らします。
SGAの目的は、迅速なアクセスのためにメモリー内にデータを格納することであるため、SGAは主メモリー内に存在する必要があります。SGAのページがディスクにスワップされると、データに迅速にアクセスできなくなります。多くのオペレーティング・システムでは、ページングによる損失は、大規模なSGAがもたらす利益をかなり上回ります。
注意: LOCK_SGA パラメータを使用すると、SGAが物理メモリーにロックされるため、ページ・アウトを防止できます。MEMORY_TARGET およびMEMORY_MAX_TARGET パラメータは、LOCK_SGA パラメータを有効化している場合は使用できません。 |
SGAとその各内部構造に割り当てられるメモリー量を確認するには、次のSQL*Plus文を入力します。
SHOW SGA
この文の出力例を次に示します。
Total System Global Area 840205000 bytes Fixed Size 279240 bytes Variable Size 520093696 bytes Database Buffers 318767104 bytes Redo Buffers 1064960 bytes
SGAをサイズ設定する場合は、個々のサーバー・プロセスとその他のプログラムがシステム上で作動するように十分なメモリーを使用できるようにします。
関連項目: オペレーティング・システムのメモリー使用方法のチューニングの詳細は、オペレーティング・システムのハードウェアとソフトウェアのマニュアル、およびオペレーティング・システム固有のOracleマニュアルを参照してください。 |
メモリーの割当てを構成する場合は、アプリケーションの要求により異なりますが、Oracleメモリー構造に使用可能なメモリーを配分します。Oracleの構造にメモリーを配分すると、Oracleが動作するために必要な物理I/Oの量に影響を与える可能性があります。最初にメモリーを適切に構成すると、I/Oシステムが効果的に構成されているかどうかも表示されます。
プロセスをひととおり実行した後で、メモリー割当てのステップを繰り返すことが必要となる可能性もあります。実行を繰り返すことによって、後のステップの変更に基づいて前のステップの調整が可能となります。たとえば、バッファ・キャッシュのサイズを小さくすると、共有プールなど別のメモリー構造のサイズを大きくできます。
様々なタイプの操作について、Oracle Databaseではバッファ・キャッシュを使用してディスクから読み取られたブロックを格納します。ソートやパラレル読取りなどの特定の操作の場合には、Oracle Databaseではバッファ・キャッシュはバイパスされます。バッファ・キャッシュを使用する操作について、この項では次の項目を説明します。
バッファ・キャッシュを効果的に使用するには、不要なリソース使用を回避するようにアプリケーションのSQL文をチューニングする必要があります。これを確認するには、頻繁に実行されるSQL文と、多数のバッファ読取りを実行するSQL文がチューニングされたかどうかを検証します。
新規にインスタンスを構成する場合は、バッファ・キャッシュの適切なサイズがわかっていません。通常、データベース管理者はキャッシュ・サイズの最初の見積りを行い、次にインスタンス上で代表的なワークロードを実行し、関連する統計を調べて、キャッシュが構成過小か構成過大かを調べます。
このビューは、DB_CACHE_ADVICE
初期化パラメータがON
に設定されているときに移入されます。このビューは、潜在的なバッファ・キャッシュ・サイズ範囲のシミュレーションによるミス率を示します。
このビューには、シミュレートされたキャッシュ・サイズのそれぞれの独自の行と、そのキャッシュ・サイズに対して発生すると予測された物理I/Oアクティビティがあります。DB_CACHE_ADVICE
パラメータは動的であるため、特定のワークロードのアドバイザ・データを収集できるように、アドバイザを動的に有効にしたり、無効にできます。
このアドバイザには、多少のオーバーヘッドが伴います。アドバイザを有効にすると、追加の記録が必要なため、CPUの使用量はわずかに増加します。
Oracleでは、DBAベースのサンプリングを使用して、キャッシュ・アドバイザ統計を収集します。サンプリングを使用すると、ブックキーピングに関連するCPUおよびメモリーのオーバーヘッドが大幅に減少します。サンプリングは、開始時のバッファの数が少ないバッファ・プールでは使用しません。
V$DB_CACHE_ADVICE
を使用するには、パラメータDB_CACHE_ADVICE
をON
に設定し、インスタンス上で代表的なワークロードを実行するようにします。V$DB_CACHE_ADVICE
ビューを問い合せる前にワークロードを安定化できるようにします。
次のSQL文は、様々なキャッシュ・サイズについてデフォルト・バッファ・プールに対するI/O要件の予測を戻します。
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)' COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers' COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor' COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads' SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON';
次の出力は、キャッシュが現行サイズの304MBではなく212MBである場合、物理読取りの予測数が1.74倍、つまり74%増加することを示しています。つまり、キャッシュ・サイズを212MBに減少させることは望ましくありません。
ただし、キャッシュ・サイズを334MBに増やすと、読取り数は0.93倍、つまり7%減少することになります。ホスト・マシン上でさらに30MB使用可能で、SGA_MAX_SIZE
設定で増分が許可されている場合は、デフォルトのバッファ・キャッシュ・プール・サイズを334MBに増やすことをお薦めします。
Estd Phys Estd Phys Cache Size (MB) Buffers Read Factor Reads ---------------- ------------ ----------- ------------ 30 3,802 18.70 192,317,943 10% of Current Size 60 7,604 12.83 131,949,536 91 11,406 7.38 75,865,861 121 15,208 4.97 51,111,658 152 19,010 3.64 37,460,786 182 22,812 2.50 25,668,196 212 26,614 1.74 17,850,847 243 30,416 1.33 13,720,149 273 34,218 1.13 11,583,180 304 38,020 1.00 10,282,475 Current Size 334 41,822 .93 9,515,878 364 45,624 .87 8,909,026 395 49,426 .83 8,495,039 424 53,228 .79 8,116,496 456 57,030 .76 7,824,764 486 60,832 .74 7,563,180 517 64,634 .71 7,311,729 547 68,436 .69 7,104,280 577 72,238 .67 6,895,122 608 76,040 .66 6,739,731 200% of Current Size
このビューは、潜在的な各キャッシュ・サイズの物理読取り数を予測する情報を提供して、キャッシュのサイズ設定を支援します。このデータには物理読取りファクタが含まれています。これは、バッファ・キャッシュが所定の値にサイズ変更された場合、現行の物理読取り回数がその分のみ変化すると予測されるファクタです。
注意: Oracleでは、物理読取りは必ずしもディスク読取りを意味しません。物理読取りは、ファイル・システム・キャッシュからで済む場合があります。 |
キャッシュ内でのブロックの検出成功とキャッシュのサイズ間の関係は、必ずしも滑らかな分布を示しません。バッファ・プールをサイズ設定するときは、キャッシュ・ヒット率の向上にまったく貢献しない(または、ほとんど貢献しない)追加バッファは使用しないでください。図7-1の例では、キャッシュ・サイズの増分の狭い帯状部分のみが考慮に値することを示しています。
図7-1を調べると、次のことがわかります。
ポイントAからポイントBへバッファを増やす場合の利点は、ポイントBからポイントCへバッファを増やす場合よりかなり大きくなります。
ポイントAとBおよびポイントBとCとの間の物理I/Oの減少は、グラフの点線で示されるように滑らかではありません。
バッファ・キャッシュ・ヒット率では、ディスク・アクセスを行わずにバッファ・キャッシュ内で要求されたブロックが検出された頻度を計算します。この率は、動的なパフォーマンス・ビューV$SYSSTAT
から選択したデータを使用して計算されます。バッファ・キャッシュ・ヒット率を使用して、V$DB_CACHE_ADVICE
で予測されたように物理I/Oを検証できます。
表7-1の統計は、ヒット率の計算に使用されます。
表7-1 ヒット率を計算するための統計
統計 | 説明 |
---|---|
バッファ・キャッシュからのブロックに対して読取り一貫性が要求された回数。 |
|
バッファ・キャッシュからのCURRENTブロックが要求された回数。 |
|
ディスクからバッファ・キャッシュへ読み取られたデータ・ブロックの合計数。 |
例7-1はV$SYSSTAT
表から直接選択した値を使用して単純化したもので、ある期間の値を選択したものではありません。アプリケーションの実行中のある期間にわたるこれらの統計の差分を計算し、それらの統計を使用してヒット率を判断することが最良の方法です。
例7-1 バッファ・キャッシュ・ヒット率の計算
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
問合せの出力の値を使用し、次の計算式でバッファ・キャッシュ・ヒット率を計算します。
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')
関連項目: V$SYSSTAT ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
バッファ・キャッシュ・サイズの増減を考慮する前に、調べるファクタは多数あります。たとえば、V$DB_CACHE_ADVICE
データおよびバッファ・キャッシュ・ヒット率を調べる必要があります。
低いキャッシュ・ヒット率は、キャッシュのサイズを大きくすることがパフォーマンスに有益であることを意味しません。キャッシュ・ヒット率の高いことが、ワークロードに対してキャッシュが適切にサイズ設定されていることを示しているとはかぎりません。
バッファ・キャッシュ・ヒット率を解釈する場合は、次の点を考慮する必要があります。
大きな表や索引を繰り返しスキャンすると、キャッシュ・ヒット率を低下させる可能性があります。バッファ読取り数が多く、頻繁に実行されるSQL文を調べて、実行計画が最適なものであるか確認します。可能であれば、1つのパスですべての処理を実行するか、SQL文を最適化して、頻繁にアクセスされるデータを繰り返しスキャンしないようにします。
可能であれば、頻繁にアクセスされるデータをクライアント・プログラムまたは中間層にキャッシュして、同じデータを再問合せしないようにします。
長い全表スキャンでアクセスされたOracleブロックは、最低使用頻度(LRU)リストの最後に配置され、リストの先頭には配置されません。このようにして、これらのブロックは、索引参照または小規模な表スキャンを実行するときに読み取られるブロックよりも早く除去されます。バッファ・キャッシュ・データの解析では、有効な大規模全表スキャン時の低いヒット率についても考慮する必要があります。
注意: 小規模表のスキャンは、一定のサイズのしきい値を使用して、表に対して実行されるスキャンです。小規模表とは、最大でバッファ・キャッシュの2%か20のいずれかの、大きい方と定義されます。 |
OLTPアプリケーションを実行するどの大容量データベースでも、常にほとんどの行は1回ないし0回しかアクセスされません。このことを基準に考えると、ブロックを使用後に長期間メモリーに保存することは、ほとんど意味がありません。
バッファ・キャッシュ・サイズを継続して増やすことはよくある間違いです。全表スキャンや、バッファ・キャッシュを使用しない操作を実行している場合は、このように値を増やしても何の効果もありません。
一般規則として、キャッシュ・ヒット率が低く、全表スキャンを実行しないようにアプリケーションがチューニングされている場合は、キャッシュのサイズを増やすことを検討してください。
キャッシュ・サイズを増やすには、まずDB_CACHE_ADVICE
初期化パラメータをON
に設定し、キャッシュ統計を安定させます。V$DB_CACHE_ADVICE
ビュー内のアドバイザ・データを調べて、実行する物理I/Oの量を大幅に減少させるために必要な次の増分を決定します。ホスト・オペレーティング・システムにページングさせずに必要な余分なメモリーをバッファ・キャッシュに割り当てることができる場合は、このメモリーを割り当てます。バッファ・キャッシュに割り当てられたメモリーの量を増やすには、DB_CACHE_SIZE
初期化パラメータの値を増やします。
必要であれば、インスタンスをシャットダウンせずに、バッファ・プールを動的にサイズ変更してこの変更を行います。
注意: キャッシュを大幅に(20パーセント以上)サイズ変更すると、古いキャッシュ・アドバイザ値は破棄されて、新しいサイズに設定されます。大幅にサイズ変更しない場合は、古いキャッシュ・アドバイザ値は既存の値を補間することで新しいサイズに調整されます。 |
DB_CACHE_SIZE
パラメータは、データベースの標準ブロック・サイズのデフォルト・キャッシュのサイズを指定します。データベースの標準ブロック・サイズとは異なるブロック・サイズを持つ表領域を作成して使用するには(トランスポータブル表領域をサポートする場合など)、使用するブロック・サイズごとに個別のキャッシュを構成する必要があります。DB_
n
K_CACHE_SIZE
パラメータを使用して、必要な標準以外のブロック・サイズを構成できます(n
は2、4、8、16または32のいずれかで、n
は標準ブロック・サイズではありません)。
注意: キャッシュ・サイズを選択するプロセスは、キャッシュがデフォルトの標準ブロック・サイズ・キャッシュ、KEEP またはRECYCLE キャッシュ、標準以外のブロック・サイズ・キャッシュのいずれかにかかわらず同様です。 |
関連項目: DB_ n K_CACHE_SIZE パラメータの使用方法の詳細は、『Oracle Databaseリファレンス』および『Oracle Database管理者ガイド』を参照してください。 |
一般に、ほとんどのシステムでは1つのデフォルト・バッファ・プールが適切です。ただし、アプリケーションのバッファ・プールについて詳しい知識を持つユーザーは、複数バッファ・プールを構成すると有益な場合があります。
非定型アクセス・パターンを持つセグメントの場合、それらのセグメントからのブロックを2つの異なるバッファ・プールであるKEEP
プールとRECYCLE
プールに格納します。セグメントのアクセス・パターンは、常にアクセスされるか(すなわち、ホット)、またはほとんどアクセスされない(たとえば、1日に1回のみバッチ・ジョブでアクセスされる大きなセグメント)というように、非定型である可能性があります。
複数バッファ・プールによって、これらの違いに対処できます。KEEP
バッファ・プールを使用してバッファ・キャッシュ内の頻繁にアクセスされるセグメントを保持し、RECYCLE
バッファ・プールを使用してオブジェクトがキャッシュ内の領域を不必要に占有するのを防ぐことができます。オブジェクトがキャッシュに関連付けられると、そのオブジェクトのすべてのブロックがそのキャッシュに置かれます。特定のバッファ・プールに割り当てられていないオブジェクトのために、DEFAULT
バッファ・プールがあります。デフォルト・バッファ・プールのサイズは、DB_CACHE_SIZE
です。各バッファ・プールは、同じLRU置換方針を使用します(たとえば、KEEP
プールがそのプールに割り当てられたすべてのセグメントを格納するほど十分大きくない場合、最も古いブロックがキャッシュから除去されます)。
オブジェクトを適切なバッファ・プールに割り当てると、次の操作を実行できます。
I/Oの低減または排除
個別のキャッシュに対するオブジェクトの隔離または制限
非常に大きいセグメントに大きい索引レンジ・スキャンまたは非有界索引レンジ・スキャンでアクセスすると、LRU除外方法では問題が発生する可能性があります。大規模とは、キャッシュのサイズと比較して大きいという意味です。非順次物理読取りのかなりの割合(10%を超える割合)を1つのセグメントが占有する場合、そのセグメントは大規模であると考えられます。大規模セグメントに対するランダム読取りは、他のセグメントのデータを含むバッファがキャッシュから除去される原因となります。大規模セグメントは、キャッシュの大きな割合を消費しますが、キャッシングによる利益はありません。
非常に頻繁にアクセスされるセグメントは、バッファが頻繁にアクセスされるのでキャッシュから除去されないため、大規模セグメントの読取りの影響を受けません。ただし、その問題は、大規模セグメントの読取りによるバッファの除外を免れるほど頻繁にはアクセスされないウォーム・セグメントに影響を与えます。この問題を解決するオプションは、次の3つです。
アクセスされたオブジェクトが索引である場合は、索引に選択性があるかどうかを調べます。選択性がない場合は、さらに選択性のある索引を使用するようにSQL文をチューニングします。
SQL文をチューニングすると、大きいセグメントを個別のRECYCLE
キャッシュに移動できるので、その他のセグメントに影響を与えません。RECYCLE
キャッシュはDEFAULT
バッファ・プールよりも小さくし、DEFAULT
バッファ・プールよりも迅速にバッファを再利用する必要があります。
大規模セグメントではまったく使用されない別のKEEP
キャッシュに小さなウォーム・セグメントを移動する方法もあります。KEEP
キャッシュをサイズ設定して、キャッシュでのミスを最小におさえられます。特定の問合せによってアクセスされるセグメントをKEEP
キャッシュに置き、除去されないようにすることで、その問合せのレスポンス時間をより予測可能にすることができます。
データベース・インスタンスごとに複数バッファ・プールを作成できます。データベースの各インスタンスについて、必ずしも同じバッファ・プール・セットを定義する必要はありません。インスタンスごとにバッファ・プールのサイズを変えることも、バッファを定義しないこともできます。それぞれのアプリケーション要件に従って、各インスタンスをチューニングします。
オブジェクトのデフォルト・バッファ・プールを定義するには、STORAGE
句のBUFFER_POOL
キーワードを使用します。この句は、CREATE TABLE
、CREATE CLUSTER
、CREATE INDEX
およびALTER TABLE
、ALTER CLUSTER
、ALTER INDEX
の各SQL文に有効です。バッファ・プールを指定すると、そのオブジェクトに対して読み取られたブロックは、すべてそのプールに配置されます。
バッファ・プールがパーティション表または索引に対して定義されている場合、オブジェクトの各パーティションは、特定のバッファ・プールで上書きされないかぎり、表または索引定義からバッファ・プールを継承します。
オブジェクトのバッファ・プールがALTER
文を使用して変更された場合、変更されたセグメントのブロックを現在格納しているすべてのバッファは、ALTER
文を発行する前にあったバッファ・プールに残ります。新たにロードされたブロック、および除去されてリロードされたブロックは、新しいバッファ・プールに入ります。
関連項目: STORAGE 句でのBUFFER_POOL の指定の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
V$DB_CACHE_ADVICE
を使用すると、インスタンス上に構成されたすべてのプールをサイズ設定できます。初期キャッシュ・サイズを見積り、代表的なワークロードを実行し、次に使用するプールのV$DB_CACHE_ADVICE
ビューを問い合せるだけです。
たとえば、KEEP
プールからデータを問い合せるには、次のようにします。
SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS FROM V$DB_CACHE_ADVICE WHERE NAME = 'KEEP' AND BLOCK_SIZE = (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size') AND ADVICE_STATUS = 'ON';
V$SYSSTAT
のデータは、すべてのバッファ・プールに対する論理読取りと物理読取りを1つの統計セットとして表します。バッファ・プールのヒット率を個々に確認するには、V$BUFFER_POOL_STATISTICS
ビューを問い合せます。このビューは、論理読取りと論理書込みの回数に関するプールごとの統計をメンテナンスします。
バッファ・プール・ヒット率は、次の計算式を使用して決定できます。
1 - (physical_reads/(db_block_gets + consistent_gets))
次の問合せを使用して比率を計算できます。
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS;
関連項目: V$BUFFER_POOL_STATISTICS ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
V$BH
ビューは、SGA内に現在存在するすべてのブロックのデータ・オブジェクトIDを示します。プール内にバッファを多く持つセグメントを判断するには、この項で説明する2つの方法のいずれかを使用します。すべてのセグメントのバッファ・キャッシュ使用パターンを確認する(方法1)か、特定のセグメントの使用パターンを調べます(方法2)。
方法1
次の問合せでは、ある時点でバッファ・キャッシュ内に常駐するすべてのセグメントのブロック数をカウントします。バッファ・キャッシュ・サイズによっては、このカウントに多数のソート領域を必要とする可能性があります。
COLUMN OBJECT_NAME FORMAT A40 COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999 SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OWNER != 'SYS' GROUP BY o.OBJECT_NAME ORDER BY COUNT(*); OBJECT_NAME NUMBER_OF_BLOCKS ---------------------------------------- ---------------- OA_PREF_UNIQ_KEY 1 SYS_C002651 1 .. DS_PERSON 78 OM_EXT_HEADER 701 OM_SHELL 1,765 OM_HEADER 5,826 OM_INSTANCE 12,644
方法2
次の手順に従って、ある時点で個々のオブジェクトによって使用されるキャッシュの割合を決定してください。
次の問合せを入力して、セグメントのOracle内部オブジェクトの数を検出します。
SELECT DATA_OBJECT_ID, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_NAME = UPPER('segment_name');
2つのオブジェクトが同じ名前を持つことがあるため(異なる型のオブジェクトの場合)、OBJECT_TYPE
列を使用して目的のオブジェクトを識別します。
SEGMENT_NAME
に対するバッファ・キャッシュ内のバッファ数を検出します。
SELECT COUNT(*) BUFFERS
FROM V$BH
WHERE OBJD = data_object_id_value;
data_object_id_value
は手順1で検出した値です。
インスタンス内にあるバッファ数を検出します。
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) FROM V$BUFFER_POOL GROUP BY NAME, BLOCK_SIZE HAVING SUM(BUFFERS) > 0;
バッファの総数に対するバッファの比率を計算し、現在SEGMENT_NAMEで使用されているキャッシュの割合を取得します。
% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]
注意: この手法は、1つのセグメントに対してのみ有効です。パーティション・オブジェクトについては、パーティションごとに問合せを実行する必要があります。 |
アプリケーションに頻繁に参照されるセグメントがある場合は、KEEP
バッファ・プールと呼ばれる別のキャッシュにそれらのセグメントのブロックを格納します。メモリーは、DB_KEEP_CACHE_SIZE
パラメータを必要なサイズに設定することでKEEP
バッファ・プールに割り当てられます。KEEP
プールのメモリーは、デフォルト・プールのサブセットではありません。保持できる一般的なセグメントは、頻繁に使用される小さい参照表です。アプリケーション開発者とDBAは、どの表が候補かを判断できます。
「プール内に多くのバッファを持つセグメントの特定」で説明するように、V$BH
を問い合せて、候補表からブロック数をチェックできます。
注意: NOCACHE 句は、KEEP キャッシュ内の表に影響を与えません。 |
KEEP
バッファ・プールの目的は、メモリー内にオブジェクトを保存して、I/O操作を避けることにあります。したがって、KEEP
バッファ・プールのサイズは、バッファ・キャッシュに保持するオブジェクトによって異なります。KEEP
バッファ・プールのおおよそのサイズは、このプールに割り当てられるすべてのオブジェクトで使用されるブロックを加算することで計算できます。セグメントに関する情報を収集する場合、DBA_TABLES.BLOCKS
とDBA_TABLES.EMPTY_BLOCKS
を問い合せて使用されるブロック数を判断できます。
ヒット率を計算するには、前述の問合せを使用してシステム・パフォーマンスの2つのスナップショットを時間をおいて取ります。物理読取り(physical reads
)、ブロック取得(block gets
)および一貫性読取り(consistent gets
)について、古い値から新しい値を引いて、これらの結果を使用してヒット率を計算します。
100%のバッファ・プール・ヒット率が最適とはかぎりません。KEEP
バッファ・プールのサイズを減らしても、十分に高いヒット率が維持されることがよくあります。KEEP
バッファ・プールから除去されたブロックは、別のバッファ・プールに割り当ててください。
注意: オブジェクトのサイズが大きくなった場合に、KEEP バッファ・プールに入りきらなくなることがあります。この場合、キャッシュからブロックが失われ始めます。 |
各オブジェクトをメモリー内に保持するとトレードオフが発生します。頻繁にアクセスされるブロックをキャッシュに保持することは有効ですが、頻繁に使用しないブロックを保持すると、よりアクティブな他のブロックのためのスペースが減ることになります。
メモリーに残さないセグメントに属するブロック用のRECYCLE
バッファ・プールを構成できます。RECYCLE
プールは、ほとんどスキャンされないか頻繁に参照されないセグメントに適しています。アプリケーションがラージ・オブジェクトのブロックをランダム方式でアクセスする場合は、バッファ・プールに格納されているブロックが除去される前に再利用できる可能性は(使用可能物理メモリーの量の制約により)ほとんどありません。これはバッファ・プールのサイズに関係なくあてはまります。したがって、そのオブジェクトのブロックはキャッシュしないでください。これらのキャッシュ・バッファは、他のオブジェクトに割り当てることができます。
メモリーは、DB_RECYCLE_CACHE_SIZE
パラメータを必要なサイズに設定することでRECYCLE
バッファ・プールに割り当てられます。このRECYCLE
バッファ・プールのメモリーは、デフォルト・プールのサブセットではありません。
あまり早くメモリーからブロックを破棄しないでください。バッファ・プールが小さすぎると、トランザクションまたはSQL文が実行を完了する前に、ブロックがキャッシュから除外されてしまう可能性があります。たとえば、アプリケーションが表から値を選択し、その値を使用してデータを処理し、レコードを更新する場合があります。SELECT
文の後でブロックがキャッシュから削除された場合は、更新を実行するために再度ディスクから読み取る必要があります。ブロックは、ユーザー・トランザクションの所要時間中は保存される必要があります。
異なるタイプのデータをキャッシュするには、共有プールを使用します。キャッシュされたデータには、PL/SQLブロックおよびSQL文のテキストおよび実行可能フォーム、ディクショナリ・キャッシュ・データ、結果キャッシュ・データおよびその他のデータが含まれています。
共有プールを適切な大きさにして使用すると、次の4つの方法でリソース使用量を低減できます。
SQL文がすでに共有プールに存在する場合は解析オーバーヘッドをなくせます。このため、ホスト上のCPUリソースとエンド・ユーザーの経過時間が節約されます。
リソース使用のラッチングが大幅に減少して、スケーラビリティがさらに増大します。
すべてのアプリケーションがSQL文およびディクショナリ・リソースの同一プールを使用するので、共有プール・メモリーの必要量が低減されます。
共有プールのディクショナリ要素はディスク・アクセスが不要なので、I/Oリソースが節約されます。
この項では、次の項目について説明します。
共有プールの主なコンポーネントは、ライブラリ・キャッシュ、ディクショナリ・キャッシュおよび(使用中の構成に応じた)結果キャッシュです。ライブラリ・キャッシュは、最近参照されたSQLとPL/SQLコードの実行可能な(解析またはコンパイルされた)形式を格納します。ディクショナリ・キャッシュは、データ・ディクショナリから参照されたデータを格納します。結果キャッシュは、問合せの結果とPL/SQLファンクションの結果を格納します。ライブラリ・キャッシュやディクショナリ・キャッシュなどの共有プール内のキャッシュの多くは、必要に応じてサイズを自動的に増減します。共有プールに空き領域がない場合は、新しいエントリを受け入れるために古いエントリがこれらのキャッシュから除去されます。
データ・ディクショナリ・キャッシュまたはライブラリ・キャッシュでのキャッシュ・ミスは、バッファ・キャッシュでのミスよりも影響が大きくなります。このため、頻繁に使用されるデータがキャッシュされるように共有プールをサイズ設定する必要があります。
共有サーバー、パラレル問合せ、Recovery Managerなど、共有プールで大きいメモリーの割当てを行う機能は多数あります。ラージ・プールと呼ばれる個別のメモリー領域を構成して、これらの機能で使用されるSGAメモリーを区別することをお薦めします。
共有プールからのメモリーの割当ては、チャンクで行われます。このため、1つの連続領域を必要とせずにラージ・オブジェクト(5KBより多い)をキャッシュにロードできるので、フラグメント化のために十分な連続メモリーが不足する可能性が減ります。
Java、PL/SQLまたはSQLの各カーソルが、まれに、5KBより大きい共有プールから割当てを行う場合があります。このような割当てを最も効率よく行うために、Oracleでは、少量の共有プールを区別しています。このメモリーは、共有プールに十分な領域がない場合に使用します。共有プールの区別された領域は予約プールと呼ばれます。
データ・ディクショナリ・キャッシュに格納されている情報には、ユーザー名、セグメント情報、プロファイル・データ、表領域情報および順序番号が含まれています。また、ディクショナリ・キャッシュはスキーマ・オブジェクトを説明する情報すなわちメタデータも格納します。メタデータが使用されるのは、SQLカーソルの解析時かPL/SQLプログラムのコンパイル時です。
ライブラリ・キャッシュは、SQLカーソル、PL/SQLプログラムおよびJavaクラスの実行可能な形式を保持します。この項では、チューニングを中心に説明します。チューニングはカーソル、PL/SQLプログラムおよびJavaクラスに関連するためです。これらをまとめてアプリケーション・コードと呼びます。
アプリケーション・コードを実行するとき、既存のコードが以前に実行されており、共有できる場合は、そのコードを再利用しようとします。解析された文の表現がライブラリ・キャッシュ内に存在し、共有できる場合は、既存のコードを再利用します。これは、ソフト解析またはライブラリ・キャッシュ・ヒットと呼ばれています。既存のコードを使用できない場合は、アプリケーション・コードの新しい実行可能バージョンを作成する必要があります。これは、ハード解析またはライブラリ・キャッシュ・ミスと呼ばれています。SQL文とPL/SQL文を共有できる場合の詳細は、「SQL共有基準」を参照してください。
ライブラリ・キャッシュ・ミスは、SQL文を処理するときの解析ステップまたは実行ステップのいずれかで発生します。アプリケーションがSQL文の解析コールを行うとき、解析された文の表現がライブラリ・キャッシュにまだ存在しない場合、Oracleはその文を解析し、共有プールに解析されたフォームを格納します。これはハード解析です。可能な場合は、すべての共有可能なSQL文が共有プール内にあることを確認して、解析コールのライブラリ・キャッシュ・ミスを低減できます。
アプリケーションがSQL文に対して実行コールを作成し、すでに作成されたSQL文の実行可能な部分が別の文のための場所を作成するためにライブラリ・キャッシュから除去(すなわち、割当て解除)された場合、Oracleはその文を暗黙に再解析し、新しい共有SQL領域を作成し、実行します。この場合も、ハード解析が発生します。通常は、ライブラリ・キャッシュに割り当てるメモリーを増やすことによって実行コールのライブラリ・キャッシュ・ミスを低減できます。
ハード解析を実行するには、ソフト解析の実行時より多くのリソースを使用します。ソフト解析に使用するリソースには、CPUおよびライブラリ・キャッシュ・ラッチ取得が含まれます。ハード解析に必要なリソースには、追加のCPU、ライブラリ・キャッシュ・ラッチ取得および共有プール・ラッチ取得が含まれます。ハード解析およびソフト解析の詳細は、「SQLの実行効率」を参照してください。
Oracleは、発行されるSQL文またはPL/SQLブロックが共有プールに現在存在する別の文と同じかどうかを自動的に判断します。
比較のために、次のステップが実行されます。
発行された文のテキストは、共有プール内の既存の文と比較されます。
文のテキストがハッシュされます。一致するハッシュ値がない場合、SQL文は共有プール内に現在存在せず、ハード解析が実行されます。
共有プール内の既存のSQL文に一致するハッシュ値がある場合は、一致した文のテキストが、ハッシュされた文のテキストと比較され、それらが同一であるかどうかが確認されます。SQL文やPL/SQLブロックのテキストは、空白、大文字小文字の区別、コメントも含め、完全に同一である必要があります。たとえば、次の文は同じ共有SQL領域を使用できません。
SELECT * FROM employees; SELECT * FROM Employees; SELECT * FROM employees;
通常は、リテラルのみ異なるSQL文は同じ共有SQL領域を使用できません。たとえば、次のSQL文は同じSQL領域に変換されません。
SELECT count(1) FROM employees WHERE manager_id = 121; SELECT count(1) FROM employees WHERE manager_id = 247;
唯一の例外は、CURSOR_SHARING
パラメータがSIMILAR
またはFORCE
に設定されている場合です。CURSOR_SHARING
パラメータが、SIMILAR
またはFORCE
に設定されている場合、類似の文はSQL領域を共有できます。CURSOR_SHARING
を使用する場合のコストと効果については、この項の後半で説明します。
関連項目: CURSOR_SHARING パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
発行された文で参照されたオブジェクトは、共有プール内のすべての既存の文の参照済オブジェクトと比較され、両方のオブジェクトが同一であるかどうかが確認されます。
SQL文やPL/SQLブロック内でスキーマ・オブジェクトを参照する際には、同じスキーマ内の同じオブジェクトである必要があります。たとえば、2人のユーザーが次のSQL文を発行するとします。
SELECT * FROM employees;
各ユーザーに独自のemployees
表がある場合、文はユーザーごとに異なる表を参照するので、この文は同一とみなされません。
SQL文の中のバインド変数は、名前、データ型および長さで一致している必要があります。
たとえば、次の文で同じ共有SQL領域を使用できないのは、バインド変数名が異なるためです。
SELECT * FROM employees WHERE department_id = :department_id; SELECT * FROM employees WHERE department_id = :dept_id;
多くのOracle製品(Oracle Formsやプリコンパイラなど)は、文をデータベースに渡す前にSQLを変換します。首尾一貫したSQL文の集合が生成されるように、文字は大文字に統一して変換され、空白は圧縮され、バインド変数は改名されます。
セッションの環境は同一である必要があります。たとえば、SQL文は、同一の最適化目標を使用して最適化する必要があります。
大容量のメモリーを搭載したシステムでは、結果キャッシュを利用して反復問合せのレスポンス時間を短縮できます。
結果キャッシュは、共有プールの結果キャッシュ・メモリー
と呼ばれる領域にSQL問合せの結果とPL/SQLファンクションの結果を格納します。これらの問合せおよびファンクションが繰り返し実行される場合、その結果はキャッシュ・メモリーから直接取得されます。これにより、レスポンス時間が高速化します。依存データベース・オブジェクトのデータが変更されると、格納されているキャッシュ済結果は無効になります。結果キャッシュの使用は、データベース全体に影響します。結果キャッシュそれ自体は、インスタンスに固有であり、異なるインスタンスで個別にサイズ変更できます。クラスタ内で結果キャッシュを無効にするには、インスタンスの起動時ごとに明示的にRESULT_CACHE_MAX_SIZE
初期化パラメータを0に設定する必要があります。
結果キャッシュ・メモリー
・プールは、SQL問合せ結果キャッシュ
(SQL問合せの結果を格納)とPL/SQLファンクション結果キャッシュ
(PL/SQLファンクションにより戻される値を格納)で構成されます。PL/SQLファンクションの結果キャッシュの使用方法は、『Oracle Database PL/SQL言語リファレンス』の、クロスセッションPL/SQLファンクションの結果キャッシュの使用方法に関する項を参照してください。
SQL問合せ結果キャッシュの概念
SQL問合せ結果は、再利用目的でキャッシュできます。読取り一貫性スナップショットを再利用するには、次のいずれかの要件を満たす必要があります。
結果の構築に使用される読取り一貫性スナップショットで、データの最新のコミット済状態を取得していること。
問合せが、フラッシュバック問合せを使用して明示的な時点を示していること。
SQL問合せ結果キャッシュの使用は、RESULT_CACHE_MODE
初期化パラメータを設定して制御できます。
RESULT_CACHE_MODE
初期化パラメータでは、SQL問合せ結果キャッシュの動作を決定します。使用可能な初期化パラメータ値は、MANUAL
およびFORCE
です。
MANUAL
に設定する場合は、result_cache
ヒントを使用して、特定の結果でキャッシュを使用することを指定する必要があります。キャッシュ内の結果を使用できない場合、問合せが実行され、その結果がキャッシュ内に格納されます。結果キャッシュ・ヒントを含め、まったく同じ文が続けて実行されると、キャッシュの内容が使用されます。
FORCE
に設定すると、可能なかぎりすべての結果でキャッシュが使用されます。FORCE
モードの使用時には、no_result_cache
ヒントを使用してキャッシュを回避できます。
キャッシュ済結果のエージ・アウトには、最低使用頻度アルゴリズムが使用されます。結果キャッシュ内の使用可能領域より大きな問合せ結果は、キャッシュされません。
RESULT_CACHE_MODE
がMANUAL
に設定されている場合に問合せの結果を結果キャッシュに格納するには、問合せにresult_cache
ヒントを含める必要があります。たとえば、次のようにします。
select /*+ result_cache */ deptno, avg(sal) from emp group by deptno;
このヒントにより、問合せの実行計画にResultCache
演算子が導入されます。問合せを実行すると、ResultCache
演算子により結果キャッシュ・メモリーが参照され、問合せの結果がすでにキャッシュ内に存在しないかどうかがチェックされます。存在する場合、その結果がキャッシュから直接取得されます。まだキャッシュに存在しない場合、問合せが実行されます。結果は出力として戻されると同時に、結果キャッシュ・メモリー内に格納されます。
RESULT_CACHE_MODE
がFORCE
のときに、問合せの結果を結果キャッシュに格納しない場合、問合せでno_result_cache
ヒントを使用する必要があります。
SQL問合せ結果キャッシュ使用時の制限事項
SQL問合せで次のデータベース・オブジェクトまたは関数を使用する場合、キャッシュ結果は使用できません。
ディクショナリ表および一時表
順序のCURRVAL
およびNEXTVAL
擬似列
SQL関数current_date
、current_timestamp
、local_timestamp
、userenv/sys_context
(非定数変数を含む)、sys_guid
、sysdate
およびsys_timestamp
非決定的なPL/SQLファンクション
キャッシュ済結果は、次のいずれかの要素が問合せで使用されている場合、パラメータ値でパラメータ化されます。
バインド変数。
SQL関数dbtimezone
、sessiontimezone
、userenv/sys_context
(定数変数を含む)、uid
およびuser
。
NLSパラメータ。
パラメータ化されたキャッシュ結果は、問合せが等価であり、パラメータ値が同じである場合、再利用できます。
最新のコミット済バージョンのデータより古いデータの読取り一貫性スナップショットに基づく問合せ結果は、キャッシュされません。キャッシュ済結果の構築に使用される表のいずれかが、現在のセッションにおいて継続中のトランザクションで変更された場合、その結果はキャッシュされません。
RESULT_CACHE
ヒントをインライン・ビューに追加すると、キャッシュ済結果の再利用性を最大化するために、外部問合せとインライン・ビュー間の最適化は無効化されます。副問合せはキャッシュされません。
共有プールの重要な目的は、SQL文とPL/SQL文の実行可能バージョンをキャッシュすることです。これにより、ハード解析にリソースを使用することなく、同じSQLまたはPL/SQLコードを複数回実行できるので、CPU、メモリーおよびラッチの使用が大幅に減少します。
また、共有プールは、データ・ウェアハウス・アプリケーションで非共有SQLをサポートできます。これらのアプリケーションでは、同時実行性が低くリソース使用率の高いSQL文が実行されます。このような状況では、リテラル値を持つ非共有SQLを使用することをお薦めします。バインド変数ではなくリテラル値を使用すると、オプティマイザは優れた列選択性の見積りを行えるので、最適なデータ・アクセス・プランを提供します。
データ・ウェアハウス環境では、SQL問合せ結果キャッシュにより、共有プールの使用も最適化できます。
関連項目: 『Oracle Databaseデータ・ウェアハウス・ガイド』 |
OLTPシステムでは、共有プールと関連リソースを効率的に使用できるようにする方法が多数あります。次の項目についてアプリケーション開発者と検討し、共有プールが効果的に使用されるようにする方法を決定します。
同時実行性の高いOLTPシステムで共有プールを効率よく使用すると、解析関連アプリケーションのスケーラビリティの問題が発生する確率が大幅に低減します。
同じアプリケーションを実行する複数のユーザーのために共有SQLを再利用すると、ハード解析が回避されます。ソフト解析は、共有プール・ラッチやライブラリ・キャッシュ・ラッチなどのリソース使用量を大幅に減少させます。カーソルを共有するには、次のことを行います。
可能な場合、SQL文ではリテラルではなくバインド変数を使用します。たとえば、次の2つの文は字面が完全には一致しないので、同じ共有領域は使用できません。
SELECT employee_id FROM employees WHERE department_id = 10; SELECT employee_id FROM employees WHERE department_id = 20;
リテラルをバインド変数と置換すると、2回実行可能なSQL文が1つのみ生成されます。
SELECT employee_id FROM employees WHERE department_id = :dept_id;
注意: バインド変数を使用するためにコードをリライトすることが実際的ではない既存のアプリケーションについては、CURSOR_SHARING 初期化パラメータを使用してハード解析のオーバーヘッドをある程度回避できます。詳細は、「既存のアプリケーション用のCURSOR_SHARING」を参照してください。 |
多数のユーザーが動的な非共有のSQL文を発行するようなアプリケーションを設計しないようにしてください。通常、大半のユーザーが必要とするデータの大部分は、事前に設定されている問合せを使用して満たすことができます。そのような機能が必要な場合は、動的SQLを使用します。
アプリケーションのユーザーが最適化アプローチと目標を各セッションに対して変更しないことを確認してください。
アプリケーション開発者に対し、次のポリシーを設定します。
SQL文とPL/SQLブロックに対して、バインド変数のネーミング規則と、スペーシング規定を標準化します。
可能な場合、ストアド・プロシージャを使用することを考慮してください。そうすれば、同じストアド・プロシージャを発行している複数のユーザーが、同じ共有PL/SQL領域を自動的に使用します。ストアド・プロシージャは解析済フォームに格納されているため、ストアド・プロシージャを使用すると実行時間の解析が減少します。
同一であっても共有されていないSQL文についてV$SQL_SHARED_CURSOR
を問い合せることで、カーソルが共有されない理由を判断できます。この理由には、オプティマイザの設定とバインド変数の不整合などがあります。
ユーザーが独自のユーザーIDでデータベースにログインするような大きいOLTPシステムでは、パブリック・シノニムを使用するのではなく、明示的にセグメントの所有者を修飾すると有益です。これにより、ディクショナリ・キャッシュ内のエントリ数が大幅に削減されます。たとえば、次のようにします。
SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;
表名を認定する別の方法として、個々のユーザーIDではなく単一のユーザーIDでデータベースに接続します。ユーザー・レベルの検証は、中間層でローカルに行われます。個別のユーザーIDの数を削減した場合も、ディクショナリ・キャッシュ上の負荷は低減します。
ストアドPL/SQLパッケージを使用すると、多数のユーザーが個々にユーザー・サインオンとパブリック・シノニムを持つシステムにおける、スケーラビリティの問題を克服できます。これは、パッケージがコール元ではなく所有者として実行されるため、ディクショナリ・キャッシュの負荷がかなり削減されるためです。
注意: スケーラビリティの問題を克服するために、定義者権限パッケージの使用をお薦めします。ディクショナリ・キャッシュの負荷軽減の利点は、実行者権限パッケージの場合ほど顕著ではありません。 |
ピーク時間に使用率の高いセグメントでDDL操作を実行しないようにします。そのようなセグメントでDDLを実行すると、多くの場合、依存SQLは無効にされるため、以降の実行で再度解析されることになります。
頻繁に更新される順序番号に十分なキャッシュ領域を割り当てると、ディクショナリ・キャッシュ・ロックの回数が大幅に減るため、スケーラビリティが向上します。CREATE SEQUENCE
文またはALTER SEQUENCE
文のCACHE
キーワードを使用して、各順序のキャッシュ済エントリ数を構成できます。
関連項目: CREATE SEQUENCE 文およびALTER SEQUENCE 文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 |
使用しているOracleアプリケーション・ツールにより異なりますが、アプリケーションが解析コールを実行する頻度を制御できます。
アプリケーションが、カーソルをクローズする、または新しいSQL文に既存のカーソルを再利用する頻度は、セッションで使用されるメモリー量と、時には、そのセッションで実行される解析の量にも影響を与えます。
(異なるSQL文の)カーソルをクローズまたは再利用するアプリケーションは、カーソルをオープンした状態を保つアプリケーションほどセッション・メモリーを必要としません。逆に、そのようなアプリケーションでは、解析コールをより多く実行し、そのための追加のCPUおよびOracleリソースを使用する可能性があります。
頻繁に実行されないSQL文に関連するカーソルをクローズしたり、他の文に再利用できるのは、その文を再実行(および再解析)する可能性が低いからです。
再実行されるSQL文を含むカーソルがクローズまたは別の文に再利用される場合は、追加の解析コールが必要になります。カーソルがオープンされた状態であれば、解析コールを発行するためのオーバーヘッドを発生させずに、カーソルを再利用できます。
カーソルの管理を行う方法は、アプリケーション開発ツールにより異なります。次の項では、いくつかのツールで使用される方法を紹介します。
関連項目:
|
Oracle Call Interface(OCI)を使用する場合、再実行するカーソルはクローズおよび再オープンしないでください。そのかわりに、カーソルをオープンしたままにし、実行前にリテラル値をバインド変数に変更してください。
既存のSQL文が今後再実行される場合は、新しいSQL文に文ハンドルを再利用しないようにしてください。
Oracleプリコンパイラを使用する場合、プリコンパイラ句を設定して、いつカーソルをクローズするかを制御できます。Oracleモードでは、プリコンパイラ句は次のとおりです。
ANSIモードでは、HOLD_CURSOR
とRELEASE_CURSOR
の値が切り替えられますが、これはお薦めしません。
プリコンパイラ句は、プリコンパイラ・コマンドライン上またはプリコンパイラ・プログラム内で指定できます。これらの句により、様々な方法で、プログラムの実行中にカーソルを管理できます。
関連項目: これらの句の詳細は、使用している言語のプリコンパイラ・マニュアルを参照してください。 |
OLTPアプリケーションは、結果キャッシュの使用により多大なメリットを得ることができます。このメリットは、アプリケーションごとに大きく異なります。使用中のアプリケーションで結果キャッシュのメリットを得ることができるかどうかを評価する場合、PL/SQLファンクション結果キャッシュおよびSQL問合せ結果キャッシュの使用を検討してください。
新規にインスタンスを構成する場合、作成する共有プール・キャッシュの適切なサイズを知ることはできません。通常、DBAはキャッシュ・サイズの最初の見積りを行い、次にインスタンス上で代表的なワークロードを実行し、関連する統計を調べて、キャッシュが過小構成か過大構成かを調べます。
OLTPアプリケーションの多くでは、共有プール・サイズはアプリケーション・パフォーマンスにとって重要な要因です。意思決定支援システム(DSS)のような、ごく少数の不連続なSQL文を発行するアプリケーションでは、共有プールのサイズはそれほど重要ではありません。
共有プールが小さすぎると、使用可能領域の限度を補うために、追加リソースを使用することになります。このため、CPUとラッチングのリソースが使用され、競合が発生します。共有プールは、頻繁にアクセスされるオブジェクトをキャッシュするためにちょうど十分な大きさであることが最適です。共有プールに大量の空きメモリーを持つことは、メモリーの無駄になります。データベースの稼働後に統計を調べる際、DBAはこの点についてワークロード内に該当する箇所がないかチェックする必要があります。
共有プールをサイズ設定するときの目標は、メモリーを割り当てすぎずに、複数回実行されるSQL文がライブラリ・キャッシュにキャッシュされるようにすることです。
以前にキャッシュされ、すでに除去されたSQL文のリロード(すなわち、再解析)の量の統計は、V$LIBRARYCACHE
ビューのRELOADS
列に示されます。効果的にSQLを再利用するアプリケーションでは、システムが最適な共有プール・サイズを持ち、RELOADS
統計が0(ゼロ)に近い値を示します。
V$LIBRARYCACHE
ビューのINVALIDATIONS
列は、ライブラリ・キャッシュのデータが無効にされ、再解析された回数を示しています。INVALIDATIONS
は0(ゼロ)に近い値である必要があります。つまり、共有できた可能性のあるSQL文が、ある操作(たとえば、DDL)により無効にされたことを意味します。この統計は、ピーク・ロード中のOLTPシステム上では、0(ゼロ)に近い値となります。
別の重要な統計は、ピーク時の共有プール内の空きメモリー量です。空きメモリー量は、共有プールの空きメモリーを参照するV$SGASTAT
から問い合せることができます。空きメモリーは、システム上にリロードを発生させない程度で、できるだけ小さい値である必要があります。
最終的には、ライブラリ・キャッシュの全般的なインジケータは、ライブラリ・キャッシュ・ヒット率で表されます。この値は、この項で説明されているその他の統計、およびハード解析率や、共有プールまたはライブラリ・キャッシュのラッチ競合があるかどうかなどのその他のデータとともに考慮する必要があります。
これらの統計の詳細は、次の項で説明します。
動的パフォーマンス・ビューV$LIBRARYCACHE
を調べることで、ライブラリ・キャッシュのアクティビティを反映する統計を監視できます。これらの統計は、最新のインスタンス起動以降のライブラリ・キャッシュのアクティビティを反映しています。
このビューの各行には、ライブラリ・キャッシュ内に保持される項目の1つに対応する統計が収録されます。各行ごとに記述される項目は、NAMESPACE
列の値によって識別されます。次のNAMESPACE
値を持つ行は、SQL文とPL/SQLブロックのライブラリ・キャッシュのアクティビティを反映します。
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
他のNAMESPACE
値を持つ行は、Oracleが依存関係のメンテナンスのために使用するオブジェクト定義に対するライブラリ・キャッシュのアクティビティを反映します。
関連項目: 動的パフォーマンス・ビューV$LIBRARYCACHE の詳細は、『Oracle Databaseリファレンス』を参照してください。 |
各NAMESPACEを個々に調べるには、次の問合せを使用します。
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE ORDER BY NAMESPACE;
この問合せの出力例を次に示します。
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS --------------- ---------- ---------- ---------- ------------- BODY 8870 8819 0 0 CLUSTER 393 380 0 0 INDEX 29 0 0 0 OBJECT 0 0 0 0 PIPE 55265 55263 0 0 SQL AREA 21536413 21520516 11204 2 TABLE/PROCEDURE 10775684 10774401 0 0 TRIGGER 1852 1844 0 0
ライブラリ・キャッシュ・ヒット率を計算するには、次の計算式を使用します。
Library Cache Hit Ratio = sum(pinhits) / sum(pins)
ライブラリ・キャッシュ・ヒット率の計算式を使用すると、キャッシュ・ヒット率は次のようになります。
SUM(PINHITS)/SUM(PINS) ---------------------- .999466248
注意: これらの問合せでは、ある時間間隔で収集された統計ではなく、インスタンス起動時からのデータを戻します。時間間隔の統計の方が、より的確に問題を特定できます。 |
戻されたデータを調べると、次のことがわかります。
SQL AREA
のNAMESPACEでは、21,536,413回の実行がありました。
11,204回の実行でライブラリ・キャッシュ・ミスが発生したため、文またはブロックを暗黙的に再解析するか、またはライブラリ・キャッシュから除去されている場合は、オブジェクト定義をリロード(すなわち、RELOAD
)する必要があります。
SQL文は2回無効化されたため、再度ライブラリ・キャッシュ・ミスが発生しました。
ヒット率は約99.94%です。これは、実行の0.06%のみが再解析されたことを意味します。
共有プールの空きメモリーの容量は、V$SGASTAT
でレポートされます。次の問合せを使用してこのビューの現在の値についてレポートを作成します。
SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool'; The output will be similar to the following: POOL NAME BYTES ----------- -------------------------- ---------- shared pool free memory 4928280
共有プール内に使用可能な空きメモリーが常にある場合、プールのサイズを増やしても、効果はほとんど(または、まったく)ありません。また、共有プールがいっぱいというだけでは、問題があるとはいえません。これは、適切に構成されたシステムであることを示している場合があります。
ライブラリ・キャッシュに使用可能なメモリー量は、Oracleインスタンスの解析率に大きな影響を与えます。共有プールのアドバイザ統計から、データベース管理者はライブラリ・キャッシュ・メモリーについての情報を得ることができ、共有プールのサイズ変更が共有プール内のオブジェクトの除去にどのように影響するかを予測できます。
共有プールのアドバイザ統計では、共有プール・メモリーにおけるライブラリ・キャッシュの使用率が追跡され、異なるサイズの共有プールでライブラリ・キャッシュがどのように動作するかが予測されます。2つの固定ビューにより、ライブラリ・キャッシュのメモリー使用量、現在の確保量、共有プールのLRUリスト上にある量、さらに共有プールのサイズ変更により損失または獲得できる時間を判別する情報が提供されます。
共有プールのアドバイザ統計では、次のビューが使用できます。共有プール・アドバイザをオンにすると、これらのビューにはあらゆるデータが表示されます。共有プール・アドバイザをオフにすると、それらの統計がリセットされます。
このビューには、共有プールのサイズを変更した場合の、見積り解析時間に関する情報が表示されます。サイズの範囲は、同じ時間間隔で、現在の共有プール・サイズまたは確保されたライブラリ・キャッシュ・メモリー量の10%のうち大きい方の値から、現在の共有プール・サイズの200%までです。時間間隔の値は、共有プールの現在のサイズによって異なります。
このビューには、別のNAMESPACEのライブラリ・キャッシュのメモリー・オブジェクトに割り当てられるメモリーに関する情報が表示されます。メモリー・オブジェクトとは、効率的な管理を行うためのメモリーの内部グループ化です。ライブラリ・キャッシュ・オブジェクトは1つ以上のメモリー・オブジェクトで構成されます。
これらのビューには、Javaに使用されるライブラリ・キャッシュ・メモリーについての情報を追跡し、Javaプールのサイズ変更が解析率に及ぼす影響を予測する、Javaプール・アドバイザ統計が含まれます。
V$JAVA_POOL_ADVICE
には、プールのサイズを変更した場合の、Javaプールの見積り解析時間に関する情報が表示されます。サイズの範囲は、同じ時間間隔で、現在のJavaプール・サイズまたは確保されたJavaライブラリ・キャッシュ・メモリー量の10%のうち大きい方の値から、現在のJavaプール・サイズの200%までです。時間間隔の値は、Javaプールの現在のサイズによって異なります。
関連項目: 動的パフォーマンス・ビューV$SHARED_POOL_ADVICE 、V$LIBRARY_CACHE_MEMORY 、V$JAVA_POOL_ADVICE およびV$JAVA_LIBRARY_CACHE_MEMORY の詳細は、『Oracle Databaseリファレンス』を参照してください。 |
共有プールがライブラリ・キャッシュに対して適切にサイズ設定されている場合、その設定はディクショナリ・キャッシュ・データに対しても適切であるのが普通です。
データ・ディクショナリ・キャッシュ・ミスは、いくつかの場合に予想されます。インスタンス起動時は、データ・ディクショナリ・キャッシュにデータは含まれていません。したがって、発行されたSQL文からキャッシュ・ミスが発生する可能性があります。キャッシュに読み取られるデータが増えると、キャッシュ・ミスの可能性は減少します。最終的に、データベースは、最も頻繁に使用されるディクショナリ・データがキャッシュ内に存在する安定状態に到達します。この時点で、キャッシュ・ミスはほとんど発生しません。
V$ROWCACHE
ビューの各行は、データ・ディクショナリ項目について単一のタイプの統計を収録します。これらの統計は、直前のインスタンス起動以降のデータ・ディクショナリ・アクティビティを反映しています。データ・ディクショナリ・キャッシュの使用と有効性を反映するV$ROWCACHE
ビューの中の列を表7-2にリストします。
表7-2 V$ROWCACHE列
列 | 説明 |
---|---|
|
特定のデータ・ディクショナリ項目を識別します。各行で、この列の値は接頭辞 |
|
対応する項目に関する情報へのリクエストの総数を示します。たとえば、ファイル記述の統計を含む行では、この列はファイル記述データへのリクエストの総数を持ちます。 |
|
キャッシュで満たされなかったデータ・リクエストで、I/Oを必要とするものの個数を示します。 |
|
ディクショナリ・キャッシュ内のデータが更新された回数を示します。 |
次の問合せによって、アプリケーションの実行中、ある期間にわたってV$ROWCACHE
ビューの統計を監視してください。導出された列PCT_SUCC_GETS
は、項目固有のヒット率と考えることができます。
column parameter format a21 column pct_succ_gets format 999.9 column updates format 999,999,999 SELECT parameter , sum(gets) , sum(getmisses) , 100*sum(gets - getmisses) / sum(gets) pct_succ_gets , sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;
この問合せの出力例を次に示します。
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES --------------------- ---------- -------------- ------------- ------------ dc_database_links 81 1 98.8 0 dc_free_extents 44876 20301 54.8 40,453 dc_global_oids 42 9 78.6 0 dc_histogram_defs 9419 651 93.1 0 dc_object_ids 29854 239 99.2 52 dc_objects 33600 590 98.2 53 dc_profiles 19001 1 100.0 0 dc_rollback_segments 47244 16 100.0 19 dc_segments 100467 19042 81.0 40,272 dc_sequence_grants 119 16 86.6 0 dc_sequences 26973 16 99.9 26,811 dc_synonyms 6617 168 97.5 0 dc_tablespace_quotas 120 7 94.2 51 dc_tablespaces 581248 10 100.0 0 dc_used_extents 51418 20249 60.6 42,811 dc_user_grants 76082 18 100.0 0 dc_usernames 216860 12 100.0 0 dc_users 376895 22 100.0 0
サンプル問合せが戻したデータを調べると、次のことがわかります。
使用済エクステント、空きエクステントおよびセグメントには、多数のミスと更新があります。つまり、インスタンスに大量の動的な領域の拡張があったことを示しています。
取得成功率およびその統計と、実際の取得数との比較に基づくと、共有プールはディクショナリ・キャッシュ・データを適切に格納できる大きさがあります。
次の計算式を使用して、総合的ディクショナリ・キャッシュ・ヒット率も計算できますが、すべてのキャッシュにわたるデータを合計すると、より細かいデータの粒度は失われます。
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
DBMS_RESULT_CACHE
パッケージでは、結果キャッシュのメモリー割当てを管理できる統計、情報および演算子が提供されます。
DBMS_RESULT_CACHE
パッケージを使用すると、キャッシュの回避、キャッシュ・メモリーの使用に関する統計の取得、キャッシュのフラッシュなどの様々な操作を実行できます。たとえば、メモリー割当ての統計を参照するには、次のSQLプロシージャを使用します。
SQL> set serveroutput on SQL> execute dbms_result_cache.memory_report
このコマンドの出力例を次に示します。
R e s u l t C a c h e M e m o r y R e p o r t [Parameters] Block Size = 1024 bytes Maximum Cache Size = 950272 bytes (928 blocks) Maximum Result Size = 47104 bytes (46 blocks) [Memory] Total Memory = 46340 bytes [0.048% of the Shared Pool] ... Fixed Memory = 10696 bytes [0.011% of the Shared Pool] ... State Object Pool = 2852 bytes [0.003% of the Shared Pool] ... Cache Memory = 32792 bytes (32 blocks) [0.034% of the Shared Pool] ....... Unused Memory = 30 blocks ....... Used Memory = 2 blocks ........... Dependencies = 1 blocks ........... Results = 1 blocks ............... SQL = 1 blocks PL/SQL procedure successfully completed.
既存のすべての結果を削除してキャッシュ・メモリーを消去するには、次のコマンドを使用します。
SQL>execute dbms_result_cache.flush
DBMS_RESULT_CACHE
パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
結果キャッシュの情報へのアクセス
表7-3に、結果キャッシュ・メモリーの情報および統計を提供するビューをリストします。各ビューは、SQL問合せ結果キャッシュおよびPL/SQLファンクション結果キャッシュの集計された統計を示します。
表7-3 RESULT_CACHEビュー
ビュー名 | 説明 |
---|---|
(G)V$_RESULT_CACHE_STATISTICS |
様々なキャッシュ設定とメモリー使用量の統計のリスト |
(G)V$RESULT_CACHE_MEMORY |
すべてのメモリー・ブロックとそれに対応する統計のリスト |
(G)V$RESULT_CACHE_OBJECTS |
すべてのオブジェクト(キャッシュ済結果および依存性)とその属性のリスト |
(G)V$RESULT_CACHE_DEPENDENCY |
キャッシュ済結果と依存性間における依存関係の詳細のリスト |
次の問合せを使用して、V$RESULT_CACHE_STATISTICS
ビューの統計を監視します。
column name format a20 select name, value from v$result_cache_statistics;
この問合せの出力例を次に示します。
NAME VALUE -------------------- ---------- Block Size (Bytes) 1024 Block Count Maximum 3136 Block Count Current 32 Result Size Maximum (Blocks) 156 Create Count Success 2 Create Count Failure 0 Find Count 0 Invalidation Count 0 Delete Count Invalid 0 Delete Count Valid 0
通常、SQL問合せ結果キャッシュが良好に使用されているシステムでは、Create Count Failure
およびDelete Count Valid
の値が比較的小さく、Find Count
の値が比較的大きくなります。
これらのビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
共有プール統計は実行可能な調整方法を示します。この項ではそのうちのいくつかについて説明します。
共有プールのメモリー量を増やすと、ライブラリ・キャッシュ、ディクショナリ・キャッシュおよび結果キャッシュで使用できるメモリー量が増えます。
共有SQL領域がそれらのSQL文を解析した後にキャッシュ内に残るようにするには、V$LIBRARYCACHE.RELOADS
値が0(ゼロ)に近くなるまでライブラリ・キャッシュに利用できるメモリー量を増やします。ライブラリ・キャッシュに利用できるメモリーを増やすには、SHARED_POOL_SIZE
初期化パラメータの値を増やしてください。このパラメータの最大値はオペレーティング・システムによって異なります。この処置によって、実行のためのSQL文とPL/SQLブロックの暗黙的な再解析が減少します。
GETS
とGETMISSES
列を監視することによって、キャッシュ・アクティビティを調べてください。ディクショナリ・キャッシュが頻繁にアクセスされる場合、GETS
の合計に対するGETMISSES
の合計の割合は、アプリケーションによって異なりますが、10%または15%より低くしてください。
次のすべてに当てはまる場合は、キャッシュに利用できるメモリー量を増やすことを考慮してください。
アプリケーションは共有プールを効果的に使用している。「共有プールの効果的な使用方法」を参照してください。
システムは安定状態に達し、項目固有のヒット率が低く、ヒット率の低いキャッシュへの取得数が多い。
初期化パラメータSHARED_POOL_SIZE
の値を増やして、データ・ディクショナリ・キャッシュに利用できるメモリーを増やします。
デフォルトでは、データベースの起動時に、Oracleによって共有プール内の結果キャッシュにメモリーが割り当てられます。割り当てられるメモリー・サイズは、共有プールのメモリー・サイズとメモリー管理システムに応じて変化します。
MEMORY_TARGET
初期化パラメータを使用してメモリー割当てを指定すると、memory_target
の0.25%が結果キャッシュに割り当てられます。
SGA_TARGET
初期化パラメータを使用して共有プールのサイズを設定すると、sga_target
の0.5%が結果キャッシュに割り当てられます。
SHARED_POOL_SIZE
初期化パラメータを使用して共有プールのサイズを指定すると、共有プールのサイズの1%が結果キャッシュに割り当てられます。
結果キャッシュに割り当てられているメモリーを変更するには、RESULT_CACHE_MAX_SIZE
初期化パラメータを設定します。データベースの起動時にこの値を0に設定すると、結果キャッシュは無効になります。データベースの起動時にSPFILEまたはinit.ora
ファイルでこの値を0に設定すると、RESULT_CACHE_MAX_SIZE
は動的に変更できなくなります。
注意: Oracleでは、結果キャッシュに共有プールの75%を超える容量が割り当てられることはありません。 |
RESULT_CACHE_MAX_RESULT
初期化パラメータを使用して、単一の結果により使用できる結果キャッシュ・メモリーの最大割合を指定します。デフォルト値は5%ですが、1〜100の任意のパーセント値を指定できます。
RESULT_CACHE_REMOTE_EXPIRATION
初期化パラメータを使用して、リモート・データベース・オブジェクトにアクセスする結果が有効状態を保つ時間(分単位)を指定します。デフォルト値は0です。0に設定すると、SQL問合せ結果キャッシュは、リモート表にアクセスする問合せに関しては無効になります。RESULT_CACHE_REMOTE_EXPIRATION
に0以外の値を使用すると、リモート・データベースに対するDMLによって結果キャッシュが無効化されません。
注意: 現在のところ、問合せ結果キャッシュの統計はV$SHARED_POOL_ADVICE に含まれません。したがって、大容量の結果キャッシュを作成する場合、V$SHARED_POOL_ADVICE の最適な共有プールのサイズにキャッシュ・サイズを追加する必要があります。 |
共有プールとは異なり、ラージ・プールにはLRUリストがありません。Oracleは、ラージ・プールからオブジェクトを除去しようとしません。
インスタンスが次のいずれかを使用する場合は、ラージ・プールの構成を考慮してください。
パラレル問合せ
パラレル問合せでは、共有プール・メモリーを使用してパラレル実行メッセージ・バッファをキャッシュします。
関連項目: パラレル問合せによるラージ・プールのサイズ設定の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。 |
Recovery Manager
Recovery Managerは、共有プールを使用してバックアップおよびリストア操作時にI/Oバッファをキャッシュします。I/Oサーバー・プロセスと、バックアップおよびリストア操作では、Oracleは数百KB単位でバッファを割り当てます。
関連項目: Recovery Managerを使用する場合のラージ・プールのサイズ設定の詳細は、『Oracle Databaseバックアップおよびリカバリ・ユーザーズ・ガイド』を参照してください。 |
共有サーバー
共有サーバー・アーキテクチャでは、各クライアント・プロセスのセッション・メモリーが共有プールに含まれています。
Oracleでは共有サーバー・セッション・メモリーに共有プールからメモリーを割り当てるため、ライブラリ・キャッシュとディクショナリ・キャッシュに使用可能な共有プール・メモリーの量が減少します。別のプールからこのセッション・メモリーを割り当てると、Oracleは、主に共有SQLのキャッシングのために共有プールを使用できるので、共有SQLキャッシュの減少によるパフォーマンス・オーバーヘッドは発生しません。
共有サーバー関連のユーザー・グローバル領域(UGA)の割当てには、共有プールではなくラージ・プールの使用をお薦めします。共有プールは、Oracleによって、共有SQLやPL/SQLプロシージャなど、他の目的のシステム・グローバル領域(SGA)メモリー用に割り当てられるためです。共有プールのかわりにラージ・プールを使用すると、共有プールの断片化も減少します。
ラージ・プールに共有サーバー関連のUGAを格納するには、初期化パラメータLARGE_POOL_SIZE
に値を指定します。どのプール(共有プールまたはラージ・プール)にオブジェクト用のメモリーが存在するかを確認するには、V$SGASTAT
で列POOL
をチェックします。ラージ・プールはデフォルトで構成されません。最小値は300KBです。ラージ・プールを構成しないと、共有サーバー・ユーザー・セッション・メモリーに共有プールが使用されます。
ラージ・プールの大きさは、同時にアクティブとなるセッションの数を基準に構成します。各アプリケーションは、必要なセッション情報メモリー量がそれぞれ異なり、ラージ・プールあるいはSGAの構成はメモリー要件を反映する必要があります。たとえば、アクティブな各セッションのセッション情報を格納するために共有サーバーが200〜300KBを必要とすると仮定します。100個のセッションが同時にアクティブになると予想される場合、30MBのラージ・プールを構成するか、ラージ・プールを構成しない場合は、共有プールを増やしてください。
注意: 共有サーバー・アーキテクチャを使用する場合、ラージ・プールを構成した場合でも、Oracleによって各構成セッションに一定量のメモリー(約10KB)が共有プールから割り当てられます。CIRCUITS 初期化パラメータは、データベースで許可される同時共有サーバー接続最大数を指定します。 |
関連項目:
|
Oracleが使用するUGAの厳密な容量は、各アプリケーションによって異なります。ラージ・プールまたは共有プールの効果的な設定を判別するには、一般的なユーザーでのUGAの使用状況を観察して、その容量をユーザー・セッションの見積り数に乗算します。
共有サーバーの使用により共有メモリーの使用が増加するとしても、合計のメモリー使用量は減少します。これは、プロセス数が減少するので、専用サーバー環境と比較した場合に共有サーバーではPGAメモリーの使用量が減るためです。
注意: 共有サーバーを使用したソートのパフォーマンスを最高にするには、SORT_AREA_SIZE とSORT_AREA_RETAINED_SIZE を同じ値に設定します。これによって、ソート結果をディスクに書き込むのではなくラージ・プールに留めておきます。 |
Oracleでは、セッションに使用された全メモリーの統計が収集され、動的パフォーマンス・ビューV$SESSTAT
に格納されます。表7-4はこれらの統計をリストしたものです。
値を検索するには、V$STATNAME
を問い合せます。共有サーバーを使用している場合、次の問合せを使用して、どの程度共有プールを大きくするか判断できます。アプリケーションの実行中に、次の問合せを発行してください。
SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS" FROM V$SESSTAT, V$STATNAME WHERE NAME = 'session uga memory' AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#; SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS" FROM V$SESSTAT, V$STATNAME WHERE NAME = 'session uga memory max' AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
また、これらの問合せでは、動的パフォーマンス・ビューV$STATNAME
から選択して、session memory
とmax session memory
の内部識別子を取得します。次にこれらの問合せの結果例を示します。
TOTAL MEMORY FOR ALL SESSIONS ----------------------------- 157125 BYTES TOTAL MAX MEM FOR ALL SESSIONS ------------------------------ 417381 BYTES
最初の問合せの結果は、現在、全セッションに割り当てられているメモリーは157,125バイトであることを示しています。この値は、セッションがOracleに接続されている方法にその位置が依存するメモリーの全体の容量です。セッションが専用サーバー・プロセスで接続されている場合、このメモリーはユーザー・プロセスのメモリーの一部です。セッションが共有サーバー・プロセスで接続されている場合、このメモリーは共有プールの一部です。
2番目の問合せの結果は、全セッションのメモリーの最大サイズの合計が417,381バイトであることを示しています。2番目の結果は、いくつかのセッションが最大の容量を割り当てた後でメモリーを割当て解除したため、最初の結果よりも大きくなっています。
共有サーバー・アーキテクチャを使用している場合、これらの問合せの結果を使用してどの程度共有プールを大きくするか判断できます。全セッションがほとんど同時にそれらの最大割当てに到達しそうでないかぎり、2番目の値よりも最初の値の方がよい見積りになります。
PRIVATE_SGA
リソース制限を設定して、各クライアント・セッションによるSGAのメモリー使用量を制限できます。PRIVATE_SGA
によって、1セッションでSGAから使用されるメモリーのバイト数が定義されます。ただし、ほとんどのDBAはユーザー単位でのSGA消費量の制限は行わないため、このパラメータを使用することはほとんどありません。
関連項目: PRIVATE_SGA リソース制限の設定の詳細は、『Oracle Database SQL言語リファレンス』のALTER RESOURCE COST 文に関する項を参照してください。 |
接続ユーザーが非常に多数の場合は、3層の接続を実装することでメモリー使用を低減できます。これはトランザクション処理(TP)モニター使用の副産物であり、ロックやコミットされていないDMLを複数のコールにわたって保持できないため、純粋なトランザクション・モデルでしか実現できません。共有サーバー環境には次の利点があります。
TPモニターに比べてアプリケーション設計の制限が大幅に少なくなります。
ユーザーがサーバーのプールを共有できるので、オペレーティング・システム・プロセス数とコンテキストの切替えが大幅に減ります。
共有サーバー・モードでさらに多くのSGAが使用される場合でも総メモリー使用量が大幅に減ります。
ライブラリ・キャッシュ・ミスがない場合も、初期化パラメータCURSOR_SPACE_FOR_TIME
の値をtrue
に設定することによって実行コールを高速化できる可能性があります。このパラメータは、新しいSQL文の領域を作成するために、ライブラリ・キャッシュからカーソルの割当てを解除するかどうかを指定します。CURSOR_SPACE_FOR_TIME
の値には次の意味があります。
CURSOR_SPACE_FOR_TIME
がfalse
に設定されていると(デフォルト)、SQL文に対応付けられているアプリケーション・カーソルがオープンされているかどうかにかかわらず、ライブラリ・キャッシュからカーソルの割当てを解除できます。この場合、Oracleでは、SQL文を含むカーソルがライブラリ・キャッシュ内にあることを検証する必要があります。
CURSOR_SPACE_FOR_TIME
をtrue
に設定すると、その文に関連するすべてのアプリケーション・カーソルがクローズされる場合のみカーソルの割当てを解除できます。この場合、カーソルに関連するアプリケーション・カーソルがオープンしている間はそのカーソルの割当てを解除できないため、カーソルがキャッシュ内にあるかどうかを確認する必要はありません。
パラメータの値をtrue
に設定することで、Oracle側の時間が少し短縮されるので、わずかながら実行コールのパフォーマンスが改善する可能性があります。この値は、対応付けられているアプリケーション・カーソルがクローズされるまでカーソルの割当て解除も防ぎます。
実行コールでライブラリ・キャッシュ・ミスがあった場合は、CURSOR_SPACE_FOR_TIME
の値をtrue
に設定しないでください。そのようなライブラリ・キャッシュ・ミスは、共有プールが十分大きくないので同時にオープンしている全カーソルの共有SQL領域を保持できないことを示しています。値がtrue
であり、共有プール内に新しいSQL文のための領域がない場合、文は解析されず、共有メモリーがなくなったことを示すエラーがOracleによって戻されます。値がfalse
であり、そして新しい文のための領域がない場合には、Oracleが既存のカーソルの割当てを解除します。カーソルの割当てを解除するとライブラリ・キャッシュ・ミスが後で発生します(カーソルが再度実行される場合のみ)が、SQL文が解析できないため、アプリケーションを停止させるエラーよりも望ましい対処と言えます。
各ユーザーに利用できるプライベートSQL領域のメモリー量が不十分な場合、CURSOR_SPACE_FOR_TIME
の値をtrue
に設定しないでください。また、この値は、オープンしているカーソルに対応付けられているプライベートSQL領域の割当て解除も防ぎます。同時にオープンしているすべてのカーソルのプライベートSQL領域が使用可能メモリーを満たしているために、新しいSQL文の領域がない場合は、文を解析できません。Oracleは、メモリーが十分にないことを示すエラーを戻します。
アプリケーションから何度も同じSQL文で解析コールが発行される場合、セッション・カーソルの再オープンがシステム・パフォーマンスに影響を及ぼすことがあります。パフォーマンスへの影響を最小限に抑えるために、セッション・カーソルをセッション・カーソル・キャッシュに保存できます。これらのカーソルは、アプリケーションによりクローズされており、再利用できます。フォーム間で切替えを行うと、最初のフォームに関連するすべてのセッション・カーソルがクローズされるため、この機能は、Oracle Formsが使用されているアプリケーションで特に有用です。
Oracleでは、ライブラリ・キャッシュをチェックして、指定の文で3回以上の解析リクエストが発行されたかどうかを識別します。発行された場合、Oracleでは、文に関連するセッション・カーソルをキャッシュすることを想定し、カーソルをセッション・カーソル・キャッシュに移動します。同じセッションでそのSQL文の解析リクエストが続けて出されると、セッション・カーソル・キャッシュ内のカーソルが検索されます。
セッション・カーソルのキャッシュを使用可能にするには、初期化パラメータSESSION_CACHED_CURSORS
を設定する必要があります。このパラメータの値は、キャッシュに保持されるセッション・カーソルの最大数を指定する正の整数です。LRUのアルゴリズムでは、必要に応じてセッション・カーソル・キャッシュ内の項目を除去し、新しい項目のための空間を作成します。
また次の文を使用すると、セッション・カーソル・キャッシュを動的に使用可能にすることもできます。
ALTER SESSION SET SESSION_CACHED_CURSORS = value;
セッション・カーソル・キャッシュがインスタンスに対して十分な大きさであるかどうかを判断するには、V$SYSSTAT
ビュー内のセッション統計(session cursor cache hits
)を調べます。この統計では、解析コールによってセッション・カーソル・キャッシュ内でカーソルが検出された回数を数えます。この統計で、セッションの合計解析コール数が相対的に低い割合である場合には、SESSION_CACHED_CURSORS
を大きい値に設定してください。
非常に大きいメモリーのリクエストは小さいチャンクに分割されますが、システムによっては、メモリーの連続チャンク(たとえば、5KB以上)を検索する必要性が依然存在する場合があります。(デフォルトの最小予約プールの割当ては4400バイトです。)
共有プールに十分な空き領域がない場合は、このリクエストを満たすための十分な空きメモリーを検索する必要があります。この操作では、検出可能期間にラッチ・リソースを保持するため、メモリー割当てで他の同時動作に対して多少の影響が生じる可能性があります。
したがって、共有プールに十分な領域がない場合、Oracleは使用できる共有プールに内部的に小さいメモリー領域を予約します。この予約プールによって、大きいチャンクの割当てがより効率的に行われます。
デフォルトでは、小さな予約プールを構成します。このメモリーは、PL/SQLおよびトリガーのコンパイルなどの操作や、Javaオブジェクトのロード時の一時領域に使用できます。予約プールから割り当てられたメモリーが解放されると、予約プールに戻ります。
予約されるデフォルトの領域量を変更する必要はほとんどありません。ただし、必要であれば、SHARED_POOL_RESERVED_SIZE
初期化パラメータを設定して予約プール・サイズを変更できます。このパラメータは、極端に大きい割当て用の領域を共有プール内に確保します。
大きい割当ての場合、Oracleは次の順序で共有プールへの領域の割当てを試行します。
共有プールの予約されていない部分。
予約プール。共有プールの予約されていない部分に十分な領域がない場合は、予約プールに十分な領域があるかどうかチェックされます。
メモリー。共有プールの予約されていない部分と予約された部分に十分な領域がない場合は、Oracleは割当てのために十分なメモリーの解放を試みます。次に、共有プールの予約されていない部分と予約されている部分が再試行されます。
SHARED_POOL_RESERVED_SIZE
のデフォルト値はSHARED_POOL_SIZE
の5%です。つまり、デフォルトでは、予約リストは構成されています。
SHARED_POOL_RESERVED_SIZE
をSHARED_POOL_SIZE
の半分を超える量に設定すると、Oracleはエラー信号を出します。予約プールにメモリーをあまり多くは予約できません。ただし、オペレーティング・システムのメモリー容量が共有プールのサイズを制約する場合があります。一般的には、SHARED_POOL_RESERVED_SIZE
はSHARED_POOL_SIZE
の10%に設定します。すでに共有プールのチューニングを済ませている場合、ほとんどのシステムではこの値で十分です。この値を大きくすると、データベースは共有プールからメモリーを取り出します。(このため、それより小さい割当てに使用可能な予約されていない共有プールのメモリーの量が減少します。)
V$SHARED_POOL_RESERVED
ビューの統計を使用すると、これらのパラメータをチューニングするのに役立ちます。SGAのサイズを大きくするための空きメモリーが豊富にあるシステムでは、REQUEST_MISSES
の値を0(ゼロ)にすることが目標です。オペレーティング・システム・メモリーに制約があるシステムの場合は、REQUEST_FAILURES
をなくすことが目標で、少なくともこの値が増加しないようにします。
これらの目標値が達成できない場合は、SHARED_POOL_RESERVED_SIZE
の値を増やしてください。また、予約リストは共有プールから取られるため、SHARED_POOL_SIZE
の値も同じだけ増やします。
関連項目: LARGE_POOL_SIZE パラメータ設定の詳細は、『Oracle Databaseリファレンス』を参照してください。 |
REQUEST_FAILURES
の値がゼロよりも大きく、増加している場合は、予約プールが小さすぎます。SHARED_POOL_RESERVED_SIZE
とSHARED_POOL_SIZE
の値をそれぞれ増やすと、これを解決できます。これらのパラメータで選択する設定は、システムのSGAサイズの制約によって異なります。
SHARED_POOL_RESERVED_SIZE
の値を増やすと、予約リストで利用可能なメモリーの容量が増えます。予約リストからメモリーを割り当てないユーザーには影響がありません。
予約リストに割り当てられているメモリーが多すぎることがあります。次の場合です。
REQUEST_MISSES
がゼロの場合(または増加しない場合)
FREE_MEMORY
の最小値がSHARED_POOL_RESERVED_SIZE
の50%以上になる場合
これらの条件のどちらかが真の場合、SHARED_POOL_RESERVED_SIZE
の値を減らします。
エントリが共有プールにロードされた後は、それを移動することはできません。エントリがロードされ、除去されると、空きメモリーが断片化されることもあります。
共有プールを管理するには、PL/SQLパッケージDBMS_SHARED_POOL
を使用します。共有SQL領域と共有PL/SQL領域は、古くなるとLRUアルゴリズムによって共有プールから除去されます(これはデータベース・バッファの場合と似ています)。パフォーマンスを改善したり、再解析が行われないようにするために、サイズの大きいSQL領域またはPL/SQL領域が古くなって共有プールから除去されないようにすることが可能です。
DBMS_SHARED_POOL
パッケージを使用すると、オブジェクトが共有メモリー内に維持されるため、これらのオブジェクトは通常のLRUメカニズムによって除去されることはありません。DBMS_SHARED_POOL
パッケージを使用し、SQL領域とPL/SQL領域をメモリーの断片化が発生する前にロードすると、オブジェクトはメモリー内に維持されます。こうすることによって、メモリーが確実に使用可能になり、SQL領域とPL/SQL領域の除去後にこれらの領域にアクセスする場合に、ユーザーのレスポンス時間中に突然原因不明のスローダウンが発生するのを防ぐことができます。
DBMS_SHARED_POOL
パッケージは、次の場合に便利です。
STANDARD
やDIUTIL
パッケージなどの大きなPL/SQLオブジェクトをロードする場合。大きなPL/SQLオブジェクトがロードされる場合で、領域を確保するために小さなオブジェクトを共有プールから除去する必要がある場合は、ユーザーのレスポンス時間に影響が現れます。場合によっては、このような大きなオブジェクトをロードするには、メモリーが十分でないこともあります。
頻繁に実行されるトリガー。頻繁に使用する表のコンパイル済トリガーを共有プール内に維持できます。
DBMS_SHARED_POOL
が順序もサポートする場合。共有プールから順序が除去されると、順序番号が失われます。DBMS_SHARED_POOL
は、共有プール内に順序を保持し、順序番号の消失を防ぎます。
DBMS_SHARED_POOL
パッケージを使用してSQL領域またはPL/SQL領域を確保するには、次の手順を実行してください。
メモリー内に確保しておくパッケージまたはカーソルを決定します。
データベースを起動します。
DBMS_SHARED_POOL.KEEP
をコールしてオブジェクトを確保します。
この手順により、保存されているオブジェクトがロードされる前にシステムの共有メモリーが使用し尽くされないことが保証されます。その結果、オブジェクトをインスタンスの早い時期に確保することにより、大きなメモリー領域を共有プールの中央に確保するために発生する可能性のある、メモリーの断片化を防ぐことができます。
関連項目: DBMS_SHARED_POOL プロシージャの使用方法の詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 |
解析の第1段階の1つは、文のテキストを共有プール内の既存の文と比較して、その文を共有できるかどうかを確認することです。文をテキストとして比較し、なんらかの点で異なる場合は、文は共有されません。
例外は、CURSOR_SHARING
パラメータがSIMILAR
またはFORCE
に設定されている場合です。このパラメータを使用する場合、まず共有プールがチェックされ、共有プールに同一の文があるかどうかが確認されます。同一の文が検出されないと、共有プール内で類似する文が検索されます。類似する文があると、解析チェックが引き続き行われ、カーソルの実行可能フォームを使用できるかどうかが検証されます。文がない場合は、文の実行可能フォームを生成するためにハード解析が必要になります。
いくつかのリテラル値以外が同一である文は、類似文と呼ばれます。CURSOR_SHARING
パラメータがSIMILAR
またはFORCE
に設定されると、類似文は解析フェーズでテキスト・チェックを省略します。テキストの類似性では、共有は保証されません。SQL文の新しいフォームでは、解析フェーズの残りのステップを実行して、既存の文の実行計画が新しい文にも同じように適用できるかどうかを確認する必要があります。
CURSOR_SHARING
をEXACT
に設定すると、SQL文はテキストがまったく同一の場合にのみSQL領域を共有できます。これはデフォルトの動作です。この設定では、類似文は共有できません。テキストとしての完全に同一の文のみ共有できます。
CURSOR_SHARING
をSIMILAR
またはFORCE
に設定すると、類似文がSQLを共有できます。SIMILAR
とFORCE
の違いは、実行計画を機能低下させることなくSIMILAR
が類似する文にSQL領域を共有させるという点です。CURSOR_SHARING
をFORCE
に設定すると、類似文に実行可能なSQL領域を共有するように強制します。この方法には、潜在的に実行計画の機能を低下させる可能性があります。したがって、計画が最適なものではなくなる可能性があってもカーソル共有率の向上を優先する場合に、FORCE
を最後の手段として使用してください。
CURSOR_SHARING
初期化パラメータにより一部のパフォーマンス問題を解決できる場合があります。初期化パラメータには、FORCE
、SIMILAR
およびEXACT
(デフォルト)の値があります。このパラメータの使用は、多数の類似SQL文を持つ既存のアプリケーションにとっては有益です。
注意: 複雑な問合せを使用している場合は、DSS環境でCURSOR_SHARING をFORCE に設定することはお薦めしません。また、CURSOR_SHARING がSIMILAR またはFORCE に設定されると、スター型変換はサポートされません。詳細は、「問合せオプティマイザ機能の有効化」を参照してください。 |
最適な解決方法は、CURSOR_SHARING
パラメータに依存するのではなく、共有可能なSQLを書くことです。これは、CURSOR_SHARING
によってハード解析がなくなる分、使用されるリソース量は大幅に削減されますが、共有プール内で類似文を検索するために、ソフト解析の一部としてある程度の追加作業が必要になるからです。
注意: CURSOR_SHARING をSIMILAR またはFORCE に設定すると、(SELECT 文に指定した)リテラルを含む選択された式の最大長(DESCRIBE からの戻り値)が増加します。ただし、戻されたデータの実際の長さは変わりません。 |
次の質問の両方に該当する場合は、CURSOR_SHARING
をSIMILAR
またはFORCE
に設定することを考慮してください。
共有プール内にリテラルの値のみが異なる文がありますか。
レスポンス時間は、ライブラリ・キャッシュ・ミス数が非常に多いために遅くなっていますか。
注意: CURSOR_SHARING をFORCE またはSIMILAR に設定すると、CURSOR_SHARING がEXACT に設定された状態で生成されたリテラルを持つアウトラインは使用されません。
|
CURSOR_SHARING
= SIMILAR
(またはFORCE
)を使用すると、多数の類似文を持ついくつかのアプリケーション上でのカーソルの共有を大幅に向上できるため、メモリー使用量が削減され、解析が高速になり、ラッチ競合が減少します。
中間層を持つ大きなOLTPアプリケーションでは、データベース・リクエストごとに接続と切断を行うのではなく、接続を維持するようにします。永続的な接続を維持することで、ラッチなどのCPUリソースとデータベース・リソースが節約されます。
バッファ・キャッシュ内のデータ・ブロックに変更を行うサーバー・プロセスでは、REDOデータをログ・バッファに生成します。LGWRは、次のいずれかに当てはまる場合に、REDOログ・バッファからオンラインREDOログにエントリをコピーする書込みを開始します。
ログ・バッファの1/3が満たされる。
LGWRが、COMMIT
またはROLLBACK
を実行するサーバー・プロセスによって通知される。
DBWRが、書込みをするようにLGWRに通知する。
LGWRがREDOログ・ファイルにREDOログ・バッファからREDOエントリを書き込むとき、ユーザー・プロセスはディスクに書き込まれたメモリー内のエントリ上に新しいエントリをコピーできます。REDOログへのアクセスが激しいときでも、通常LGWRは高速に書込みを行い、新しいエントリのバッファ内の領域が利用できることを保証します。
大きいバッファにより、新しいエントリのための領域がある可能性が高くなります。また、LGWRに、REDOレコードを効率よく書き出す機会を与えます(大規模な更新を行うシステム上の小さすぎるログ・バッファは、LGWRがREDOを継続的にディスクにフラッシュすることになり、ログ・バッファは2/3が空白のままになります)。
高速のプロセッサと比較的低速のディスクを持つマシンでは、REDOログ・ライターによってバッファの一部がディスクに移動される時間に、プロセッサがバッファの残りにデータを挿入していることがあります。この状況では、大きいログ・バッファは低速のディスクの影響を一時的に隠すことがあります。次のような方法も選択できます。
チェックポイント機能またはアーカイブ・プロセスを改善する。
すべてのオンライン・ログを高速のRAWデバイスに移動するなどの方法で、ログ・ライターのパフォーマンスを改善する。
REDOログ・バッファの有効利用の簡単な例を示します。
ログ・ライターがREDOログ・エントリを効率的に書き込めるようにする、バッチ・ジョブに対する一括コミット操作
大量のデータをロードするときのNOLOGGING
操作の使用
REDOログ・バッファのサイズは、初期化パラメータLOG_BUFFER
で決定されます。ログ・バッファ・サイズは、インスタンス起動後には変更できません。
大量のデータを挿入、変更または削除するアプリケーションは、通常、デフォルトのログ・バッファ・サイズを変更する必要があります。ログ・バッファは総SGAサイズと比較すると小さく、中規模サイズのログ・バッファは、多数の更新を実行するシステムでのスループットを大幅に向上させます。
そのようなシステムにおける合理的な最初の見積りはデフォルト値に対するもので、次のとおりです。
MAX(0.5M, (128K * number of cpus))
大半のシステムでは、ログ・バッファを1MBより大きくサイズ設定しても、パフォーマンスの利点が得られません。バッファ・サイズを増やしても、パフォーマンスまたはリカバリ能力に対してマイナスの影響を及ぼしません。単に追加のメモリーが使用されます。
統計REDO BUFFER ALLOCATION RETRIES
は、ユーザー・プロセスがREDOログ・バッファ内の領域の使用を待機した回数を反映します。この統計は、動的パフォーマンス・ビューV$SYSSTAT
で問い合せることができます。
次の問合せによって、アプリケーションの実行中に、ある程度の期間にわたってこれらの統計を監視します。
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME = 'redo buffer allocation retries';
redo buffer allocation retries
の値は、ある時間間隔に対して0(ゼロ)に近い値である必要があります。この値が一貫して増分する場合は、プロセスがREDOログ・バッファ内の領域を待機する必要があったということです。この待機は、ログ・バッファが小さすぎること、あるいはチェックポイント機能が原因となっていることがあります。必要であれば、初期化パラメータのLOG_BUFFER
の値を変更することによって、REDOログ・バッファのサイズを大きくできます。このパラメータの値はバイト単位で表されます。あるいは、チェックポイント機能またはアーカイブ・プロセスを改善してください。
別のデータ・ソースは、log buffer space
待機イベントがインスタンスの待機時間における重要な要因でないことをチェックするためのものです。重要な要因でなければ、バッファ・サイズはほぼ適切です。
プログラム・グローバル領域(PGA)は、サーバー・プロセスのデータおよび制御情報を含むプライベート・メモリー領域です。この領域に対するアクセスはそのサーバー・プロセスに対して排他的であり、そのかわりの役割を果すOracleコードでのみ読取りおよび書込みが行われます。そのような情報の例として、カーソルのランタイム領域があります。カーソルを実行するたびに、そのカーソルを実行するサーバー・プロセスのPGAメモリー領域内に、そのカーソルのための新しいランタイム領域が作成されます。
注意: ランタイム領域の一部は、共有サーバーを使用するときにSGA内に配置できます。 |
複雑な問合せ(たとえば、意思決定支援の問合せ)の場合、ランタイム領域の大部分が、次のようなメモリー集約型演算子で割り当てられた作業領域に使用されます。
ソート・ベース演算子(たとえば、ORDER BY
、GROUP BY
、ROLLUP
およびウィンドウ・ファンクション)
ハッシュ結合
ビットマップ・マージ
ビットマップ作成
一括ロード操作で使用される書込みバッファ
ソート演算子は、作業領域(ソート領域)を使用して一連の行のメモリー内ソートを実行します。同様に、ハッシュ結合演算子は作業領域(ハッシュ領域)を使用して、ハッシュ表を左側から入力して作成します。
作業領域のサイズは、制御およびチューニングできます。一般に、作業領域を大きくすると、メモリー消費量は増えますが特定の演算子のパフォーマンスを大幅に向上できます。作業領域のサイズは、関連するSQL演算子で割り当てられた入力データや補助メモリー構造を十分収容できるほど大きなサイズが理想的です。これが作業領域の最適サイズとされます。作業領域のサイズが最適なサイズより小さい場合は、入力データの部分に対して追加のパスが実行されるので、レスポンス時間は増えます。これは、作業領域のワン・パス・サイズと呼ばれます。ワン・パスしきい値以下の場合は、作業領域のサイズが入力データ・サイズに比べて小さすぎる場合に入力データに対する複数のパスが必要です。このため、演算子のレスポンス時間が大幅に増加する可能性があります。これは、作業領域のマルチ・パス・サイズと呼ばれます。たとえば、10GBのデータをソートするシリアル・ソート操作では、最適なサイズで実行するには10GBよりも少し多めが必要で、ワン・パスで実行するには少なくとも40MBが必要です。このソートが40MBより少ない取得を行う場合は、入力データに対して複数のパスを実行する必要があります。
目標は、最適なサイズ(たとえば、90%を超える、またはOLTPシステム固有の場合は100%)で大半の作業領域を動作させ、その一部をワン・パス・サイズ(たとえば、10%未満)で動作させることです。マルチ・パスの実行は避けてください。大きなソートとハッシュ結合を実行するDSSシステムの場合であっても、ワン・パスの実行のメモリー要件は相対的に少ない量です。妥当なPGAメモリー量で構成されたシステムは、入力データに対してマルチ・パスを実行する必要がありません。
自動PGAメモリー管理により、PGAメモリーの割当て方法が単純化され改善されます。デフォルトでは、PGAメモリー管理は有効化されます。このモードでは、SGAメモリー・サイズの20%をベースとして、作業領域専用のPGAメモリーのサイズが動的に調整されます。最小値は10MBです。
注意: 下位互換性のために、PGA_AGGREGATE_TARGET 初期化パラメータを0(ゼロ)に設定して、自動PGAメモリー管理を無効にできます。自動PGAメモリー管理が無効になっている場合は、SORT_AREA_SIZE 初期化パラメータなどの関連_AREA_SIZE パラメータを使用して作業領域の最大サイズを設定できます。
|
自動PGAメモリー管理モードで実行すると、すべてのセッションの作業領域のサイズ設定は自動になり、*_AREA_SIZE
パラメータはそのモードで動作するすべてのセッションで無視されます。常に、インスタンスでアクティブな作業領域に使用できるPGAメモリーの総量は、PGA_AGGREGATE_TARGET
初期化パラメータから自動的に導出されます。この量は、システムの他のコンポーネントで割り当てられたPGAメモリー(たとえば、セッションで割り当てられたPGAメモリー)の量をPGA_AGGREGATE_TARGET
から減算した値に設定されます。次に、その結果のPGAメモリーは、それぞれ特定のメモリー要件に基づいて個々のアクティブな作業領域に割り当てられます。
自動PGAメモリー管理モードにおいてOracleが主な目標としたものは、SQL作業領域に割り当てられるPGAメモリーの量を動的に制御することで、DBAが設定したPGA_AGGREGATE_TARGET
の制限を満たすことです。これと同時に、使用するPGAメモリー(キャッシュ・メモリー)の量が最適である作業領域の数を最大にして、すべてのメモリー集中型SQL操作のパフォーマンスを最大限に引き出す試みも行っています。パラメータPGA_AGGREGATE_TARGET
でDBAにより設定されたPGAメモリーの制限が低すぎて、マルチ・パスを実行してPGAメモリーの消費をさらに削減して、PGAのターゲット制限を満たす必要がある場合を除き、残りの作業領域はワン・パス・モードで実行されます。
新規インスタンスを構成する場合には、PGA_AGGREGATE_TARGET
の適切な設定を正確に知ることは困難です。この設定は、次の3つの段階を実行して判別します。
PGA_AGGREGATE_TARGET
の最初の見積りは経験に基づいて行う。デフォルトでは、SGAサイズの20%が使用されます。ただし、この初期設定は、大規模DSSシステムには小さすぎる場合があります。
インスタンスで代理のワークロードを実行し、Oracleにより収集されたPGA統計を使用してパフォーマンスを監視して、最大PGAサイズが高く構成されたか低く構成されたかを確認する。
Oracle PGAのアドバイスの統計を使用して、PGA_AGGREGATE_TARGET
をチューニングする。
関連項目: PGA_AGGREGATE_TARGET 初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
これについては、次の項で詳しく説明します。
Oracleインスタンスに使用できる総メモリー量に基づいて、PGA_AGGREGATE_TARGET
初期化パラメータの値(たとえば、100000KB、2500MB、50GBなど)を設定する必要があります。この値は後からインスタンス・レベルでチューニングしたり動的に変更できます。例7-2に一般的な状況を示します。
例7-2 PGA_AGGREGATE_TARGETの初期設定
Oracleインスタンスが4GBの物理メモリーを持つシステム上で動作するように設定されていると仮定します。そのメモリーの一部は、オペレーティング・システムと同じハードウェア・システムで動作しているその他のOracle以外のアプリケーションに残しておく必要があります。たとえば、使用可能なメモリーの80%(3.2GB)のみをOracleインスタンス専用にできます。
次に、残りのメモリーをSGAとPGAに分割する必要があります。
OLTPシステムの場合、PGAメモリーの割合は使用可能なメモリーの総量の一部(例: 20%がPGA用、80%がSGA用)とするのが普通です。
メモリー集中型の大きな問合せを実行するDSSシステムの場合、PGAメモリーには総量の最大70%(この例では最大2.2GB)までを使用できます。
PGA_AGGREGATE_TARGET
パラメータの適切な初期値の例を次に示します。
OLTPの場合: PGA_AGGREGATE_TARGET
= (total_mem
* 80%) * 20%
DSSの場合: PGA_AGGREGATE_TARGET
= (total_mem
* 80%) * 50%
total_mem
はシステムで使用可能な物理メモリーの総量です。
この例では、4GBのtotal_mem
の値を使用することにより、PGA_AGGREGATE_TARGET
をDSSシステムの場合は1600MBに、OLTPシステムの場合は655MBに初期設定できます。
チューニング・プロセスを開始する前に、Oracle Databaseで収集される主要統計の監視および解釈方法を理解して、自動PGAメモリー管理コンポーネントのパフォーマンスを評価する場合の参考にする必要があります。そのための動的パフォーマンス・ビューの例を次に示します。
このビューは、PGAメモリー使用量および自動PGAメモリー・マネージャに関するインスタンス・レベルの統計を示します。たとえば、次のようにします。
SELECT * FROM V$PGASTAT;
この問合せの出力例を次に示します。
NAME VALUE UNIT -------------------------------------------------------- ---------- ------------ aggregate PGA target parameter 41156608 bytes aggregate PGA auto target 21823488 bytes global memory bound 2057216 bytes total PGA inuse 16899072 bytes total PGA allocated 35014656 bytes maximum PGA allocated 136795136 bytes total freeable PGA memory 524288 bytes PGA memory freed back to OS 1713242112 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 2383872 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 8470528 bytes over allocation count 291 bytes processed 2124600320 bytes extra bytes read/written 39949312 bytes cache hit percentage 98.15 percent
V$PGASTAT
に表示される主な統計は次のとおりです。
aggregate PGA target parameter
: これは初期化パラメータPGA_AGGREGATE_TARGET
の現在の値です。デフォルト値は、SGAサイズの20%です。このパラメータを0(ゼロ)に設定すると、PGAメモリーの自動管理は無効になります。
aggregate PGA auto target
: 自動モードで実行する作業領域に使用できるPGAメモリーの量を示します。この量は、PGA_AGGREGATE_TARGET
パラメータの値と現在の作業領域のワークロードから動的に導出されます。したがって、Oracleで継続的に調整されます。この値がPGA_AGGREGATE_TARGET
と比べて小さい場合、多くのPGAメモリーがシステムの他のコンポーネント(たとえば、PL/SQLやJavaメモリー)で使用され、ソート作業領域にはメモリーがほとんど残されていません。自動モードで実行される作業領域には十分なPGAメモリーが残されている必要があります。
global memory bound
: AUTO
モードで実行された作業領域の最大サイズを示します。この値は、作業領域のワークロードの現在の状態を反映するように継続的に調整されます。通常は、システム内のアクティブな作業領域の数が増えると、グローバル・メモリー・バウンドが縮小します。一般的には、グローバル・バウンドの値は1MBを下回らないようにする必要があります。1MB未満になった場合は、PGA_AGGREGATE_TARGET
の値を増やす必要があります。
total PGA allocated
: インスタンスによって割り当てられた現在のPGAメモリー量を示します。通常この値は、PGA_AGGREGATE_TARGET
の値未満に維持されます。ただし、作業領域のワークロードが急速に増えている場合や、初期化パラメータPGA_AGGREGATE_TARGET
の設定値が小さすぎる場合は、少量かつ短時間、その値を超過したPGAが割り当てられることがあります。
total freeable PGA memory
: 割当て済で解放可能なPGAメモリーの量を示します。
total PGA used for auto workareas
: 自動メモリー管理モードで実行する作業領域で現在消費されているPGAメモリーの量を示します。この数値から、PGAメモリーの他のコンシューマ(たとえば、PL/SQLやJava)で消費されるメモリーの量を判断できます。
PGA other = total PGA allocated - total PGA used for auto workareas
over allocation count
: この統計は、インスタンスの起動時から累積されます。PGA_AGGREGATE_TARGET
の値が小さすぎて、前述の等式のPGA other
コンポーネントと、作業領域のワークロードを実行するために必要な最小メモリーに対応できない場合は、PGAメモリーの過剰割当てとなる可能性があります。その場合、Oracleは初期化パラメータPGA_AGGREGATE_TARGET
を満たすことができないため、追加のPGAメモリーを割り当てる必要があります。過剰割当てが発生した場合は、アドバイス・ビューV$PGA_TARGET_ADVICE
の情報を使用して、PGA_AGGREGATE_TARGET
の値を増やす必要があります。
total bytes processed
: インスタンスの起動後にメモリー集中型SQL演算子によって処理されたバイト数を示します。たとえば、ソート操作の入力サイズが、処理されたバイト数によって示されます。この数値はcache hit percentage
メトリックを計算する場合に使用します。
extra bytes read/written
: 作業領域が最適に実行できない場合は、1つ以上の余分なパスが入力データで実行されています。extra bytes read/written
は、インスタンス起動後にこれらのパスで処理されたバイト数を示します。この数値はcache hit percentage
を計算する場合にも使用します。total bytes processed
に比べて小さい値であることが理想的です。
cache hit percentage
: このメトリックはOracle Databaseによって計算され、PGAメモリー・コンポーネントのパフォーマンスを反映します。この値はインスタンスの起動時から累積されます。値が100%の場合は、インスタンス起動後にシステムが実行したすべての作業領域で、最適な量のPGAメモリーが使用されたことを意味します。それが理想的ですが、純粋なOLTPシステムなどの場合を除き、そのようになることはほとんどありません。実際には、PGAメモリーの総サイズによって、ワン・パスやマルチ・パスを実行する作業領域も発生します。作業領域が最適に実行できない場合は、1つ以上の余分なパスが入力データで実行されています。その場合、入力データのサイズと実行された余分なパス数に比例してcache hit percentage
が低下します。例7-3に、余分なパスによってcache hit percentage
が受ける影響を示します。
例7-3 キャッシュ・ヒット率の計算
4つのソート操作が実行され、そのうちの3つは小さく(1MBの入力データ)、1つは大きい(100MBの入力データ)という場合の単純事例を示します。4つの操作で処理される合計バイト数(BP
)は103MBです。小さなソートの1つがワン・パスを実行すると、1MBの入力データで余分なパスが実行されます。この1MBという値は、extra bytes read/written
(EBP
)の数を示します。cache hit percentage
は次の計算式で計算されます。
BP x 100 / (BP + EBP)
この場合のcache hit percentage
は99.03%で、ほぼ100%です。他のすべてのソートを最適に実行している間、余分なパスを実行する小さなソートが1つであったことがこの値に反映されています。したがって、cache hit percentage
はほぼ100%になりますが、それはこの1MB超の余分なパスがわずかなオーバーヘッドであるためです。一方、大きなソートがワン・パスを実行するソートの場合、EBPは1MBではなく100MBとなり、cache hit percentage
は50.73%に低下しますが、それはこの余分なパスがもたらす影響が大きくなるためです。
このビューでは、インスタンスに接続されているOracleプロセス1つにつき行が1つあります。PGA_USED_MEM
列、PGA_ALLOC_MEM
列、PGA_ALLOC_MEM
列およびPGA_MAX_MEM
列を使用して、これらのプロセスのPGAメモリー使用量を監視できます。たとえば、次のようにします。
SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM FROM V$PROCESS;
この問合せの出力例を次に示します。
PROGRAM PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM -------------------------------------- ------------ ------------- ---------------- ----------- PSEUDO 0 0 0 0 oracle@examp1690 (PMON) 314540 685860 0 685860 oracle@examp1690 (MMAN) 313992 685860 0 685860 oracle@examp1690 (DBW0) 696720 1063112 0 1063112 oracle@examp1690 (LGWR) 10835108 22967940 0 22967940 oracle@examp1690 (CKPT) 352716 710376 0 710376 oracle@examp1690 (SMON) 541508 948004 0 1603364 oracle@examp1690 (RECO) 323688 685860 0 816932 oracle@examp1690 (q001) 233508 585128 0 585128 oracle@examp1690 (QMNC) 314332 685860 0 685860 oracle@examp1690 (MMON) 885756 1996548 393216 1996548 oracle@examp1690 (MMNL) 315068 685860 0 685860 oracle@examp1690 (q000) 330872 716200 65536 716200 oracle@examp1690 (TNS V1-V3) 635768 928024 0 1255704 oracle@examp1690 (CJQ0) 533476 1013540 0 1144612 oracle@examp1690 (TNS V1-V3) 430648 812108 0 812108
このビューは、各Oracleプロセスの名前を付けられたコンポーネント・カテゴリごとに、動的PGAのメモリー使用量を表示します。このビューには、各Oracleプロセスに対して1行ずつ、6行まで(次のそれぞれに対して各1行)が含まれます。
名前を付けられた各コンポーネント・カテゴリ: Java、PL/SQL、OLAP、SQL
開放可能: オペレーティング・システムによって、特定のカテゴリにではなくプロセスに割り当てられたメモリー。
その他: 名前を付けられたカテゴリの1つではなく、1つのカテゴリに割り当てられたメモリー。
列CATEGORY
、ALLOCATED
、USED
およびMAX_ALLOCATED
を使用して、6つのカテゴリそれぞれのOracleプロセスのPGAメモリー使用量を動的に監視できます。
関連項目: V$PROCESS_MEMORY ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |
このビューには、インスタンス起動後に、最適なメモリー・サイズ、ワン・パス・メモリー・サイズおよびマルチ・パス・メモリー・サイズで実行された作業領域の総数を示します。このビューの統計は、作業領域の最適なメモリー要件によって定義されるバケットに副分割されます。各バケットは、列LOW_OPTIMAL_SIZE
およびHIGH_OPTIMAL_SIZE
の値で指定された最適メモリー要件の範囲によって識別されます。
例7-4および例7-5に、V$SQL_WORKAREA_HISTOGRAM
の2種類の使用方法を示します。
例7-4 V$SQL_WORKAREA_HISTOGRAMへの問合せ: 空でないバケット
最適に実行する(キャッシュされる)には3MBのメモリーを必要とするソート操作の事例を示します。このソートで使用される作業領域に関する統計は、LOW_OPTIMAL_SIZE = 2097152
(2MB)およびHIGH_OPTIMAL_SIZE = 4194303
(4MB - 1バイト)で定義されるバケットに配置されます。これは3MBが最適サイズの範囲内に収まるためです。統計は作業領域のサイズでセグメント化されます。最適、ワン・パスまたはマルチ・パスの各モードで作業領域を実行する場合のパフォーマンスの影響は、その作業領域のサイズに大きく依存するためです。
次の問合せでは、空でないすべてのバケットの統計が示されます。空のバケットは述語where total_execution != 0
で削除されます。
SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb, OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS FROM V$SQL_WORKAREA_HISTOGRAM WHERE TOTAL_EXECUTIONS != 0;
この問合せの結果を次に示します。
LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS ------ ------- ------------------ ------------------ ---------------------- 8 16 156255 0 0 16 32 150 0 0 32 64 89 0 0 64 128 13 0 0 128 256 60 0 0 256 512 8 0 0 512 1024 657 0 0 1024 2048 551 16 0 2048 4096 538 26 0 4096 8192 243 28 0 8192 16384 137 35 0 16384 32768 45 107 0 32768 65536 0 153 0 65536 131072 0 73 0 131072 262144 0 44 0 262144 524288 0 22 0
1024〜2048KBのバケットでは、551の作業領域で最適な量のメモリーが使用されたこと、またワンパス・モードで実行されたものが16ある一方で、マルチ・パス・モードで実行されたものはなかったことがこの問合せ結果に示されています。また、1MB未満のすべての作業領域が最適モードで実行できたことも示されています。
例7-5 V$SQL_WORKAREA_HISTOGRAMへの問合せ: 最適パーセント
V$SQL_WORKAREA_HISTOGRAM
を使用すると、起動後に作業領域が最適、ワン・パスまたはマルチ・パスの各モードで実行された回数の割合を調べることもできます。この問合せでは、一定のサイズ(最適メモリー要件が最低64KB)の作業領域のみ考慮されます。
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc, onepass_count, round(onepass_count*100/total, 2) onepass_perc, multipass_count, round(multipass_count*100/total, 2) multipass_perc FROM (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total, sum(OPTIMAL_EXECUTIONS) optimal_count, sum(ONEPASS_EXECUTIONS) onepass_count, sum(MULTIPASSES_EXECUTIONS) multipass_count FROM v$sql_workarea_histogram WHERE low_optimal_size > 64*1024);
この問合せの出力例を次に示します。
OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC ------------- ------------ ------------- ------------ --------------- -------------- 2239 81.63 504 18.37 0 0
この結果には、最適な量のメモリーを使用して実行できるのは、これらの作業領域の81.63%であることが示されています。残り(18.37%)はワン・パスで実行されました。マルチ・パスで実行されたものはありませんでした。これは望ましい状態ですが、その理由は次のとおりです。
マルチ・パス・モードでは、パフォーマンスが大幅に低下する可能性があります。マルチ・パスの作業領域が多いと、関連付けられたSQL演算子のレスポンス時間に大きな悪影響があります。
ワンパスの実行では多量のメモリーを必要としません。ワンパス・モードで1GBのデータをソートする場合に必要なメモリーはわずか22MBです。
このビューを使用すると、インスタンスでアクティブな(または実行中の)作業領域を表示できます。小さいアクティブなソート(64KB以下)はビューから除外されます。すべてのアクティブな作業領域のサイズを正確に監視したり、それらの作業領域が一時セグメントに流用されているかどうかを判断するには、このビューを使用します。例7-6に、このビューの代表的な問合せを示します。
例7-6 V$SQL_WORKAREA_ACTIVEへの問合せ
SELECT to_number(decode(SID, 65535, NULL, SID)) sid, operation_type OPERATION, trunc(EXPECTED_SIZE/1024) ESIZE, trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM", NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2; The output of this query might look like the following: SID OPERATION ESIZE MEM MAX MEM PASS TSIZE --- ----------------- --------- --------- --------- ----- ------- 8 GROUP BY (SORT) 315 280 904 0 8 HASH-JOIN 2995 2377 2430 1 20000 9 GROUP BY (SORT) 34300 22688 22688 0 11 HASH-JOIN 18044 54482 54482 0 12 HASH-JOIN 18044 11406 21406 1 120000
この出力は、作業領域がワンパス・モードで動作しているハッシュ結合(PASS
列)を、セッション12(列SID
)が実行していることを示しています。この作業領域は現在、11406KBのメモリー(MEM
列)を使用しており、過去に最大21406KBのPGAメモリー(MAX MEM
列)を使用しました。また、サイズ120000KBの一時セグメントにも流用されています。最終的に、列ESIZE
には、PGAメモリー・マネージャが予測する、このハッシュ結合での最大メモリー使用量が示されます。この最大値は、PGAメモリー・マネージャがワークロードに基づいて動的に計算します。
作業領域を割当て解除したとき、すなわち、関連するSQL演算子の実行が完了したときに、作業領域はV$SQL_WORKAREA_ACTIVE
ビューから自動的に削除されます。
実行計画が1つ以上の作業領域を使用するカーソルがロードされるたびに、累積された作業領域の統計がメンテナンスされます。作業領域が割当て解除されるたびに、V$SQL_WORKAREA
表がその作業領域の実行統計で更新されます。
V$SQL_WORKAREA
をV$SQL
と結合して、作業領域をカーソルに関連付けることができます。V$SQL_PLAN
とも結合でき、計画のどの演算子が作業領域を使用しているかを正確に判断できます。
例7-7に、V$SQL_WORKAREA
動的ビューでの代表的な問合せを3つ示します。
例7-7 V$SQL_WORKAREAへの問合せ
次の問合せでは、最もキャッシュ・メモリーを必要とする上位10個の作業領域を検索します。
SELECT * FROM ( SELECT workarea_address, operation_type, policy, estimated_optimal_size FROM V$SQL_WORKAREA ORDER BY estimated_optimal_size ) WHERE ROWNUM <= 10;
次の問合せでは、ワン・パスまたはマルチ・パスで実行された1つ以上の作業領域を持つカーソルを検索します。
col sql_text format A80 wrap SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt, sum(MULTIPASSES_EXECUTIONS) mpass_cnt FROM V$SQL s, V$SQL_WORKAREA wa WHERE s.address = wa.address GROUP BY sql_text HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;
特定のカーソルのハッシュ値とアドレスを使用することにより、関連する作業領域に関する情報を含むカーソル実行計画が次の問合せで表示されます。
col "O/1/M" format a10 col name format a20 SELECT operation, options, object_name name, trunc(bytes/1024/1024) "input(MB)", trunc(last_memory_used/1024) last_mem, trunc(estimated_optimal_size/1024) optimal_mem, trunc(estimated_onepass_size/1024) onepass_mem, decode(optimal_executions, null, null, optimal_executions||'/'||onepass_executions||'/'|| multipasses_executions) "O/1/M" FROM V$SQL_PLAN p, V$SQL_WORKAREA w WHERE p.address=w.address(+) AND p.hash_value=w.hash_value(+) AND p.id=w.operation_id(+) AND p.address='88BB460C' AND p.hash_value=3738161960; OPERATION OPTIONS NAME input(MB) LAST_MEM OPTIMAL_ME ONEPASS_ME O/1/M ------------ -------- -------- --------- -------- ---------- ---------- ------ SELECT STATE HASH GROUP BY 4582 8 16 16 16/0/0 HASH JOIN SEMI 4582 5976 5194 2187 16/0/0 TABLE ACCESS FULL ORDERS 51 TABLE ACCESS FUL LINEITEM 1000
アドレスおよびハッシュ値は、問合せでパターンを指定することによりV$SQL
ビューから取得できます。たとえば、次のようにします。
SELECT address, hash_value
FROM V$SQL
WHERE sql_text LIKE '%my_pattern%';
初期化パラメータPGA_AGGREGATE_TARGET
のチューニングを容易にするため、V$PGA_TARGET_ADVICE
およびV$PGA_TARGET_ADVICE_HISTOGRAM
というビューが提供されています。これらのビューを調べれば、経験的な方法でPGA_AGGREGATE_TARGET
の値をチューニングする必要がありません。このビューを使用すると、PGA_AGGREGATE_TARGET
の値を変更したときに、主なPGA統計がどのような影響を受けるかを調べることができます。
どちらのビューでも、可能性のある高い値および低い値を評価するため、予測に使用するPGA_AGGREGATE_TARGET
の値は、そのパラメータの現在の値の分数または倍数から導出されます。予測に使用される値の範囲は、10MB〜最大256GBです。
Oracleは、ワークロードの履歴を記録し、その履歴を異なる値のPGA_AGGREGATE_TARGET
でシミュレートすることによってPGAアドバイス・パフォーマンス・ビューを生成します。このシミュレーション・プロセスはバックグラウンドで実行され、ワークロードの履歴を継続的に更新してシミュレーション結果を生成します。その結果はV$PGA_TARGET_ADVICE
またはV$PGA_TARGET_ADVICE_HISTOGRAM
を問い合せることによっていつでも表示できます。
PGAアドバイス・パフォーマンス・ビューの自動生成を有効化するには、次のパラメータが設定されていることを確認してください。
PGA_AGGREGATE_TARGET
。自動PGAメモリー管理が有効化されます(「PGA_AGGREGATE_TARGETの初期設定」を参照)。
STATISTICS_LEVEL
。TYPICAL
(デフォルト)またはALL
に設定します。このパラメータをBASIC
に設定すると、PGAパフォーマンス・アドバイス・ビューの生成がオフになります。
これらのPGAアドバイス・パフォーマンス・ビューの内容は、インスタンス起動時またはPGA_AGGREGATE_TARGET
の変更時にリセットされます。
注意: シミュレーションに実際の実行のすべての要素を含めることはできません。したがって、導出された統計が、実際のパフォーマンス統計に完全には一致しない場合があります。PGA_AGGREGATE_TARGET を変更した後は、常にシステムを監視して、新しいパフォーマンスが予測どおりであるかどうかを確認してください。 |
このビューでは、初期化パラメータPGA_AGGREGATE_TARGET
の値を変更した場合に、V$PGASTAT
の統計cache hit percentage
およびover allocation count
がどのような影響を受けるかを予測します。例7-8に、このビューの代表的な問合せを示します。
例7-8 V$PGA_TARGET_ADVICEへの問合せ
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE;
この問合せの出力例を次に示します。
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT ---------- -------------- -------------------- 63 23 367 125 24 30 250 30 3 375 39 0 500 58 0 600 59 0 700 59 0 800 60 0 900 60 0 1000 61 0 1500 67 0 2000 76 0 3000 83 0 4000 85 0
この問合せの結果は図7-3のようなグラフになります。
この曲線は、PGA_AGGREGATE_TARGET
の値が増加するにつれて、PGAのcache hit percentage
が上昇する状況を示しています。グラフ内の影付きの部分はover allocation
ゾーンで、列ESTD_OVERALLOCATION_COUNT
の値が0(ゼロ)以外になります。これはPGAメモリーの最低所要量にも達しないほどPGA_AGGREGATE_TARGET
が小さいことを示します。PGA_AGGREGATE_TARGET
をover allocation
ゾーンに設定すると、メモリー・マネージャによってメモリーが過剰割当てされ、実際に消費されたPGAメモリーが設定された制限を超過します。したがって、PGA_AGGREGATE_TARGET
の値をそのゾーンに設定しても意味がありません。この例では、PGA_AGGREGATE_TARGET
は最低375MBに設定する必要があります。
注意: PGAcache hit percentage の理論的な最大値が100%の場合でも、作業領域の実際の最大サイズには制限があります。したがって、PGA_AGGREGATE_TARGET の値をさらに増やしても、理論的な最大値に達しない場合があります。これが発生するのは、最適メモリー要件が大きく、cache hit percentage の値が低いヒット率(90%など)に下がる可能性のある大規模DSSシステムのみです。 |
over allocation
ゾーンを超えると、PGA cache hit percentage
が急速に増加します。これは最適、またはワン・パスで実行される作業領域の数が増加し、マルチ・パス実行の数が減少するためです。この例では、500MB付近で曲線の変化が発生していますが、これはほとんどの(場合によってはすべての)作業領域が最適、または少なくともワン・パスで実行可能になるポイントに対応しています。その変化以降もcache hit percentage
は緩やかに増加し、先細りが始まってPGA_AGGREGATE_TARGET
の増加による上昇がわずかしか見られないポイントに達します。図7-3で、この状態はPGA_AGGREGATE_TARGET
が3GBに達したときに発生しています。この時点のcache hit percentage
は83%で、PGAメモリーを1GB増やしてもわずか(2%)しか向上しません。この例では、PGA_AGGREGATE_TARGET
の最適値は3GBと考えられます。
PGA_AGGREGATE_TARGET
は、最適値に設定するか、少なくともover allocation
ゾーンを超えた範囲で可能な最大値に設定するのが理想的です。一般的には、PGA cache hit percentage
は60%以上に設定します。60%の時点でシステムは、理想的な状況で実際に処理する必要のあるバイト数のほぼ2倍の量を処理するためです。この例では、PGA_AGGREGATE_TARGET
を最低500MBから3GBにできるだけ近く設定するのが理想的です。ただし、PGA_AGGREGATE_TARGET
パラメータの適切な設定は、PGAコンポーネントにどれだけのメモリーを使用できるかによって異なります。一般的に、PGAメモリーを追加する場合は、共有プールまたはバッファ・キャッシュの場合と同様に、一部のSGAコンポーネントのメモリーを減らす必要があります。これはOracleインスタンスに使用する全メモリーが、システムで使用できる物理メモリー量の制約を受ける場合があるためです。したがって、システム内で使用可能なメモリーと、様々なSGAコンポーネントのパフォーマンス(共有プールのアドバイザの統計およびバッファ・キャッシュ・アドバイザの統計で監視)という、より大きな視点で、PGAメモリーの増量の決定を下す必要があります。メモリーをSGAから取得できない場合は、システムへの物理メモリーの追加を検討します。
PGA_AGGREGATE_TARGET
をチューニングする場合は、チューニング・ガイドラインとして次の手順に従います。
メモリーが過剰割当てされないようPGA_AGGREGATE_TARGET
を設定します。過剰割当てゾーンに設定しないでください。例7-8では、PGA_AGGREGATE_TARGET
は最低375MBに設定する必要があります。
過剰割当てを解消した後、レスポンス時間の要件およびメモリーの制約に基づいてPGA cache hit percentage
を極力最大化します。例7-8では、PGAに割当て可能なメモリーに制限Xがあると仮定しています。
この制限Xが最適値を超過している場合は、PGA_AGGREGATE_TARGET
を最適値に設定します。この時点では、PGA_AGGREGATE_TARGET
へのメモリー割当ての増加に伴う有益性はごくわずかです。例7-8で10GBをPGA専用とする場合は、PGA_AGGREGATE_TARGET
を最適値である3GBに設定します。残りの7GBはSGA専用となります。
制限Xが最適値より小さい場合は、PGA_AGGREGATE_TARGET
をXに設定します。例7-8で2GBのみをPGA専用とする場合は、PGA_AGGREGATE_TARGET
を2GBに設定し、75%のcache hit percentage
を受け入れます。
さらに、Oracleで収集され、インスタンスの起動時から累積されるほとんどの統計と同様に、時間間隔の始めと終わりにおけるビューのスナップショットを取得できます。その時間間隔の予測値は次のように導出できます。
estd_overalloc_count = (difference in estd_overalloc_count between the two snapshots)
(difference in bytes_processed between the two snapshots) estd_pga_cache_hit_percentage = ----------------------------------------------------------------- (difference in bytes_processed + extra_bytes_rw between the two snapshots )
このビューは、初期化パラメータPGA_AGGREGATE_TARGET
の値を変更したときに、パフォーマンス・ビューV$SQL_WORKAREA_HISTOGRAM
に表示される統計がどのように影響を受けるかを予測します。動的ビューV$PGA_TARGET_ADVICE_HISTOGRAM
を使用すると、予測に使用する一連のPGA_AGGREGATE_TARGET
値における、最適、ワン・パス、マルチ・パスの各作業領域の予測実行回数に関する詳細情報を表示できます。
V$PGA_TARGET_ADVICE_HISTOGRAM
ビューはV$SQL_WORKAREA_HISTOGRAM
ビューと同じであり、予測に使用するPGA_AGGREGATE_TARGET
値を示す2つの追加列があります。したがって、PGA_AGGREGATE_TARGET
の希望値を選択するための追加の述語を使用し、V$SQL_WORKAREA_HISTOGRAM
ビューに対して実行される任意の問合せがこのビューで使用できます。
例7-9 V$PGA_TARGET_ADVICE_HISTOGRAMへの問合せ
次の問合せでは、初期化パラメータPGA_AGGREGATE_TARGET
の値を現在の値の2倍に設定したときの、V$SQL_WORKAREA_HISTOGRAM
の予測内容が表示されます。
SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb, estd_optimal_executions estd_opt_cnt, estd_onepass_executions estd_onepass_cnt, estd_multipasses_executions estd_mpass_cnt FROM v$pga_target_advice_histogram WHERE pga_target_factor = 2 AND estd_total_executions != 0 ORDER BY 1;
この問合せの出力例を次に示します。
LOW_KB HIGH_KB ESTD_OPTIMAL_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT ------ ------- ---------------- ---------------- -------------- 8 16 156107 0 0 16 32 148 0 0 32 64 89 0 0 64 128 13 0 0 128 256 58 0 0 256 512 10 0 0 512 1024 653 0 0 1024 2048 530 0 0 2048 4096 509 0 0 4096 8192 227 0 0 8192 16384 176 0 0 16384 32768 133 16 0 32768 65536 66 103 0 65536 131072 15 47 0 131072 262144 0 48 0 262144 524288 0 23 0
この出力は、PGA_AGGREGATE_TARGET
を2倍ずつ増やしていくと、16MB未満のすべての作業領域を最適モードで実行できることを示しています。
関連項目: 『Oracle Databaseリファレンス』 |
V$SYSSTAT
ビューとV$SESSTAT
ビューの統計は、最適なメモリー・サイズ、ワン・パス・メモリー・サイズおよびマルチ・パス・メモリー・サイズで実行された作業領域の総数を示します。これらの統計は、インスタンスまたはセッションが開始された後から累積されます。
次の問合せは、インスタンスの開始後にこれらのモードで作業領域が実行された回数の総数とパーセンテージを示します。
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%');
この問合せの出力例を次に示します。
PROFILE CNT PERCENTAGE ----------------------------------- ---------- ---------- workarea executions - optimal 5395 95 workarea executions - onepass 284 5 workarea executions - multipass 0 0
反復問合せのレスポンス時間を短縮するため、Oracle Call Interface(OCI)アプリケーションでは、クライアント・メモリーを通じてOCI結果キャッシュを利用できます。結果キャッシュには、すべてのセッションで共有される問合せの結果が格納されます。これらの問合せが繰り返し実行される場合、その結果はキャッシュ・メモリーから直接取得されるため、レスポンス時間が速くなります。問合せによりアクセスされているデータベース・オブジェクトのデータが変更されると、キャッシュ内に格納されている問合せ結果は無効になります。クライアント側の結果キャッシュは、サーバー側の結果キャッシュとは別の機能です。サーバーの結果キャッシュとは異なり、OCI結果キャッシュでは、結果がサーバーSGAにキャッシュされません。
関連項目: クライアントの結果キャッシュと、クライアントおよびサーバー・キャッシュの設定の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。 |
結果キャッシュ・メカニズムを制御するには、サーバー初期化ファイル(init.ora
)でRESULT_CACHE_MODE
パラメータを使用します。このパラメータは、システム・レベル、セッション・レベルおよび表レベルで次の値に設定できます。
MANUAL
MANUAL
モードでは、問合せの結果は/*+ result_cache */
ヒントの使用時にのみ結果キャッシュに格納されます。
FORCE
FORCE
モードでは、すべての結果が結果キャッシュに格納されます。
RESULT_CACHE_MODE
パラメータがMANUAL
のときに、問合せの結果を結果キャッシュに格納する場合、次の例に示すように問合せで/*+ result_cache */
ヒントを明示的に使用する必要があります。
SELECT /*+ result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
問合せを実行すると、OCIにより結果キャッシュ・メモリーが参照され、問合せの結果がすでにOCIクライアント・キャッシュ内に存在しないかどうかが検証されます。存在する場合、その結果がキャッシュから直接取得されます。存在しない場合、問合せがデータベースで実行され、出力として戻された結果がクライアントの結果キャッシュ・メモリーに格納されます。
RESULT_CACHE_MODE
パラメータがFORCE
のときに、問合せの結果を結果キャッシュに格納しない場合、次の例に示すように問合せで/*+ no_result_cache */
ヒントを使用する必要があります。
SELECT /*+ no_result_cache */ deptno, avg(sal) FROM emp GROUP BY deptno;
関連項目: 『Oracle Database SQL言語リファレンス』 |
クライアントの結果キャッシュを管理するには、サーバー初期化ファイルで次のパラメータ設定を変更します。
CLIENT_RESULT_CACHE_SIZE
結果キャッシュに割り当てられているメモリーを変更するには、このパラメータを設定します。この値を0(デフォルト値)に設定すると、結果キャッシュは無効になります。
CLIENT_RESULT_CACHE_LAG
クライアントの結果キャッシュのタイム・ラグ長を変更するには、このパラメータを設定します。OCIアプリケーションによるデータベースへのアクセス頻度が低い場合、このパラメータの値を小さくすると、クライアントの結果キャッシュとデータベースとの同期を維持する目的で、OCIクライアント・ライブラリからデータベースに対するラウンドトリップが増加します。
関連項目:
|
CLIENT_RESULT_CACHE_STATS$
ビューには、結果キャッシュの様々なキャッシュ設定とメモリー使用量の統計がリストされます。
関連項目: CLIENT_RESULT_CACHE_STATS$ ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 |