16 プログラム・グローバル領域のチューニング

この章では、プログラム・グローバル領域(PGA)のチューニング方法を説明します。自動メモリー管理を使用してシステムのデータベース・メモリーを管理している場合は、この章で説明されているように、PGAを手動でチューニングする必要はありません。

この章のトピックは、次のとおりです:

16.1 プログラム・グローバル領域について

プログラム・グローバル領域(PGA)は、サーバー・プロセスのデータおよび制御情報が含まれるプライベート・メモリー領域です。PGAにアクセスできるのはサーバー・プロセスのみです。Oracle Databaseは、サーバー・プロセスのかわりにPGAに対する情報の読取りおよび書込みを行います。そのような情報の例として、カーソルのランタイム領域があります。カーソルを実行するたびに、そのカーソルを実行するサーバー・プロセスのPGAメモリー領域内に、そのカーソルのための新しいランタイム領域が作成されます。

注意:

ランタイム領域の一部は、共有サーバーを使用するときに共有グローバル領域(SGA)内に配置できます。

複雑な問合せ(意思決定支援の問合せなど)の場合、ランタイム領域の大部分が、次のようなメモリー集中型演算子で割り当てられた作業領域に使用されます。

  • ソート・ベース演算子(たとえば、ORDER BYGROUP BYROLLUPおよびウィンドウ・ファンクション)

  • ハッシュ結合

  • ビットマップ・マージ

  • ビットマップ作成

  • 一括ロード操作で使用される書込みバッファ

ソート演算子は、作業領域(ソート領域)を使用して一連の行のインメモリー・ソートを実行します。同様に、ハッシュ結合演算子は作業領域(ハッシュ領域)を使用して、ハッシュ表を左側から入力して作成します。

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メモリー管理を構成するには、次のようにします。

  1. 「PGA_AGGREGATE_TARGETの初期値の設定」で説明されているように、PGA_AGGREGATE_TARGETパラメータの値の初期見積りを行います。

  2. 「自動PGAメモリー管理の監視」で説明されているように、データベース・インスタンスで代表的なワークロードを実行し、パフォーマンスを監視します。

  3. 「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の初期値を設定するには、次のようにします。

  1. オペレーティング・システムと、同じシステムで実行されているその他のOracle以外のアプリケーション用に予約する、合計物理メモリー量を決定します。

    たとえば、オペレーティング・システムとその他のOracle以外のアプリケーション用に合計物理メモリーの20%を予約する場合、システムのメモリーの80%をOracleデータベース・インスタンスで使用することになります。

  2. 使用可能な残りのメモリーを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ビューの統計

統計 説明

aggregate PGA target parameter

この統計は、PGA_AGGREGATE_TARGETパラメータの現在の値を示します。デフォルト値は、SGAサイズの20%です。このパラメータを0に設定すると、自動PGAメモリー管理が無効化されます。

aggregate PGA auto target

この統計は、Oracle Databaseが、自動モードで実行中の作業領域に使用できるPGAメモリーの量を示します。この量は、PGA_AGGREGATE_TARGETパラメータの値と現在の作業領域のワークロードから動的に導出されます。したがって、Oracle Databaseにより継続的に調整されます。この値がPGA_AGGREGATE_TARGETの値と比較して小さいと、システムの他のコンポーネント(PL/SQLまたはJavaなど)によりPGAメモリーの大部分が使用され、作業領域用にはわずかしか残りません。自動モードで実行されている作業領域用に十分なPGAメモリーが残るようにしてください。

global memory bound

この統計は、自動モードで実行される作業領域の最大サイズを示します。この値は、作業領域のワークロードの現在の状態を反映するように継続的に調整されます。通常、システム内でアクティブな作業領域の数が増加すると、グローバル・メモリーの上限は低くなります。一般的には、グローバル・バウンドの値は1MBを下回らないようにする必要があります。そうなった場合は、PGA_AGGREGATE_TARGETパラメータの値を増やします。

total PGA allocated

この統計は、データベース・インスタンスにより割り当てられたPGAメモリーの現在の量を示します。この値はOracle Databaseにより、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 DatabaseはPGA_AGGREGATE_TARGETの値を満たすことができないため、追加のPGAメモリーを割り当てる必要があります。過剰割当てが発生した場合は、「V$PGA_TARGET_ADVICEビューの使用」で説明されているように、V$PGA_TARGET_ADVICEビューにより提供される情報を使用して、PGA_AGGREGATE_TARGETパラメータの値を増やします。

total bytes processed

この統計は、インスタンスの起動後にメモリー集中型SQL演算子によって処理されたバイト数を示します。たとえば、ソート操作の入力サイズが、処理されたバイト数によって示されます。この数値はcache hit percentageメトリックを計算する場合に使用します。

extra bytes read/written

作業領域が最適に実行できない場合は、1つ以上の余分なパスが入力データで実行されています。この統計は、インスタンスの起動後の追加パスの間に処理されたバイト数を表します。この数値はcache hit percentageメトリックを計算する場合にも使用します。total bytes processedに比べて小さい値であることが理想的です。

cache hit percentage

このメトリックはOracle Databaseによって計算され、PGAメモリー・コンポーネントのパフォーマンスを反映します。この値はインスタンスの起動時から累積されます。値100%は、インスタンス起動後にシステムで実行されたすべての作業領域で、最適な量のPGAメモリーが使用されていることを意味します。これが理想的ですが、純粋なOLTPシステムを除き、そのようになることはほとんどありません。通常は、PGAメモリーの総サイズに応じて、一部の作業領域でワン・パスやマルチ・パスが実行されます。作業領域を最適に実行できない場合は、1つ以上の余分なパスが入力データで実行されています。これにより、入力データのサイズと追加実行されたパス数に比例して、cache hit percentageが低下します。このメトリックの計算方法の例は、例16-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および例16-4は、このビューを問い合せる2つの方法を示します。

例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メモリー(MAX MEM列)を使用しました

  • 作業領域は、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パフォーマンス・アドバイザ・ビューの自動生成を有効化するには、次のようにします。

  1. PGA_AGGREGATE_TARGETパラメータを設定して、自動PGAメモリー管理を有効化します。

    このパラメータを0に設定すると、自動PGAメモリー管理を無効化できますが、推奨されません。このパラメータの設定方法の詳細は、「PGA_AGGREGATE_TARGETの初期値の設定」を参照してください。

  2. 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

次の図は、この問合せの結果をプロットする方法を示しています。

図16-1 V$PGA_TARGET_ADVICEのグラフ

図16-1の説明が続きます
「図16-1 V$PGA_TARGET_ADVICEのグラフ」の説明

この曲線は、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をチューニングするには、次のようにします。

  1. メモリーを過剰に割り当てないようにPGA_AGGREGATE_TARGETパラメータの値を設定します。

    「V$PGA_TARGET_ADVICEビューの使用」で説明されているように、V$PGA_TARGET_ADVICEビューを使用して、PGA_AGGREGATE_TARGETの値がover-allocationゾーン内に設定されていないことを確認します。例16-8では、PGA_AGGREGATE_TARGETの値は最低375MBに設定する必要があります。

  2. レスポンス時間の要件およびメモリーの制約に基づいて、PGAのcache hit percentageを最大化します。

    「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%のcache hit percentageを受け入れます。

  3. PGA_AGGREGATE_TARGETパラメータの新しい値が、最適およびワン・パスの作業領域実行の必要な数になることを検証し、マルチ・パス作業領域実行を回避します。

    「V$PGA_TARGET_ADVICE_HISTOGRAMビューの使用」で説明されているように、V$PGA_TARGET_ADVICE_HISTOGRAMビューを使用して、最適、ワン・パスおよびマルチ・パスの各作業領域実行の数を予測します。

  4. より多くのPGAメモリーが必要な場合は、SGAコンポーネントからメモリーを減らすか、システムに物理メモリーを追加してPGAメモリーを増やします。

  5. 任意の時点で、最適、ワン・パスおよびマルチ・パスの各作業領域実行の数が予測に一致することを確認し、必要な場合は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を過剰に使用すると、スワッピング率が高くなる可能性があります。そうなった場合は、システムが応答せず、不安定になります。その場合、次のいずれかの方法を使用して、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メモリーの強い制限が無効化されます。

関連項目:

  • PGA_AGGREGATE_LIMIT初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください

  • V$PGASTATビューの詳細は、『Oracle Databaseリファレンス』を参照してください

  • Oracle Database Resource Managerおよびコンシューマ・グループの詳細は、『Oracle Database管理者ガイド』を参照してください

16.3.2 リソース・マネージャを使用したプログラム・グローバル領域のサイズ設定

Oracle Databaseリソース・マネージャのDBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVEプロシージャでSESSION_PGA_LIMITパラメータを使用して、特定のコンシューマ・グループ内の各セッションに割り当てられるPGAメモリー使用量に絶対制限を設定できます。セッションがコンシューマ・グループに設定されたPGAメモリー制限を超過した場合、そのセッションは終了しORA-10260エラー・メッセージが表示されます。

関連項目: