10gリリース2（10.2）
B19207-02
 目次 |
 索引 |
| Oracle Database パフォーマンス・チューニング・ガイド 10gリリース2（10.2） B19207-02 |
|
この章では、Oracleメモリー・キャッシュにメモリーを割り当てる方法とこれらのキャッシュの使用方法について説明します。Oracleメモリー・キャッシュを適切にサイズ設定して効率的に使用すると、データベースのパフォーマンスが大幅に向上します。
SGA_TARGETおよびPGA_AGGREGATE_TARGET初期化パラメータを使用するシステムには、自動メモリー構成をお薦めします。ただし、システムのメモリー・プールは手動で調整できます。そのプロセスはこの章に記載されています。
この章には次の項があります。
Oracleはメモリー・キャッシュ内およびディスク上に情報を格納します。メモリー・アクセスは、ディスク・アクセスよりはるかに高速です。ディスク・アクセス（物理I/O）は、メモリー・アクセスに比べ、時間がかかります（通常は約10ミリ秒）。また、物理I/Oでは、デバイス・ドライバやオペレーティング・システムのイベント・スケジューラのパス長のために必要なCPUリソースも増加します。このため、頻繁にアクセスされるオブジェクトに対するデータ要求は、ディスク・アクセスではなく、メモリー・アクセスで要求する方が効率的です。
パフォーマンスの目標は、必要なデータがメモリー内にある可能性を高くしたり、必要なデータを取り出すプロセスをさらに効率的にし、できるだけ多くの物理I/Oオーバーヘッドを削減することです。
自動メモリー管理の使用をお薦めします。任意のメモリー・プール・サイズを設定する前に、次の点を確認してください。
パフォーマンスに影響を与える主なOracleメモリー・キャッシュは次のとおりです。
自動共有メモリー管理は、SGAの構成を簡素化する、推奨のメモリー構成です。自動共有メモリー管理を使用するには、SGA_TARGET初期化パラメータをゼロ以外の値に設定し、STATISTICS_LEVEL初期化パラメータをTYPICALまたはALLに設定します。SGA_TARGETパラメータの値は、SGA専用にするメモリーの容量に設定する必要があります。自動SGA管理では、システム上のワークロードに応じて次のメモリー・プールにメモリーが適切に配分されます。
自動的にチューニングされるこれらのメモリー・プールをゼロ以外の値に設定すると、その値が自動共有メモリー管理における最小レベルとして使用されます。アプリケーション・コンポーネントが最小限のメモリーで正常に機能できる場合は、最小値に設定します。
SGA_TARGETは動的パラメータであり、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の初期化パラメータを使用して、各メモリー・プールのサイズを手動で変更できます。 「キャッシュ・サイズの動的な変更」を参照してください。
次のプールは手動でサイズ設定されるコンポーネントで、自動共有メモリー管理の影響は受けません。
これらのメモリー・プールを手動でサイズ設定するには、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サイズ変更操作に関する情報を提供します。
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メモリー・キャッシュを効率的に使用すると、これらのキャッシュを保護するラッチ、CPU、I/Oシステムなどの関連リソースに対する負荷も削減できます。
最高のパフォーマンスを得るために、次のことを考慮してください。
既存のアプリケーションに対する変更または追加を行う場合は、変更されたアプリケーションの要求を満たすためにOracleメモリー構造のサイズ変更が必要な場合があります。
アプリケーションがJavaを使用する場合、Javaプールのデフォルト構成を変更する必要があるかどうかを調べる必要があります。Javaのメモリー使用量の詳細は、『Oracle Database Java開発者ガイド』を参照してください。
大半のオペレーティング・システムでは、次のことを考慮することが重要です。
ページングは、新しいページをメモリーにロードできるようにするため、オペレーティング・システムがメモリー常駐ページをディスクに転送する場合に行われます。多くのオペレーティング・システムは、実メモリーに格納しきれない大量の情報を収容するために、ページングを行います。大半のオペレーティング・システムでは、ページングはパフォーマンスを低下させます。
オペレーティング・システムのユーティリティを使用して、オペレーティング・システムを調べ、システム上に多数のページングがあるかどうかを確認します。ページングが多数ある場合は、システム上の総メモリー量が、メモリーを割り当てたすべてを保持できるほど十分に大きくない場合があります。システム上の全体のメモリーを増やすか、割り当てたメモリー量を減らします。
SGAの目的は、迅速なアクセスのためにメモリー内にデータを格納することであるため、SGAは主メモリー内に存在する必要があります。SGAのページがディスクにスワップされると、データに迅速にアクセスできなくなります。多くのオペレーティング・システムでは、ページングによる損失は、大規模な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が動作するために必要な物理I/Oの量に影響を与える可能性があります。最初にメモリーを適切に構成すると、I/Oシステムが効果的に構成されているかどうかも表示されます。
プロセスをひととおり実行した後で、メモリー割当てのステップを繰り返すことが必要となる可能性もあります。実行を繰り返すことによって、後のステップの変更に基づいて前のステップの調整が可能となります。たとえば、バッファ・キャッシュのサイズを小さくすると、共有プールなど別のメモリー構造のサイズを大きくできます。
様々なタイプの操作について、Oracleではバッファ・キャッシュを使用してディスクから読み取られたブロックを格納します。ソートやパラレル読込みなどの特定操作の場合には、Oracleではバッファ・キャッシュはバイパスされます。バッファ・キャッシュを使用する操作について、この項では次の項目を説明します。
バッファ・キャッシュを効果的に使用するには、不要なリソース使用を回避するようにアプリケーションの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
このビューは、潜在的な各キャッシュ・サイズの物理読込み数を予測する情報を提供して、キャッシュのサイズ設定を支援します。このデータには物理読込みファクタが含まれています。これは、バッファ・キャッシュが所定の値にサイズ変更された場合、現行の物理読込み回数がその分のみ変化すると予測されるファクタです。
キャッシュ内でのブロックの検出成功とキャッシュのサイズ間の関係は、必ずしも滑らかな分布を示しません。バッファ・プールをサイズ設定するときは、キャッシュ・ヒット率の向上にまったく貢献しない（または、ほとんど貢献しない）追加バッファは使用しないでください。図7-1の例では、キャッシュ・サイズの増分の狭い帯状部分のみが考慮に値することを示しています。
図7-1を調べると、次のことがわかります。
バッファ・キャッシュ・ヒット率では、ディスク・アクセスを行わずにバッファ・キャッシュ内で要求されたブロックが検出された頻度を計算します。この率は、動的なパフォーマンス・ビューV$SYSSTATから選択したデータを使用して計算されます。バッファ・キャッシュ・ヒット率を使用して、V$DB_CACHE_ADVICEで予測されたように物理I/Oを検証できます。
表7-1の統計は、ヒット率の計算に使用されます。
| 統計 | 説明 |
|---|---|
|
consistent gets from cache |
バッファ・キャッシュからのブロックに対して読取り一貫性が要求された回数。 |
|
db block gets from cache |
バッファ・キャッシュからのCURRENTブロックが要求された回数。 |
|
physical reads cache |
ディスクからバッファ・キャッシュへ読み込まれたデータ・ブロックの合計数。 |
例7-1はV$SYSSTAT表から直接選択した値を使用して単純化したもので、ある期間の値を選択したものではありません。アプリケーションの実行中のある期間にわたるこれらの統計の差分を計算し、それらの統計を使用してヒット率を判断することが最良の方法です。
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$DB_CACHE_ADVICEデータおよびバッファ・キャッシュ・ヒット率を調べる必要があります。
低いキャッシュ・ヒット率は、キャッシュのサイズを大きくすることがパフォーマンスに有益であることを意味しません。キャッシュ・ヒット率の高いことが、ワークロードに対してキャッシュが適切にサイズ設定されていることを示しているとはかぎりません。
バッファ・キャッシュ・ヒット率を解釈する場合は、次の点を考慮する必要があります。
一般規則として、キャッシュ・ヒット率が低く、全表スキャンを実行しないようにアプリケーションがチューニングされている場合は、キャッシュのサイズを増やすことを検討してください。
キャッシュ・サイズを増やすには、まずDB_CACHE_ADVICE初期化パラメータをONに設定し、キャッシュ統計を安定させます。V$DB_CACHE_ADVICEビュー内のアドバイザ・データを調べて、実行する物理I/Oの量を大幅に減少させるために必要な次の増分を決定します。ホスト・オペレーティング・システムにページングさせずに必要な余分なメモリーをバッファ・キャッシュに割り当てることができる場合は、このメモリーを割り当てます。バッファ・キャッシュに割り当てられたメモリーの量を増やすには、DB_CACHE_SIZE初期化パラメータの値を増やします。
必要であれば、インスタンスをシャットダウンせずに、バッファ・プールを動的にサイズ変更してこの変更を行います。
DB_CACHE_SIZEパラメータは、データベースの標準ブロック・サイズのデフォルト・キャッシュのサイズを指定します。データベースの標準ブロック・サイズとは異なるブロック・サイズを持つ表領域を作成して使用するには（トランスポータブル表領域をサポートする場合など）、使用するブロック・サイズごとに個別のキャッシュを構成する必要があります。DB_nK_CACHE_SIZEパラメータを使用して、必要な標準以外のブロック・サイズを構成できます（n は2、4、8、16または32のいずれかで、n は標準ブロック・サイズではありません）。
キャッシュ・ヒット率が高い場合、キャッシュが十分大きく、最も頻繁にアクセスされるデータも保持できる状態になっています。V$DB_CACHE_ADVICEデータをチェックして、キャッシュ・サイズを削減すると物理I/O数が大幅に増えるかどうかを調べます。物理I/Oへの影響がなければ、別のメモリー構造にメモリーを必要とする場合に、キャッシュ・サイズを削減しても、良好なパフォーマンスを維持できます。バッファ・キャッシュを小さくするには、DB_CACHE_SIZEパラメータの値を変更してキャッシュのサイズを削減します。
一般に、ほとんどのシステムでは1つのデフォルト・バッファ・プールが適切です。ただし、アプリケーションのバッファ・プールについて詳しい知識を持つユーザーは、複数バッファ・プールを構成すると有益な場合があります。
非定型アクセス・パターンを持つセグメントの場合、それらのセグメントからのブロックを2つの異なるバッファ・プールであるKEEPプールとRECYCLEプールに格納します。セグメントのアクセス・パターンは、常にアクセスされるか（すなわち、ホット）、またはほとんどアクセスされない（たとえば、1日に1回のみバッチ・ジョブでアクセスされる大きなセグメント）というように、非定型である可能性があります。
複数バッファ・プールによって、これらの違いに対処できます。KEEPバッファ・プールを使用してバッファ・キャッシュ内の頻繁にアクセスされるセグメントをメンテナンスし、RECYCLEバッファ・プールを使用してオブジェクトがキャッシュ内の領域を不必要に占有するのを防ぐことができます。オブジェクトがキャッシュに関連付けられると、そのオブジェクトのすべてのブロックがそのキャッシュに置かれます。特定のバッファ・プールに割り当てられていないオブジェクトのために、DEFAULTバッファ・プールがメンテナンスされています。デフォルト・バッファ・プールのサイズは、DB_CACHE_SIZEです。各バッファ・プールは、同じLRU置換方針を使用します（たとえば、KEEPプールがそのプールに割り当てられたすべてのセグメントを格納するほど十分大きくない場合、最も古いブロックがキャッシュから除去されます）。
オブジェクトを適切なバッファ・プールに割り当てると、次の操作を実行できます。
非常に大きいセグメントに大きい索引レンジ・スキャンまたは非有界索引レンジ・スキャンでアクセスすると、LRU除外方法では問題が発生する可能性があります。大規模とは、キャッシュのサイズと比較して大きいという意味です。非順次物理読込みのかなりの割合（10%を超える割合）を1つのセグメントが占有する場合、そのセグメントは大規模であると考えられます。大規模セグメントに対するランダム読込みは、他のセグメントのデータを含むバッファがキャッシュから除去される原因となります。大規模セグメントは、キャッシュの大きな割合を消費しますが、キャッシングによる利益はありません。
非常に頻繁にアクセスされるセグメントは、バッファが頻繁にアクセスされるのでキャッシュから除去されないため、大規模セグメントの読込みの影響を受けません。ただし、その問題は、大規模セグメントの読込みによるバッファの除外を免れるほど頻繁にはアクセスされないウォーム・セグメントに影響を与えます。この問題を解決するオプションは、次の3つです。
RECYCLEキャッシュに移動できるので、その他のセグメントに影響を与えません。RECYCLEキャッシュはDEFAULTバッファ・プールよりも小さくし、DEFAULTバッファ・プールよりも迅速にバッファを再利用する必要があります。
KEEPキャッシュに小さなウォーム・セグメントを移動する方法もあります。KEEPキャッシュをサイズ設定して、キャッシュでのミスを最小におさえられます。特定の問合せによってアクセスされるセグメントをKEEPキャッシュに置き、除去されないようにすることで、その問合せの応答時間をより予測可能にできます。
データベース・インスタンスごとに複数バッファ・プールを作成できます。データベースの各インスタンスについて、必ずしも同じバッファ・プール・セットを定義する必要はありません。インスタンスごとにバッファ・プールのサイズを変えることも、バッファを定義しないこともできます。それぞれのアプリケーション要件に従って、各インスタンスをチューニングします。
オブジェクトのデフォルト・バッファ・プールを定義するには、STORAGE句のBUFFER_POOLキーワードを使用します。この句は、CREATE TABLEおよびALTER TABLE、CLUSTER、およびINDEXの各SQL文に有効です。バッファ・プールを指定すると、そのオブジェクトに対して読み込まれたブロックは、すべてそのプールに配置されます。
バッファ・プールがパーティション表または索引に対して定義されている場合、オブジェクトの各パーティションは、特定のバッファ・プールで上書きされないかぎり、表または索引定義からバッファ・プールを継承します。
オブジェクトのバッファ・プールがALTER文を使用して変更された場合、変更されたセグメントのブロックを現在格納しているすべてのバッファは、ALTER文を発行する前にあったバッファ・プールに残ります。新たにロードされたブロック、および除去されて再ロードされたブロックは、新しいバッファ・プールに入ります。
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$BHビューは、SGA内に現在常駐するすべてのブロックのデータ・オブジェクトIDを示します。プール内にバッファを多く持つセグメントを判断するには、この項で説明する2つの方法のいずれかを使用します。すべてのセグメントのバッファ・キャッシュ使用パターンを確認する（方法1）か、特定のセグメントの使用パターンを調べます（方法2）。
次の問合せでは、ある時点でバッファ・キャッシュ内に常駐するすべてのセグメントのブロック数をカウントします。バッファ・キャッシュ・サイズによっては、このカウントに多数のソート領域を必要とする可能性があります。
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
次の手順に従って、ある時点で個々のオブジェクトによって使用されるキャッシュの割合を決定してください。
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;
% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]
アプリケーションに頻繁に参照されるセグメントがある場合は、KEEPバッファ・プールと呼ばれる個別のキャッシュにそれらのセグメントのブロックをキャッシュします。メモリーは、DB_KEEP_CACHE_SIZEパラメータを必要なサイズに設定することでKEEPバッファ・プールに割り当てられます。KEEPプールのメモリーは、デフォルト・プールのサブセットではありません。保持できる一般的なセグメントは、頻繁に使用される小さい参照表です。アプリケーション開発者とDBAは、どの表が候補かを判断できます。
「プール内に多くのバッファを持つセグメントの判断」で説明するように、V$BHを問い合せて、候補表からブロック数をチェックできます。
KEEPバッファ・プールの目的は、メモリー内にオブジェクトを保存して、I/O操作を避けることにあります。したがって、KEEPバッファ・プールのサイズは、バッファ・キャッシュに保持するオブジェクトによって異なります。KEEPバッファ・プールのおおよそのサイズは、このプールに割り当てられるすべてのオブジェクトで使用されるブロックを加算することで計算できます。セグメントに関する情報を収集する場合、DBA_TABLES.BLOCKSとDBA_TABLES.EMPTY_BLOCKSを問い合せて使用されるブロック数を判断できます。
ヒット率を計算するには、前述の問合せを使用してシステム・パフォーマンスの2つのスナップショットを時間をおいて取ります。物理読込み（physical reads）、ブロック取得（block gets）および一貫取得（consistent gets）について、古い値から新しい値を引いて、これらの結果を使用してヒット率を計算します。
100%のバッファ・プール・ヒット率が最適とはかぎりません。KEEPバッファ・プールのサイズを減らしても、十分に高いヒット率が維持されることがよくあります。KEEPバッファ・プールから除去されたブロックは、別のバッファ・プールに割り当ててください。
各オブジェクトをメモリー内に保持するとトレードオフが発生します。頻繁にアクセスされるブロックをキャッシュに保持することは有効ですが、頻繁に使用しないブロックを保持すると、よりアクティブな他のブロックのためのスペースが減ることになります。
メモリーに残さないセグメントに属するブロック用のRECYCLEバッファ・プールを構成できます。RECYCLEプールは、ほとんどスキャンされないか頻繁に参照されないセグメントに適しています。アプリケーションがラージ・オブジェクトのブロックをランダム方式でアクセスする場合は、バッファ・プールに格納されているブロックが除去される前に再利用できる可能性は（使用可能物理メモリーの量の制約により）ほとんどありません。これはバッファ・プールのサイズに関係なくあてはまります。したがって、そのオブジェクトのブロックはキャッシュしないでください。これらのキャッシュ・バッファは、他のオブジェクトに割り当てることができます。
メモリーは、DB_RECYCLE_CACHE_SIZEパラメータを必要なサイズに設定することでRECYCLEバッファ・プールに割り当てられます。このRECYCLEバッファ・プールのメモリーは、デフォルト・プールのサブセットではありません。
あまり早くメモリーからブロックを破棄しないでください。バッファ・プールが小さすぎると、トランザクションまたはSQL文が実行を完了する前に、ブロックがキャッシュから除外されてしまう可能性があります。たとえば、アプリケーションが表から値を選択し、その値を使用してデータを処理し、レコードを更新する場合があります。SELECT文の後でブロックがキャッシュから削除された場合は、更新を実行するために再度ディスクから読み込む必要があります。ブロックは、ユーザー・トランザクションの所要時間中は保存される必要があります。
異なるタイプのデータをキャッシュするには、共有プールを使用します。キャッシュされたデータには、PL/SQLブロックおよびSQL文のテキストおよび実行可能フォーム、ディクショナリ・キャッシュ・データおよびその他のデータが含まれています。
共有プールを適切な大きさにして使用すると、次の4つの方法でリソース使用量を低減できます。
この項では、次の項目について説明します。
共有プールの主なコンポーネントは、ライブラリ・キャッシュとディクショナリ・キャッシュです。ライブラリ・キャッシュは、最近参照された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ブロックが共有プールに現在存在する別の文と同じかどうかを自動的に判断します。
比較のために、次のステップが実行されます。
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を使用する場合のコストと効果については、この項の後半で説明します。
SQL文やPL/SQLブロック内でスキーマ・オブジェクトを参照する際には、同じスキーマ内の同じオブジェクトである必要があります。たとえば、2人のユーザーが次のSQL文を発行するとします。
SELECT * FROM employees;
各ユーザーに独自のemployees表がある場合、文はユーザーごとに異なる表を参照するので、この文は同一とみなされません。
たとえば、次の文で同じ共有SQL領域を使用できないのは、バインド変数名が異なるためです。
SELECT * FROM employees WHERE department_id = :department_id; SELECT * FROM employees WHERE department_id = :dept_id;
多くのOracle製品（Oracle Formsやプリコンパイラなど）は、文をデータベースに渡す前にSQLを変換します。首尾一貫したSQL文の集合が生成されるように、文字は大文字に統一して変換され、空白は圧縮され、バインド変数は改名されます。
共有プールの重要な目的は、SQL文とPL/SQL文の実行可能バージョンをキャッシュすることです。これにより、ハード解析にリソースを使用することなく、同じSQLまたはPL/SQLコードを複数回実行できるので、CPU、メモリーおよびラッチの使用が大幅に減少します。
また、共有プールは、データ・ウェアハウス・アプリケーションで非共有SQLをサポートできます。これらのアプリケーションでは、同時実行性が低くリソース使用率の高いSQL文が実行されます。このような状況では、リテラル値を持つ非共有SQLを使用することをお薦めします。バインド変数ではなくリテラル値を使用すると、オプティマイザは優れた列選択性の見積りを行えるので、最適なデータ・アクセス・プランを提供します。
OLTPシステムでは、共有プールと関連リソースを効率的に使用できるようにする方法が多数あります。次の項目についてアプリケーション開発者と検討し、共有プールが効果的に使用されるようにする方法を決定します。
同時実行性の高いOLTPシステムで共有プールを効率よく使用すると、解析関連アプリケーションの拡張性の問題が発生する確率が大幅に低減します。
同じアプリケーションを実行する複数のユーザーのために共有SQLを再利用すると、ハード解析が回避されます。ソフト解析は、共有プール・ラッチやライブラリ・キャッシュ・ラッチなどのリソース使用量を大幅に減少させます。カーソルを共有するには、次のことを行います。
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;
|
注意:
バインド変数を使用するためにコードをリライトすることが実際的ではない既存のアプリケーションについては、 |
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キーワードを使用して、各順序のキャッシュ済エントリ数を構成できます。
使用しているOracleアプリケーション・ツールにより異なりますが、アプリケーションが解析コールを実行する頻度を制御できます。
アプリケーションが、カーソルをクローズする、または新しいSQL文に既存のカーソルを再利用する頻度は、セッションで使用されるメモリー量と、時には、そのセッションで実行される解析の量にも影響を与えます。
（異なるSQL文の）カーソルをクローズまたは再利用するアプリケーションは、カーソルをオープンした状態を保つアプリケーションほどセッション・メモリーを必要としません。逆に、そのようなアプリケーションでは、解析コールをより多く実行し、そのための追加のCPUおよびOracleリソースを使用する可能性があります。
頻繁に実行されないSQL文に関連するカーソルをクローズしたり、他の文に再利用できるのは、その文を再実行（および再解析）する可能性が低いからです。
再実行されるSQL文を含むカーソルがクローズまたは別の文に再利用される場合は、追加の解析コールが必要になります。カーソルがオープンされた状態であれば、解析コールを発行するためのオーバーヘッドを発生させずに、カーソルを再利用できます。
カーソルの管理を行う方法は、アプリケーション開発ツールにより異なります。次の項では、いくつかのツールで使用される方法を紹介します。
Oracle Call Interface（OCI）を使用する場合、再実行するカーソルはクローズおよび再オープンしないでください。そのかわりに、カーソルをオープンしたままにし、実行前にリテラル値をバインド変数に変更してください。
既存のSQL文が今後再実行される場合は、新しいSQL文に文ハンドルを再利用しないようにしてください。
Oracleプリコンパイラを使用する場合、プリコンパイラ句を設定して、いつカーソルをクローズするかを制御できます。Oracleモードでは、プリコンパイラ句は次のとおりです。
ANSIモードでは、HOLD_CURSORとRELEASE_CURSORの値が切り替えられますが、これはお薦めしません。
プリコンパイラ句は、プリコンパイラ・コマンドライン上またはプリコンパイラ・プログラム内で指定できます。これらの句により、様々な方法で、プログラムの実行中にカーソルを管理できます。
文を用意し、バインド変数に新しい値を使用して文を再実行します。カーソルは、セッション中はオープンのままです。
新しいリテラル値は、再実行のためにカーソルにバインドできるので、カーソルを再実行する場合は、カーソルをクローズしないでください。別の方法として、JDBCはsetStmtCacheSize()メソッドを使用してJDBCクライアント内にSQL文キャッシュを提供しています。このメソッドを使用して、JDBCはJDBCプログラムに対してローカルなSQL文キャッシュを作成します。
Oracle Formsで、カーソル管理の一部を管理できます。トリガー・レベル、フォーム・レベルまたは実行時のいずれかで、この管理を実施できます。
新規にインスタンスを構成する場合、作成する共有プール・キャッシュの適切なサイズを知ることはできません。通常、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ブロックのライブラリ・キャッシュのアクティビティを反映します。
他のNAMESPACE値を持つ行は、Oracleが依存関係のメンテナンスのために使用するオブジェクト定義に対するライブラリ・キャッシュのアクティビティを反映します。
各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回の実行がありました。
RELOAD）する必要があります。
共有プールの空きメモリーの容量は、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プールの現在のサイズによって異なります。
共有プールがライブラリ・キャッシュに対して適切にサイズ設定されている場合、その設定はディクショナリ・キャッシュ・データに対しても適切であるのが普通です。
データ・ディクショナリ・キャッシュ・ミスは、いくつかの場合に予想されます。インスタンス起動時は、データ・ディクショナリ・キャッシュにデータは含まれていません。したがって、発行されたSQL文からキャッシュ・ミスが発生する可能性があります。キャッシュに読み込まれるデータが増えると、キャッシュ・ミスの可能性は減少します。最終的に、データベースは、最も頻繁に使用されるディクショナリ・データがキャッシュ内に存在する安定状態に到達します。この時点で、キャッシュ・ミスはほとんど発生しません。
V$ROWCACHEビューの各行は、データ・ディクショナリ項目について単一のタイプの統計を収録します。これらの統計は、直前のインスタンス起動以降のデータ・ディクショナリ・アクティビティを反映しています。データ・ディクショナリ・キャッシュの使用と有効性を反映するV$ROWCACHEビューの中の列を表7-2にリストします。
次の問合せによって、アプリケーションの実行中、ある期間にわたって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;
共有プール統計は実行可能な調整方法を示します。この項ではそのうちのいくつかについて説明します。
共有プールのメモリー量を増やすと、ライブラリ・キャッシュとディクショナリ・キャッシュの両方で使用できるメモリー量が増えます。
共有SQL領域がそれらのSQL文を解析した後にキャッシュ内に残るようにするには、V$LIBRARYCACHE.RELOADS値が0（ゼロ）に近くなるまでライブラリ・キャッシュに利用できるメモリー量を増やします。ライブラリ・キャッシュに利用できるメモリーを増やすには、SHARED_POOL_SIZE初期化パラメータの値を増やしてください。このパラメータの最大値はオペレーティング・システムによって異なります。この処置によって、実行のためのSQL文とPL/SQLブロックの暗黙的な再解析が減少します。
共有SQL領域に利用可能な追加のメモリーを利用するために、セッションに対して許可されるカーソル数を増やすこともあります。その場合は、初期化パラメータOPEN_CURSORSの値を増やします。
GETSとGETMISSES列を監視することによって、キャッシュ・アクティビティを調べてください。ディクショナリ・キャッシュが頻繁にアクセスされる場合、GETSの合計に対するGETMISSESの割合は、アプリケーションによって異なりますが、10%あるいは15%より低くしてください。
次のすべてに当てはまる場合は、キャッシュに利用できるメモリー量を増やすことを考慮してください。
初期化パラメータSHARED_POOL_SIZEの値を増やして、データ・ディクショナリ・キャッシュに利用できるメモリーを増やします。
RELOADSが0（ゼロ）に近く、共有プール内の空きメモリーが少ない場合、共有プールは、最も頻繁にアクセスされるデータを保持できる十分な大きさがあります。
常に共有プールに多数の空きメモリーがある場合、このメモリーを他の場所に割り当てるために共有プールのサイズを小さくしても、良好なパフォーマンスを維持できます。
共有プールを小さくするには、SHARED_POOL_SIZEパラメータの値を変更してキャッシュのサイズを小さくします。
共有プールとは異なり、ラージ・プールにはLRUリストがありません。Oracleは、ラージ・プールからオブジェクトを除去しようとしません。
インスタンスが次のいずれかを使用する場合は、ラージ・プールの構成を考慮してください。
パラレル問合せでは、共有プール・メモリーを使用してパラレル実行メッセージ・バッファをキャッシュします。
Recovery Managerは、共有プールを使用してバックアップおよびリストア操作時にI/Oバッファをキャッシュします。I/Oサーバー・プロセスと、バックアップおよびリストア操作では、Oracleは数百KB単位でバッファを割り当てます。
共有サーバー・アーキテクチャでは、各クライアント・プロセスのセッション・メモリーが共有プールに含まれています。
Oracleでは共有サーバー・セッション・メモリーに共有プールからメモリーを割り当てるため、ライブラリ・キャッシュとディクショナリ・キャッシュに使用可能な共有プール・メモリーの量が減少します。別のプールからこのセッション・メモリーを割り当てると、Oracleは、主に共有SQLのキャッシングのために共有プールを使用できるので、共有SQLキャッシュの減少によるパフォーマンス・オーバーヘッドは発生しません。
共有サーバー関連のユーザー・グローバル領域（UGA）の割当てには、共有プールではなくラージ・プールの使用をお薦めします。共有プールは、Oracleによって、共有SQLやPL/SQLプロシージャなど、他の目的のシステム・グローバル領域（SGA）メモリー用に割り当てられるためです。共有プールのかわりにラージ・プールを使用すると、共有プールの断片化も減少します。
ラージ・プールに共有サーバー関連のUGAを格納するには、初期化パラメータLARGE_POOL_SIZEに値を指定します。どのプール（共有プールまたはラージ・プール）にオブジェクト用のメモリーが存在するかを確認するには、V$SGASTATで列POOLをチェックします。ラージ・プールはデフォルトで構成されません。最小値は300KBです。ラージ・プールを構成しないと、共有サーバー・ユーザー・セッション・メモリーに共有プールが使用されます。
ラージ・プールの大きさは、同時にアクティブとなるセッションの数を基準に構成します。各アプリケーションは、必要なセッション情報メモリー量がそれぞれ異なり、ラージ・プールあるいはSGAの構成はメモリー要件を反映する必要があります。たとえば、アクティブな各セッションのセッション情報を格納するために共有サーバーが200〜300KBを必要とすると仮定します。100個のセッションが同時にアクティブになると予想される場合、30MBのラージ・プールを構成するか、ラージ・プールを構成しない場合は、共有プールを増やしてください。
Oracleが使用するUGAの厳密な容量は、各アプリケーションによって異なります。ラージ・プールまたは共有プールの効果的な設定を判別するには、一般的なユーザーでのUGAの使用状況を観察して、その容量をユーザー・セッションの見積り数に乗算します。
共有サーバーの使用により共有メモリーの使用が増加するとしても、合計のメモリー使用量は減少します。これは、プロセス数が減少するので、専用サーバー環境と比較した場合に共有サーバーではPGAメモリーの使用量が減るためです。
Oracleでは、セッションに使用された全メモリーの統計が収集され、動的パフォーマンス・ビューV$SESSTATに格納されます。表7-3はこれらの統計をリストしたものです。
| 統計 | 説明 |
|---|---|
|
|
この統計の値は、セッションに割り当てられたメモリー容量です（単位はバイト）。 |
|
|
この統計の値は、セッションに割り当てたメモリー容量の最大値です（単位はバイト）。 |
値を検索するには、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消費量の制限は行わないため、このパラメータを使用することはほとんどありません。
接続ユーザーが非常に多数の場合は、3層の接続を実装することでメモリー使用を低減できます。これはトランザクション処理（TP）モニター使用の副産物であり、ロックやコミットされていないDMLを複数のコールにわたって保持できないため、純粋なトランザクション・モデルでしか実現できません。共有サーバー環境には次の利点があります。
ライブラリ・キャッシュ・ミスがない場合も、初期化パラメータ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は次の順序で共有プールへの領域の割当てを試行します。
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の値も同じだけ増やします。
REQUEST_FAILURESの値がゼロよりも大きく、増加している場合は、予約プールが小さすぎます。SHARED_POOL_RESERVED_SIZEとSHARED_POOL_SIZEの値をそれぞれ増やすと、これを解決できます。これらのパラメータで選択する設定は、システムのSGAサイズの制約によって異なります。
SHARED_POOL_RESERVED_SIZEの値を増やすと、予約リストで利用可能なメモリーの容量が増えます。予約リストからメモリーを割り当てないユーザーには影響がありません。
予約リストに割り当てられているメモリーが多すぎることがあります。次の場合です。
これらの条件のどちらかが真の場合、SHARED_POOL_RESERVED_SIZEの値を減らします。
V$SHARED_POOL_RESERVED固定ビューを使用すると、SHARED_POOL_SIZEの値が小さすぎる場合を示すこともできます。これはREQUEST_FAILURESがゼロより大きいかあるいは増加しているような場合です。
予約リストを使用可能にしている場合は、SHARED_POOL_RESERVED_SIZEの値を減らします。予約リストを使用可能にしていない場合は、SHARED_POOL_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をコールしてオブジェクトを確保します。この手順により、保存されているオブジェクトがロードされる前にシステムの共有メモリーが使用し尽くされないことが保証されます。その結果、オブジェクトをインスタンスの早い時期に確保することにより、大きなメモリー領域を共有プールの中央に確保するために発生する可能性のある、メモリーの断片化を防ぐことができます。
解析の第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パラメータに依存するのではなく、共有可能なSQLを書くことです。これは、CURSOR_SHARINGによってハード解析がなくなる分、使用されるリソース量は大幅に削減されますが、共有プール内で類似文を検索するために、ソフト解析の一部としてある程度の追加作業が必要になるからです。
次の質問の両方に該当する場合は、CURSOR_SHARINGをSIMILARまたはFORCEに設定することを考慮してください。
CURSOR_SHARING = SIMILAR（またはFORCE）を使用すると、多数の類似文を持ついくつかのアプリケーション上でのカーソルの共有を大幅に向上できるため、メモリー使用量が削減され、解析が高速になり、ラッチ競合が減少します。
中間層を持つ大きなOLTPアプリケーションでは、データベース要求ごとに接続と切断を行うのではなく、接続を維持するようにします。永続的な接続を維持することで、ラッチなどのCPUリソースとデータベース・リソースが節約されます。
バッファ・キャッシュ内のデータ・ブロックに変更を行うサーバー・プロセスでは、REDOデータをログ・バッファに生成します。LGWRは、次のいずれかに当てはまる場合に、REDOログ・バッファからオンラインREDOログにエントリをコピーする書込みを開始します。
LGWRがREDOログ・ファイルにREDOログ・バッファからREDOエントリを書き込むとき、ユーザー・プロセスはディスクに書き込まれたメモリー内のエントリ上に新しいエントリをコピーできます。REDOログへのアクセスが激しいときでも、通常LGWRは高速に書込みを行い、新しいエントリのバッファ内の領域が利用できることを保証します。
大きいバッファにより、新しいエントリのための領域がある可能性が高くなります。また、LGWRに、REDOレコードを効率よく書き出す機会を与えます（大規模な更新を行うシステム上の小さすぎるログ・バッファは、LGWRがREDOを継続的にディスクにフラッシュすることになり、ログ・バッファは2/3が空白のままになります）。
高速のプロセッサと比較的低速のディスクを持つシステムでは、REDOログ・ライターによってバッファの一部がディスクに移動される時間に、プロセッサがバッファの残りにデータを挿入していることがあります。この状況では、大きいログ・バッファは低速のディスクの影響を一時的に隠すことがあります。次のような方法も選択できます。
REDOログ・バッファの有効利用の簡単な例を示します。
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メモリー領域内に、そのカーソルのための新しいランタイム領域が作成されます。
複雑な問合せ（たとえば、意思決定支援の問合せ）の場合、ランタイム領域の大部分が、次のようなメモリー集約型演算子で割り当てられた作業領域に使用されます。
ソート演算子は、作業領域（ソート領域）を使用して一連の行のメモリー内ソートを実行します。同様に、ハッシュ結合演算子は作業領域（ハッシュ領域）を使用して、ハッシュ表を左側から入力して作成します。
作業領域のサイズは、制御およびチューニングできます。一般に、作業領域を大きくすると、メモリー消費量は増えますが特定の演算子のパフォーマンスを大幅に向上できます。作業領域のサイズは、関連するSQL演算子で割り当てられた入力データや補助メモリー構造を十分収容できるほど大きなサイズが理想的です。これが作業領域の最適サイズとされます。作業領域のサイズが最適なサイズより小さい場合は、入力データの部分に対して追加のパスが実行されるので、応答時間は増えます。これは、作業領域のワン・パス・サイズと呼ばれます。ワン・パスしきい値以下の場合は、作業領域のサイズが入力データ・サイズに比べて小さすぎる場合に入力データに対する複数のパスが必要です。このため、演算子の応答時間が大幅に増加する可能性があります。これは、作業領域のマルチ・パス・サイズと呼ばれます。たとえば、10GBのデータをソートするシリアル・ソート操作では、最適なサイズで実行するには10GBよりも少し多めが必要で、ワン・パスで実行するには少なくとも40MBが必要です。このソートが40MBより少ない取得を行う場合は、入力データに対して複数のパスを実行する必要があります。
目標は、最適なサイズ（たとえば、90%を超える、またはOLTPシステム固有の場合は100%）で大半の作業領域を動作させ、その一部をワン・パス・サイズ（たとえば、10%未満）で動作させることです。マルチ・パスの実行は避けてください。大きなソートとハッシュ結合を実行するDSSシステムの場合であっても、ワン・パスの実行のメモリー要件は相対的に少ない量です。妥当なPGAメモリー量で構成されたシステムは、入力データに対してマルチ・パスを実行する必要がありません。
自動PGAメモリー管理により、PGAメモリーの割当て方法が単純化され改善されます。デフォルトでは、PGAメモリー管理は有効化されます。このモードでは、SGAメモリー・サイズの20%をベースとして、作業領域専用のPGAメモリーのサイズが動的に調整されます。最小値は10MBです。
自動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システムには小さすぎる場合があります。
PGA_AGGREGATE_TARGETをチューニングする。これについては、次の項で詳しく説明します。
Oracleインスタンスに使用できる総メモリー量に基づいて、PGA_AGGREGATE_TARGET初期化パラメータの値（たとえば、100000KB、2500MB、50GBなど）を設定する必要があります。この値は後からインスタンス・レベルでチューニングしたり動的に変更できます。例7-2に一般的な状況を示します。
Oracleインスタンスが4 GBの物理メモリーを持つシステム上で動作するように設定されていると仮定します。そのメモリーの一部は、オペレーティング・システムと同じハードウェア・システムで動作しているその他のOracle以外のアプリケーションに残しておく必要があります。たとえば、使用可能なメモリーの80%（3.2 GB）のみをOracleインスタンス専用にできます。
次に、残りのメモリーをSGAとPGAに分割する必要があります。
PGA_AGGREGATE_TARGETパラメータの適切な初期値の例を次に示します。
: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%total_mem はシステムで使用可能な物理メモリーの総量です。
この例では、4GBのtotal_mem の値を使用することにより、PGA_AGGREGATE_TARGETをDSSシステムの場合は1600MBに、OLTPシステムの場合は655MBに初期設定できます。
チューニング・プロセスを開始する前に、Oracleで収集される主要統計の監視および解釈方法を理解して、自動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によって計算され、PGAメモリー・コンポーネントのパフォーマンスを反映します。この値はインスタンスの起動時から累積されます。値が100%の場合は、インスタンス起動後にシステムが実行したすべての作業領域で、最適な量のPGAメモリーが使用されたことを意味します。それが理想的ですが、純粋なOLTPシステムなどの場合を除き、そのようになることはほとんどありません。実際には、PGAメモリーの総サイズによって、ワン・パスやマルチ・パスを実行する作業領域も発生します。作業領域が最適に実行できない場合は、1つ以上の余分なパスが入力データで実行されています。その場合、入力データのサイズと実行された余分なパス数に比例してcache hit percentageが低下します。例7-3に、余分なパスによってcache hit percentageが受ける影響を示します。
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@dlsun1690 (PMON) 314540 685860 0 685860
oracle@dlsun1690 (MMAN) 313992 685860 0 685860
oracle@dlsun1690 (DBW0) 696720 1063112 0 1063112
oracle@dlsun1690 (LGWR) 10835108 22967940 0 22967940
oracle@dlsun1690 (CKPT) 352716 710376 0 710376
oracle@dlsun1690 (SMON) 541508 948004 0 1603364
oracle@dlsun1690 (RECO) 323688 685860 0 816932
oracle@dlsun1690 (q001) 233508 585128 0 585128
oracle@dlsun1690 (QMNC) 314332 685860 0 685860
oracle@dlsun1690 (MMON) 885756 1996548 393216 1996548
oracle@dlsun1690 (MMNL) 315068 685860 0 685860
oracle@dlsun1690 (q000) 330872 716200 65536 716200
oracle@dlsun1690 (TNS V1-V3) 635768 928024 0 1255704
oracle@dlsun1690 (CJQ0) 533476 1013540 0 1144612
oracle@dlsun1690 (TNS V1-V3) 430648 812108 0 812108
このビューは、各Oracleプロセスの名前を付けられたコンポーネント・カテゴリごとに、動的PGAのメモリー使用量を表示します。このビューには、各Oracleプロセスに対して1行ずつ、6行まで（次のそれぞれに対して各1行）が含まれます。
列CATEGORY、ALLOCATED、USEDおよびMAX_ALLOCATEDを使用して、6つのカテゴリそれぞれのOracleプロセスのPGAメモリー使用量を動的にモニターできます。
このビューには、インスタンス起動後に、最適なメモリー・サイズ、ワン・パス・メモリー・サイズおよびマルチ・パス・メモリー・サイズで実行された作業領域の総数を示します。このビューの統計は、作業領域の最適なメモリー要件によって定義されるバケットに副分割されます。各バケットは、列LOW_OPTIMAL_SIZEおよびHIGH_OPTIMAL_SIZEの値で指定された最適メモリー要件の範囲によって識別されます。
例7-3および例7-4で、V$SQL_WORKAREA_HISTOGRAMの2種類の使用方法を示します。
最適に実行する（キャッシュされる）には3MBのメモリーを必要とするソート操作の事例を示します。このソートで使用される作業領域に関する統計は、LOW_OPTIMAL_SIZE = 2097152（2 MB）およびHIGH_OPTIMAL_SIZE = 4194303（4 MB - 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未満のすべての作業領域が最適モードで実行できたことも示されています。
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%）はワン・パスで実行されました。マルチ・パスで実行されたものはありませんでした。これは望ましい状態ですが、その理由は次のとおりです。
このビューを使用すると、インスタンスでアクティブな（または実行中の）作業領域を表示できます。小さいアクティブなソート（64KB以下）はビューから除外されます。すべてのアクティブな作業領域のサイズを正確に監視したり、それらの作業領域が一時セグメントに流用されているかどうかを判断するには、このビューを使用します。例7-6に、このビューの代表的な問合せを示します。
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つ示します。
次の問合せでは、最もキャッシュ・メモリーを必要とする上位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のチューニングを容易にするため、次の2種類のPGAアドバイス・パフォーマンス・ビューが提供されています。
これらのビューを調べれば、経験的な方法で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の値を変更した場合に、V$PGASTATの統計cache hit percentageおよびover allocation countがどのような影響を受けるかを予測します。例7-8に、このビューの代表的な問合せを示します。
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に設定する必要があります。
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に設定する必要があります。
cache hit percentageを極力最大化します。例7-8では、PGAに割当て可能なメモリーに制限Xがあると仮定しています。
PGA_AGGREGATE_TARGETを最適値に設定します。この時点では、PGA_AGGREGATE_TARGETへのメモリー割当ての増加に伴う有益性はごくわずかです。例7-8で10GBをPGA専用とする場合は、PGA_AGGREGATE_TARGETを最適値である3GBに設定します。残りの7GBはSGA専用となります。
PGA_AGGREGATE_TARGETをXに設定します。例7-8で2GBのみをPGA専用とする場合は、PGA_AGGREGATE_TARGETを2GBに設定し、cache hit percentage 75%を受け入れます。
さらに、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ビューに対して実行される任意の問合せがこのビューで使用できます。
次の問合せでは、初期化パラメータ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未満のすべての作業領域を最適モードで実行できることを示しています。
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
OLAP_PAGE_POOL_SIZE初期化パラメータは、OLAPセッションに割り当てられているページング・キャッシュの最大サイズをバイト単位で指定します。
パフォーマンス上の理由のため、通常は、小さいOLAPページング・キャッシュを構成し、DB_CACHE_SIZEを使用して大きいデフォルト・バッファ・プールを設定することが望ましい対処です。4 MBのOLAPページング・キャッシュが標準的で、そのうちの2 MBはメモリー・リソースが制限されたシステムに使用されます。
|
 Copyright © 2000, 2008, Oracle Corporation. All Rights Reserved. |
|