16 プログラム・グローバル領域のチューニング
この章では、プログラム・グローバル領域(PGA)のチューニング方法を説明します。自動メモリー管理を使用してシステムのデータベース・メモリーを管理している場合は、この章で説明されているように、PGAを手動でチューニングする必要はありません。
この章のトピックは、次のとおりです:
16.1 プログラム・グローバル領域について
プログラム・グローバル領域(PGA)は、サーバー・プロセスのデータおよび制御情報が含まれるプライベート・メモリー領域です。PGAにアクセスできるのはサーバー・プロセスのみです。Oracle Databaseは、サーバー・プロセスのかわりにPGAに対する情報の読取りおよび書込みを行います。そのような情報の例として、カーソルのランタイム領域があります。カーソルを実行するたびに、そのカーソルを実行するサーバー・プロセスのPGAメモリー領域内に、そのカーソルのための新しいランタイム領域が作成されます。
注意:
ランタイム領域の一部は、共有サーバーを使用するときに共有グローバル領域(SGA)内に配置できます。
複雑な問合せ(意思決定支援の問合せなど)の場合、ランタイム領域の大部分が、次のようなメモリー集中型演算子で割り当てられた作業領域に使用されます。
-
ソート・ベース演算子(たとえば、
ORDERBY、GROUPBY、ROLLUPおよびウィンドウ・ファンクション) -
ハッシュ結合
-
ビットマップ・マージ
-
ビットマップ作成
-
一括ロード操作で使用される書込みバッファ
ソート演算子は、作業領域(ソート領域)を使用して一連の行のインメモリー・ソートを実行します。同様に、ハッシュ結合演算子は作業領域(ハッシュ領域)を使用して、ハッシュ表を左側から入力して作成します。
16.1.1 作業領域のサイズ
Oracle Databaseを使用すると、作業領域のサイズを制御およびチューニングできます。一般に、作業領域を大きくすると、メモリー消費量は増えますが特定の演算子のパフォーマンスを大幅に向上できます。次に、使用可能な作業領域のサイズを示します。
-
最適
最適なサイズは、作業領域のサイズが、関連するSQL演算子で割り当てられた入力データや補助メモリー構造を処理できるほど十分に大きなサイズである場合です。これは、作業領域の理想的なサイズです。
-
ワン・パス
ワン・パス・サイズは、作業領域のサイズが最適なサイズを下回っており、入力データの一部で余分なパスが実行される場合です。ワン・パス・サイズでは、レスポンス時間が長くなります。
-
マルチ・パス
マルチ・パス・サイズは、作業領域のサイズがワン・パスしきい値を下回っており、入力データに対する複数のパスが必要な場合です。入力データのサイズに比べて作業領域のサイズが小さすぎるため、マルチ・パス・サイズでは、レスポンス時間が大幅に長くなります。
たとえば、10GBのデータをソートするシリアル・ソート操作では、最適なサイズで実行するには10GBよりも少し多めにする必要があり、ワン・パス・サイズで実行するには少なくとも40MBが必要です。作業領域が40MBに満たない場合は、入力データに対し、ソート操作で複数のパスを実行する必要があります。
作業領域をサイズ設定する際の目標は、最適なサイズ(90%を超える、またはOLTPシステム固有の場合は100%)で大半の作業領域を動作させ、ごく一部をワン・パス・サイズ(10%未満)で動作させることです。次の理由から、マルチ・パス実行はお薦めしません。
-
マルチ・パス実行では、パフォーマンスが大幅に低下する可能性があります。
マルチ・パスの作業領域が多いと、関連付けられたSQL演算子のレスポンス時間に大きな悪影響があります。
-
ワン・パス実行を行うために、大量のメモリーは必要ありません。
ワン・パス・サイズで1GBのデータのソートに必要なのは22MBのみです。
大きなソートとハッシュ結合を実行するDSSシステムの場合であっても、ワン・パスの実行のメモリー要件は相対的に少ない量です。妥当なPGAメモリー量で構成されたシステムは、入力データに対してマルチ・パスを実行する必要がありません。
16.2 自動メモリー管理を使用したプログラム・グローバル領域のサイズ設定
自動PGAメモリー管理により、PGAメモリーの割当て方法が単純化され改善されます。デフォルトでは、PGAメモリー管理は有効化されます。このモードでは、SGAメモリー・サイズの20%をベースとして、作業領域専用のPGAメモリーの一部が動的に調整され、Oracle Databaseにより自動的にPGAが設定されます。最小値は10MBです。
注意:
下位互換性のために、PGA_AGGREGATE_TARGET初期化パラメータを0(ゼロ)に設定して、自動PGAメモリー管理を無効にできます。自動PGAメモリー管理が無効になっている場合は、SORT_AREA_SIZE初期化パラメータなどの関連_AREA_SIZEパラメータを使用して作業領域の最大サイズを設定できます。
この項では、自動PGAメモリー管理を使用してPGAをサイズ設定する方法を説明しており、内容は次のとおりです。
16.2.1 自動PGAメモリー管理の構成
Oracle Databaseを自動PGAメモリー管理モードで実行すると、すべてのセッションの作業領域のサイズ設定は自動になり、*_AREA_SIZEパラメータはそのモードで動作するすべてのセッションで無視されます。アクティブな作業領域に使用できるPGAメモリーの総量は、Oracle Databaseにより、PGA_AGGREGATE_TARGET初期化パラメータから自動的に導出されます。PGAのメモリー量は、システムの他のコンポーネントに割り当てられたPGAメモリー(セッションで割り当てられたPGAメモリーなど)の量をPGA_AGGREGATE_TARGETの値から減算した値に設定されます。その後、Oracle Databaseにより、結果のPGAメモリーが、それぞれの特定のメモリー要件に基づいて個々のアクティブな作業領域に割り当てられます。
Oracle Databaseは、作業領域に割り当てられるPGAのメモリー量を動的に制御することで、DBAが設定したPGA_AGGREGATE_TARGETの値への準拠を試行します。これを達成するために、Oracle Databaseではまず、メモリー集中型のすべてのSQL操作に対する最適な作業領域数の最大化が試行されます。DBAにより(PGA_AGGREGATE_TARGETパラメータを使用して)設定されたPGAメモリーの制限が低すぎるため、マルチ・パスを実行してメモリー消費を削減し、PGAのターゲット制限を満たす必要がある場合を除き、残りの作業領域はワン・パス・モードで実行されます。
データベース・インスタンスを新しく構成する際は、PGA_AGGREGATE_TARGETの適切な設定を判断することが困難な場合があります。
自動PGAメモリー管理を構成するには、次のようにします。
-
「PGA_AGGREGATE_TARGETの初期値の設定」で説明されているように、
PGA_AGGREGATE_TARGETパラメータの値の初期見積りを行います。 -
「自動PGAメモリー管理の監視」で説明されているように、データベース・インスタンスで代表的なワークロードを実行し、パフォーマンスを監視します。
-
「PGA_AGGREGATE_TARGETのチューニング」で説明されているように、Oracle PGAアドバイス統計を使用して
PGA_AGGREGATE_TARGETパラメータの値をチューニングします。
関連項目:
PGA_AGGREGATE_TARGET初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
16.2.2 PGA_AGGREGATE_TARGETの初期値の設定
Oracleデータベース・インスタンスに使用できるメモリー量に基づいて、PGA_AGGREGATE_TARGET初期化パラメータの初期値を設定します。この値は後からインスタンス・レベルでチューニングしたり動的に変更できます。デフォルトで、Oracle Databaseは、この値のSGAサイズの20%を使用します。ただし、この設定は、大規模DSSシステムには小さすぎる場合があります。
PGA_AGGREGATE_TARGETの初期値を設定するには、次のようにします。
-
オペレーティング・システムと、同じシステムで実行されているその他のOracle以外のアプリケーション用に予約する、合計物理メモリー量を決定します。
たとえば、オペレーティング・システムとその他のOracle以外のアプリケーション用に合計物理メモリーの20%を予約する場合、システムのメモリーの80%をOracleデータベース・インスタンスで使用することになります。
-
使用可能な残りのメモリーをSGAとPGAで分割します。
-
OLTPシステムの場合、使用可能なメモリーにおけるPGAメモリーの割合は通常ごくわずかで、残りのメモリーの大部分がSGAに充てられます。
最初は、使用可能なメモリーの20%をPGAに使用し、80%をSGAに使用することをお薦めします。そのため、OLTPシステムの
PGA_AGGREGATE_TARGETパラメータの初期値は、次のようにして計算できます。PGA_AGGREGATE_TARGET= (total_mem* 0.8) * 0.2 (ここで、total_memは、システムで使用可能な物理メモリーの合計量です。) -
メモリー集中型の大量の問合せを実行するDSSシステムでは、通常、PGAメモリーは使用可能なメモリーの70%までを使用します。
最初は、使用可能なメモリーの50%をPGAに使用し、50%をSGAに使用することをお薦めします。そのため、DSSシステムの
PGA_AGGREGATE_TARGETパラメータの初期値は、次のようにして計算できます。PGA_AGGREGATE_TARGET= (total_mem* 0.8) * 0.5 (ここで、total_memは、システムで使用可能な物理メモリーの合計量です。)
-
たとえば、物理メモリーが4GBのシステムで実行するようOracleデータベース・インスタンスが構成されている場合、およびOracleデータベース・インスタンスがメモリーの80% (または3.2GB)を使用している場合、OLTPシステムではPGA_AGGREGATE_TARGETを640MBに、DSSシステムでは1,600MBに設定します。
16.2.3 自動PGAメモリー管理の監視
チューニング・プロセスを開始する前に、データベース・インスタンスで代表的なワークロードを実行し、パフォーマンスを監視してください。Oracle Databaseにより収集されるPGA統計を使用すると、PGAの最大サイズが小さく構成されているか大きく構成されているかを判断できます。これらの統計を監視すると、自動PGAメモリー管理のパフォーマンスを評価でき、PGA_AGGREGATE_TARGETパラメータの値を適宜チューニングできます。
この項では、パフォーマンス・ビューを使用して自動PGAメモリー管理を監視する方法を説明しており、内容は次のとおりです。
16.2.3.1 V$PGASTATビューの使用
V$PGASTATビューは、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
表16-1は、V$PGASTATビューに表示される主な統計を説明しています。
表16-1 V$PGASTATビューの統計
| 統計 | 説明 |
|---|---|
|
|
この統計は、 |
|
|
この統計は、Oracle Databaseが、自動モードで実行中の作業領域に使用できるPGAメモリーの量を示します。この量は、 |
|
|
この統計は、自動モードで実行される作業領域の最大サイズを示します。この値は、作業領域のワークロードの現在の状態を反映するように継続的に調整されます。通常、システム内でアクティブな作業領域の数が増加すると、グローバル・メモリーの上限は低くなります。一般的には、グローバル・バウンドの値は1MBを下回らないようにする必要があります。そうなった場合は、 |
|
|
この統計は、データベース・インスタンスにより割り当てられたPGAメモリーの現在の量を示します。この値はOracle Databaseにより、 |
|
|
この統計は、割り当てられたPGAメモリーのうち解放可能な量を示します。 |
|
|
この統計は、自動モードで実行中の作業領域により現在消費されているPGAメモリーの量を示します。この数値を使用して、PGAメモリーの他のコンシューマ(PL/SQLやJavaなど)で消費されるメモリーの量を判断できます。 PGA other = total PGA allocated - total PGA used for auto workareas |
|
|
この統計は、インスタンス起動時から累積されます。 |
|
|
この統計は、インスタンスの起動後にメモリー集中型SQL演算子によって処理されたバイト数を示します。たとえば、ソート操作の入力サイズが、処理されたバイト数によって示されます。この数値は |
|
|
作業領域が最適に実行できない場合は、1つ以上の余分なパスが入力データで実行されています。この統計は、インスタンスの起動後の追加パスの間に処理されたバイト数を表します。この数値は |
|
|
このメトリックはOracle Databaseによって計算され、PGAメモリー・コンポーネントのパフォーマンスを反映します。この値はインスタンスの起動時から累積されます。値100%は、インスタンス起動後にシステムで実行されたすべての作業領域で、最適な量のPGAメモリーが使用されていることを意味します。これが理想的ですが、純粋なOLTPシステムを除き、そのようになることはほとんどありません。通常は、PGAメモリーの総サイズに応じて、一部の作業領域でワン・パスやマルチ・パスが実行されます。作業領域を最適に実行できない場合は、1つ以上の余分なパスが入力データで実行されています。これにより、入力データのサイズと追加実行されたパス数に比例して、 |
例16-1に、余分なパスがcache hit percentageメトリックにどのように影響するかを示します。
例16-1 キャッシュ・ヒット率の計算
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%です。この値は、余分なパスが実行されたのは小さいソート操作の1つのみで、その他すべてのソート操作は最適なサイズで実行できたことを反映しています。そのため、cache hit percentageはほぼ100%になりますが、それはこの1MB超の余分なパスがわずかなオーバーヘッドであるためです。ただし、大きなソート操作がワン・パス・サイズで実行される場合、EBPは1MBではなく100MBとなり、cache hit percentageは50.73%に低下しますが、それはこの余分なパスがもたらす影響がはるかに大きくなるためです。
16.2.3.2 V$PROCESSビューの使用
V$PROCESSビューでは、データベース・インスタンスに接続されているOracleプロセスごとに1行が表示されます。これらのプロセスのPGAメモリー使用量を監視するには、このビューの次の列を使用します。
-
PGA_USED_MEM -
PGA_ALLOC_MEM -
PGA_FREEABLE_MEM -
PGA_MAX_MEM
例16-2に、このビューの問合せを示します。
例16-2 V$PROCESSビューの問合せ
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 (CJQ0) 533476 1013540 0 1144612
16.2.3.3 V$PROCESS_MEMORYビューの使用
V$PROCESS_MEMORYビューは、それぞれのOracleプロセスの動的なPGAメモリー使用量を名前付きコンポーネント・カテゴリ別に示します。このビューでは、各Oracleプロセスに最大6行が表示され、1つは次のための行です。
-
各名前付きコンポーネント・カテゴリ:
-
Java
-
PL/SQL
-
OLAP
-
SQL
-
-
解放可能
オペレーティング・システムによって、特定のカテゴリにではなくプロセスに割り当てられたメモリー
-
その他
名前を付けられたカテゴリではなく、1つのカテゴリに割り当てられたメモリー
6つのカテゴリのそれぞれに対するOracleプロセスのPGAメモリー使用量を動的に監視するには、このビューの次の列を使用します。
-
CATEGORY -
ALLOCATED -
USED -
MAX_ALLOCATED
注意:
V$PROCESS_MEMORY_DETAILビューには、PGA使用量が500MBを超えるOracleプロセスの動的PGAメモリー使用量が表示されます。Oracle Database 12cリリース2以降では、V$PROCESS_MEMORY_DETAILビューを使用できます。
関連項目:
V$PROCESS_MEMORYビューおよびV$PROCESS_MEMORY_DETAILビューの詳細は、Oracle Databaseリファレンスを参照してください
16.2.3.4 V$SQL_WORKAREA_HISTOGRAMビューの使用
V$SQL_WORKAREA_HISTOGRAMビューには、インスタンス起動後に、最適なメモリー・サイズ、ワン・パス・メモリー・サイズおよびマルチ・パス・メモリー・サイズで実行された作業領域の総数が示されます。このビューの統計は、バケットに分割されます。バケットは、作業領域の最適なメモリー要件によって定義されます。各バケットは、LOW_OPTIMAL_SIZEおよびHIGH_OPTIMAL_SIZE列の値で指定された最適メモリー要件の範囲によって識別されます。
たとえば、ソート操作を最適なサイズ(キャッシュ)で実行するには、3MBのメモリーが必要です。このソート操作により使用された作業領域に関する統計は、次のようにして定義されるバケットに配置されます。
-
LOW_OPTIMAL_SIZE= 2097152 (2MB) -
HIGH_OPTIMAL_SIZE= 4194303 (4MBから1バイト減算)
最適、ワン・パスまたはマルチ・パスのサイズで作業領域を実行する場合のパフォーマンスの影響は、その作業領域のサイズに大きく依存するため、統計は作業領域のサイズでセグメント化されます。3MBが最適なサイズのその範囲に含まれるため、この例では、作業領域の統計はこのバケットに配置されています。
例16-3 V$SQL_WORKAREA_HISTOGRAMビューの問合せ: 空でないバケット
次の問合せでは、空でないすべてのバケットの統計が示されます。
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
この例の出力は、1MBから2MBのバケットで551の作業領域が最適なサイズで実行されている一方で、16がワン・パス・サイズで実行され、マルチ・パス・サイズで実行されている作業領域はないことを示しています。また、1MB未満のすべての作業領域が最適なサイズで実行できたことも示されています。
例16-4 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(multipass_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%)はワン・パス・サイズで実行され、マルチ・パス・サイズで実行されたものはありませんでした。
16.2.3.5 V$WORKAREA_ACTIVEビューの使用
V$WORKAREA_ACTIVEビューには、データベース・インスタンスでアクティブ(または実行中)な作業領域が表示されます。小さいアクティブなソート操作(64KB未満)はこのビューから除外されます。すべてのアクティブな作業領域のサイズを正確に監視したり、それらのアクティブな作業領域が一時セグメントに流用されているかどうかを判断するには、このビューを使用します。
例16-5に、このビューの問合せを示します。
例16-5 V$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;
この問合せの出力例を次に示します。
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列)で動作しているハッシュ結合操作(OPERATION列)を、セッション12(SID列)が実行しています -
PGAメモリー・マネージャが予測する、このハッシュ結合操作で使用する最大メモリー量は18044 KB (
ESIZE列)です -
作業領域は現在11406KBのメモリー(
MEM列)を使用しています -
作業領域は、過去に最大21406KBのPGAメモリー(
MAXMEM列)を使用しました -
作業領域は、120000KBの一時セグメント (
TSIZE列)に流用されました
作業領域を割当て解除されたとき、すなわち、関連するSQL演算子の実行が完了したときに、このビューから自動的に削除されます。
16.2.3.6 V$SQL_WORKAREAビューの使用
実行計画が1つ以上の作業領域を使用するカーソルがロードされるたびに、累積された作業領域の統計がメンテナンスされます。作業領域が割当て解除されるたびに、V$SQL_WORKAREAビューがその作業領域の実行統計で更新されます。
V$SQL_WORKAREAビューをV$SQLビューと結合して、作業領域をカーソルに関連付けることができ、V$SQL_PLANビューと結合した場合は、計画のどの演算子が作業領域を使用するかを正確に判断できます。
例16-6に、このビューの問合せを3つ示します。
例16-6 V$SQL_WORKAREAビューの問合せ
次の問合せでは、最もキャッシュ・メモリーを必要とする上位10個の作業領域を検索します。
SELECT *
FROM (SELECT workarea_address, operation_type, policy, estimated_optimal_size
FROM V$SQL_WORKAREA
ORDER BY estimated_optimal_size DESC)
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%';16.2.4 PGA_AGGREGATE_TARGETのチューニング
PGA_AGGREGATE_TARGET初期化パラメータの値のチューニングを容易にするため、Oracle Databaseには、V$PGA_TARGET_ADVICEおよびV$PGA_TARGET_ADVICE_HISTOGRAMの2つのPGAパフォーマンス・アドバイザ・ビューが提供されています。これらのビューを使用することで、経験的な方法でPGA_AGGREGATE_TARGETパラメータの値をチューニングする必要がありません。かわりに、これらのビューを使用して、PGA_AGGREGATE_TARGETパラメータの値を変更すると、主要なPGA統計にどのような影響があるかを予測できます。
この項では、PGA_AGGREGATE_TARGET初期化パラメータの値をチューニングする方法を説明しており、内容は次のとおりです。
16.2.4.1 PGAパフォーマンス・アドバイザ・ビューの自動生成の有効化
Oracle Databaseでは、ワークロードの履歴を記録し、その履歴をPGA_AGGREGATE_TARGETパラメータの様々な値でシミュレートすることによって、V$PGA_TARGET_ADVICEおよびV$PGA_TARGET_ADVICE_HISTOGRAMビューを生成します。可能性のある高い値および低い値を評価するため、PGA_AGGREGATE_TARGETパラメータの値は、その現在の値の分数または倍数から導出されます。これらの値は予測に使用され、範囲は10MBから最大256GBまでです。このシミュレーション・プロセスはバックグラウンドで実行され、ワークロードの履歴を継続的に更新してシミュレーション結果を生成します。これらのビューを問い合せれば、いつでも結果を表示できます。
PGAパフォーマンス・アドバイザ・ビューの自動生成を有効化するには、次のようにします。
-
PGA_AGGREGATE_TARGETパラメータを設定して、自動PGAメモリー管理を有効化します。このパラメータを0に設定すると、自動PGAメモリー管理を無効化できますが、推奨されません。このパラメータの設定方法の詳細は、「PGA_AGGREGATE_TARGETの初期値の設定」を参照してください。
-
STATISTICS_LEVELパラメータをTYPICAL(デフォルト)またはALLに設定します。このパラメータを
BASICに設定すると、PGAパフォーマンス・アドバイス・ビューの生成を無効化できますが、推奨されません。
注意:
PGAアドバイス・パフォーマンス・ビューの内容は、インスタンスの起動時か、PGA_AGGREGATE_TARGETパラメータの値が変更されたときにリセットされます。
16.2.4.2 V$PGA_TARGET_ADVICEビューの使用
V$PGA_TARGET_ADVICEビューでは、PGA_AGGREGATE_TARGET初期化パラメータの値を変更した場合に、V$PGASTATビューの次の統計にどのような影響があるかを予測できます。
-
cache hit percentage
-
over allocation count
次の例に、このビューの問合せを示します。
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
次の図は、この問合せの結果をプロットする方法を示しています。
この曲線は、PGA_AGGREGATE_TARGETパラメータの値が増加するにつれて、PGAのcache hit percentageが上昇する様子を示しています。グラフの陰付きのゾーンは、ESTD_OVERALLOCATION_COUNT列の値がゼロではない、over allocationゾーンを表します。この領域は、PGA_AGGREGATE_TARGETパラメータの値が小さすぎるため、PGAメモリーの最低要件を満たしていないことを示します。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パラメータの値の増加による上昇がわずかしかないポイントに達します。図では、この状態はPGA_AGGREGATE_TARGETが3GBに達したときに発生しています。この時点で、cache hit percentageは83%で、PGAメモリーに1GBを追加してもわずかしか向上(2%)しません。この例では、PGA_AGGREGATE_TARGETパラメータの最適値は3GBです。
注意:
PGAのcache hit percentageの理論的な最大値が100%の場合でも、作業領域の実際の最大サイズには制限があるため、PGA_AGGREGATE_TARGETパラメータの値をさらに増やしても、理論的な最大値に達しない場合があります。これが発生するのは、最適メモリー要件が大きく、cache hit percentageの値が低いヒット率(90%など)に下がる可能性のある大規模DSSシステムのみです。
PGA_AGGREGATE_TARGETパラメータの値は、最適値に設定するか、少なくともover allocationゾーンを超えた範囲で可能な最大値に設定するのが理想的です。一般的には、PGAのcache hit percentageは60%以上に設定します。60%の時点でシステムは、理想的な状況で実際に処理する必要のあるバイト数のほぼ2倍の量を処理するためです。この例では、PGA_AGGREGATE_TARGETパラメータの値は最低500MBで、可能なかぎり3GBに近い値に設定する必要があります。ただし、PGA_AGGREGATE_TARGETパラメータの適切な設定は、PGAコンポーネントにどれだけのメモリーを使用できるかによって異なります。一般的に、PGAメモリーを追加する場合は、共有プールまたはバッファ・キャッシュなど、一部のSGAコンポーネントのメモリーを減らす必要がありますが、これはデータベース・インスタンスに使用する全メモリーが、システムで使用できる物理メモリー量の制約を受ける場合があるためです。したがって、システム内で使用可能なメモリーと、様々なSGAコンポーネントのパフォーマンス(共有プール・アドバイザの統計およびバッファ・キャッシュ・アドバイザの統計で監視)という、より大きな視点で、PGAメモリーの増量の決定を下す必要があります。メモリーをSGAコンポーネントから減らせない場合は、システムへの物理メモリーの追加を検討してください。
16.2.4.3 V$PGA_TARGET_ADVICE_HISTOGRAMビューの使用
V$PGA_TARGET_ADVICE_HISTOGRAMビューでは、PGA_AGGREGATE_TARGET初期化パラメータの値を変更した場合に、V$SQL_WORKAREA_HISTOGRAMビューの統計にどのような影響があるかが予測されます。このビューを使用すると、予測に使用するPGA_AGGREGATE_TARGET値における、最適、ワン・パス、マルチ・パスの各作業領域の予測実行回数に関する詳細情報を表示できます。
V$PGA_TARGET_ADVICE_HISTOGRAMビューはV$SQL_WORKAREA_HISTOGRAMビューと同じであり、予測に使用するPGA_AGGREGATE_TARGET値を示す2つの追加列があります。したがって、PGA_AGGREGATE_TARGETパラメータの希望値を選択するための追加の述語を使用し、V$SQL_WORKAREA_HISTOGRAMビューに対して実行される任意の問合せをこのビューで使用できます。
例16-7に、PGA_AGGREGATE_TARGETパラメータの値を現在の値の2倍に設定したときの、V$SQL_WORKAREA_HISTOGRAMビューの予測内容を表示するこのビューの問合せを示します。
例16-7 V$PGA_TARGET_ADVICE_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未満のすべての作業領域を最適サイズで実行できることを示しています。
16.2.4.4 V$SYSSTATおよびV$SESSTATビューの使用
V$SYSSTATビューとV$SESSTATビューの統計は、最適、ワン・パスおよびマルチ・パスの各メモリー・サイズで実行された作業領域の総数を示します。これらの統計は、インスタンスまたはセッションが開始された後から累積されます。
例16-8に、インスタンスの起動後にこれら3つのサイズで作業領域が実行された合計回数と割合を表示するV$SYSSTATビューの問合せを示します。
例16-8 V$SYSSTATビューの問合せ
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
この例の出力では、5,395 (または95%)の作業領域実行が最適なサイズで実行され、284 (または5%)の作業領域実行がワン・パス・サイズで実行されたことを示しています。
16.2.4.5 チュートリアル: PGA_AGGREGATE_TARGETのチューニング方法
このチュートリアルは、この章で説明されている様々なビューを使用して、PGA_AGGREGATE_TARGETパラメータの値をチューニングするためのガイドラインです。
PGA_AGGREGATE_TARGETをチューニングするには、次のようにします。
-
メモリーを過剰に割り当てないように
PGA_AGGREGATE_TARGETパラメータの値を設定します。「V$PGA_TARGET_ADVICEビューの使用」で説明されているように、
V$PGA_TARGET_ADVICEビューを使用して、PGA_AGGREGATE_TARGETの値がover-allocationゾーン内に設定されていないことを確認します。例16-8では、PGA_AGGREGATE_TARGETの値は最低375MBに設定する必要があります。 -
レスポンス時間の要件およびメモリーの制約に基づいて、PGAの
cachehitpercentageを最大化します。「V$PGA_TARGET_ADVICEビューの使用」で説明されているように、
V$PGA_TARGET_ADVICEビューを使用して、PGA_AGGREGATE_TARGETパラメータの最適値を判断し、パラメータをその値に設定するか可能なかぎり最大の値に設定します。PGAに割当て可能なメモリーの制限Xを推定します。
-
制限Xが最適値より大きい場合は、
PGA_AGGREGATE_TARGETパラメータの値を最適値に設定します。例16-8で、10GBをPGA専用とする場合は、
PGA_AGGREGATE_TARGETパラメータの値を3GBに設定し、残りの7GBをSGAに使用します。 -
制限Xが最適値より小さい場合は、
PGA_AGGREGATE_TARGETパラメータの値をXに設定します。例16-8で、2GBのみをPGA専用とする場合は、
PGA_AGGREGATE_TARGETパラメータの値を2GBに設定し、75%のcachehitpercentageを受け入れます。
-
-
PGA_AGGREGATE_TARGETパラメータの新しい値が、最適およびワン・パスの作業領域実行の必要な数になることを検証し、マルチ・パス作業領域実行を回避します。「V$PGA_TARGET_ADVICE_HISTOGRAMビューの使用」で説明されているように、
V$PGA_TARGET_ADVICE_HISTOGRAMビューを使用して、最適、ワン・パスおよびマルチ・パスの各作業領域実行の数を予測します。 -
より多くのPGAメモリーが必要な場合は、SGAコンポーネントからメモリーを減らすか、システムに物理メモリーを追加してPGAメモリーを増やします。
-
任意の時点で、最適、ワン・パスおよびマルチ・パスの各作業領域実行の数が予測に一致することを確認し、必要な場合は
PGA_AGGREGATE_TARGETパラメータの値をチューニングします。「V$SYSSTATおよびV$SESSTATビューの使用」で説明されているように、
V$SYSSTATおよびV$SESSTATビューを使用して、インスタンスの起動またはセッションの開始以降の最適、ワン・パスおよびマルチ・パスの各メモリー・サイズで実行された作業領域の合計数を確認します。
16.3 絶対制限指定によるプログラム・グローバル領域のサイズ設定
自動PGAメモリー管理モードでは、Oracle Databaseは、作業領域に割り当てられるPGAのメモリー量を動的に制御することで、PGA_AGGREGATE_TARGETの値への準拠を試行します。ただし、次の理由で、PGAメモリー使用量がPGA_AGGREGATE_TARGET設定を超える場合があります。
-
PGA_AGGREGATE_TARGET設定が制限ではなく、目標として機能するため。 -
PGA_AGGREGATE_TARGETで制御されるのがチューニング可能なメモリーの割当てのみであるため。
-
PGA_AGGREGATE_LIMITパラメータを使用して、全体のPGAメモリーの使用量に対して絶対制限を設定します。 -
リソース・マネージャのプロシージャ
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVEを使用して、特定のコンシューマ・グループ内の各セッションのPGAメモリー使用量に絶対制限を設定します。リソース・マネージャを使用したプログラム・グローバル領域のサイズ設定を参照してください
16.3.1 PGA_AGGREGATE_LIMITパラメータを使用したプログラム・グローバル領域のサイズ設定
PGA_AGGREGATE_LIMIT初期化パラメータを使用すると、PGAメモリー使用量に絶対制限を指定できます。PGA_AGGREGATE_LIMITの値を超過している場合は、Oracle Databaseにより、チューニングできないPGAメモリーを最も消費しているセッションやプロセスが、次の順番で中断または終了されます。
-
チューニングできないPGAメモリーを最も消費しているセッションのセルが中断されます。
-
PGAのメモリー使用量が依然として
PGA_AGGREGATE_LIMITを超過している場合は、チューニングできないPGAメモリーを最も消費しているセッションおよびプロセスが終了されます。
中断または終了するセッションやプロセスを決定する際、Oracle Databaseでは、パラレル問合せが1つの単位として処理されます。
デフォルトで、PGA_AGGREGATE_LIMITパラメータは2GBより大きく設定されるか、PGA_AGGREGATE_TARGET値の200%、またはPROCESSESパラメータの値の300万倍に設定されます。ただし、物理メモリーのサイズからSGAの合計サイズを引いた数の120%を超えることはありません。デフォルト値はアラート・ログに印刷されます。システムの物理メモリー量を判別できない場合は、警告のメッセージがアラート・ログに印刷されます。
PGA_AGGREGATE_LIMITを設定するには、次のようにします。
-
PGA_AGGREGATE_LIMIT初期化パラメータを、バイト単位で必要な値に設定します。値は、
K(KBの場合)、M(MBの場合)またはG(GBの場合)が続く数値として表されます。値を0に設定すると、PGAメモリーの強い制限が無効化されます。
16.3.2 リソース・マネージャを使用したプログラム・グローバル領域のサイズ設定
Oracle Databaseリソース・マネージャのDBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVEプロシージャでSESSION_PGA_LIMITパラメータを使用して、特定のコンシューマ・グループ内の各セッションに割り当てられるPGAメモリー使用量に絶対制限を設定できます。セッションがコンシューマ・グループに設定されたPGAメモリー制限を超過した場合、そのセッションは終了しORA-10260エラー・メッセージが表示されます。
関連項目:
-
Oracle Database管理者ガイドのトピック
-
コンシューマ・グループ内の各セッションに対するPGAメモリーの制限の詳細は、プログラム・グローバル領域(PGA)に関する項を参照してください。
-
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVEプロシージャを使用したリソース・プラン・ディレクティブの作成の詳細は、リソース・プラン・ディレクティブの作成に関する項を参照してください。
-
-
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVEプロシージャの構文は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください。