皆さんこんにちは、今年は連日の猛暑日と非常に厳しい夏でしたが、やっと秋らしく過ごしやすい気候になってきましたね。 今回は、まだTEMP領域の使用で苦労されている方も多いようですので、第45回の続きとしてTEMP領域の使用を削減する（PGAメモリの使用を少なくする）方法について、自動PGAメモリ管理のメカニズムや問題点も含めて説明しようと思います。後半に、実行計画のE-RowsとA-Rowsの比較についても説明していますので、参考にしてください。
1. PGAメモリの補足
第45回では、TEMP領域の使用を改善するのに、一つのプロセスで使用できるメモリ・サイズの制限から、パラレル度を増やしてくださいと説明しましたが、同時実行数やPGA_AGGREGATE_TARGETの値によっても改善する方法が異なるので、補足として以下についてもう少し説明します。
- 自動PGAメモリ管理の問題点
- PGAメモリ・チューニングの考え方
- SQL作業領域の削減
（１）自動PGAメモリ管理の問題点
まずは、第14回の続きとして、自動PGAメモリ管理で問題となることをメカニズムも交えて説明します。
PGAメモリで最も多く使用するのが、SQL作業領域（ソート領域やハッシュ領域など）です。SQL作業領域は、最適なサイズがSQLごとに異なり同時実行を考えると、それぞれを最適な値で指定するのは簡単なことではありません。そのため、自動PGAメモリ管理により、割り当て可能な合計サイズ（初期化パラメータPGA_AGGREGATE_TARGET）から必要なサイズを割り当てるようにしました。ただし、単一SQLと同時実行のどちらにも対応するように、以下のような単一プロセスの最大サイズを使用しているので、SQL作業領域が非常に大きいSQLだと、シリアル実行ではTEMP領域を使用してしまいます。
- 単一サーバー・プロセスでの最大SQL作業領域サイズ
以下のようにPGA_AGGREGATE_TARGETから、ある程度の同時実行性を考慮して最大サイズを決定します。Oracle Database 10gR2（Oracle10gR2）から拡張されて、最大値が100Mバイトから1Gバイトになっていますが、シリアル実行ではこれより多く使用することはできません。
バージョン PGA_AGGREGATE_TARGETの値 最大SQL作業領域サイズ 10gR1以前 － MIN(PGA_AGGREGATE_TARGETの5%, 100Mバイト) 10gR2以降 1Gバイト未満 MIN(PGA_AGGREGATE_TARGETの20%, 100Mバイト) 1Gバイト以上 MIN(PGA_AGGREGATE_TARGETの10%, 1Gバイト) - パラレル実行時の単一PXプロセスでの最大SQL作業領域サイズ
パラレル実行のときには、PGA_AGGREGATE_TARGETの50%÷パラレル度（Oracle10gR1以前はPGA_AGGREGATE_TARGETの30%÷パラレル度）と単一サーバー・プロセスの最大サイズの小さい方を、単一PXプロセスの最大サイズとします。つまり、パラレル度を増やすと、より多く使用できますが、あまりSQLを同時実行することはできません。
Oracle10gR2から最大サイズを大きくすることで、TEMP領域の使用が削減されていますが、同時実行のSQLが多くなるとPGA_AGGREGATE_TARGETをオーバーして、メモリ不足（ORA-04030）が発生しやすくなっています（つまり、PGA_AGGREGATE_TARGETでPGAサイズを制限することはできません）。そのため、Oracle Database 12c（Oracle12c）から発生しないように、初期化パラメータPGA_AGGREGATE_LIMITが追加されています。このように、すべてで最適にできる訳ではないことから、苦労されている方が多いのだと思います。
PGA_AGGREGATE_LIMITについて
ご存知ない方のために、ここでOracle12cからのPGA_AGGREGATE_LIMITについて簡単に説明します。
自動PGAメモリ管理は、同時実行数が増えるとPGA_AGGREGATE_TARGETをオーバーしてしまう可能性があります。そのため、Oracle12cからハードリミットとして初期化パラメータPGA_AGGREGATE_LIMITを追加して、メモリ不足（ORA-04030）にならないように、以下の条件でデフォルト値を設定しています。
・MAX(PGA_AGGERATE_TARGETの200%, PROCESSES×3Mバイト, 2Gバイト)
・物理メモリ・サイズから合計SGAサイズを引いた値の90%を超えない
・PGA_AGGREGATE_TARGETを下回らない
Oracle12cR2からは、デフォルト値と指定できる値が、以下のように変更されています（初期化パラメータMEMORY_TARGETが反映され、2Gバイトより小さくできないようになりました）。
・MEMORY_TARGETが設定されている場合はMEMORY_TARGETの値
・MEMORY_TARGETが0の場合はPGA_AGGREGATE_TARGETの200%
・MEMORY_TARGETとPGA_AGGREGATE_TARGETが0の場合は物理メモリ・サイズから合計SGAサイズを引いた値の90%
・すべての場合で2GバイトまたはPROCESSES×3Mバイトの値を下回らない
この値を超えるとORA-04036になり、大量にメモリを使用しているセッションを強制終了して、メモリを解放します。そのため、同時実行数が多いときに、メモリの使用が多いSQLを実行させないようにすることができます。
（２）PGAメモリ・チューニングの考え方
次に、このようなPGAメモリをどのようにチューニングするかを説明します。
自動PGAメモリ管理は、極端な環境（単一SQLのメモリ・サイズが非常に大きい、同時実行するSQLが非常に多いなど）では効果的に動作することが難しいです。そのような場合には、どちらを優先するか決める必要があります。同時実行を優先するときには、SQL作業領域を少なくするSQLにしないと、ある程度のTEMP領域の使用は発生することになります。以下に、それぞれの考え方とチューニングの手順をまとめてみました。ここで大事なのが、PGA_AGGREGATE_TARGETが上限とはならないので、単一プロセスの最大サイズを設定するために（TEMP領域を使用するしきい値として）使用するということです。
・単一SQLのメモリ・サイズを大きくしたい場合
① TEMP領域を使用しているときにはPGA_AGGREGATE_TARGETを増やす
② 最大サイズを1Gバイトより大きくするときはパラレル実行を行う（データの偏りでいくつかのPXプロセスがTEMP領域を使用しないように注意する）
③ パラレル度を多くするときはメモリ不足にならないようにする（メモリ・サイズの大きいSQLの同時実行を減らす）
④ パラレル実行できないときは手動PGAメモリ管理を使用する
・同時実行するSQLを多くしたい場合
① メモリ不足になるようなときはSQL作業領域が少ないSQLにする
② 削減できないときは単一プロセスの最大サイズを小さくする（PGA_AGGREGATE_TARGETを減らしてPGA_AGGREGATE_LIMITを調整する）
③ TEMP領域を使用するときには最低でも第14回で説明したone-passになるようにする
どちらの場合でも、パフォーマンスを向上させるために、使用するSQL作業領域をできるだけ少なくすることを忘れないでください。
（３）SQL作業領域の削減
次に、SQL作業領域を削減する方法について説明します。
特に、同時実行が多いシステムでは、SQL作業領域を使用しないか、できるだけサイズを削減して行う必要があります。第45回では、索引やパーティションを使用することを説明しましたが、それ以外にも行数の削減（第29回のGroup By Placementや第9回の上位N件など）と行サイズの削減がありますが、ここではまだ説明していない行サイズを削減する方法を紹介します。
行サイズを削減するには、SQL作業領域を使用する処理に、必要ない列を含めないようにします（特に、サイズの大きい列がある場合には効果が大きいです）。具体的には、SQL作業領域を使用する処理を、必要最小限の列だけで副問合せを作成して、最終的な結果に必要な列を再結合して求めます。第47回で説明したように、同一表に何度もアクセスするのは効率よくないのですが、TEMP領域の使用で性能問題になっているのであれば、再結合した方が効果的な場合もあるので、一つの選択肢として覚えておいてください。
分かりやすいように、分析ファンクションの例を使用して説明します。以下のSQLでは、実行計画にUsed-Tmpが出力されているので、TEMP領域を使用していますが、この分析ファンクションには列c3は必要ありません（この例で使用している列c3は、比較しやすいようにchar(1000)と大きいサイズを使用しています）。
2 FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c2 ORDER BY c1) a1, c3 FROM tab02) WHERE a1 < 10;
——————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| | A-Rows | | Used-Mem | Used-Tmp|
——————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | | 90 | | | |
|* 1 | VIEW | | 1 | 100K| 96M| | 90 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 100K| 96M| | 100 | | 97M (1)| 2048 |
| 3 | TABLE ACCESS FULL | TAB02 | 1 | 100K| 96M| | 100K| | | |
——————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(“A1″<10)
2 – filter(ROW_NUMBER() OVER ( PARTITION BY “A”.”C2″ ORDER BY “A”.”C1″)<10)
以下のようなSQLに書き換えることで、分析ファンクションの使用メモリ・サイズ（Used-Mem）を削減することができます（TEMP領域も使用していません）。
2 FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c2 ORDER BY c1) a1, c1 FROM tab02) A, tab02 B
3 WHERE A.a1 < 10 and A.c1 = B.c1;
———————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| | A-Rows | | Used-Mem |
———————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | | 90 | | |
|* 1 | HASH JOIN | | 1 | 100K| 98M| | 90 | | 9401K (0)|
|* 2 | VIEW | | 1 | 100K| 2539K| | 90 | | |
|* 3 | WINDOW SORT PUSHED RANK| | 1 | 100K| 2539K| | 100 | | 5338K (0)|
| 4 | TABLE ACCESS FULL | TAB02 | 1 | 100K| 781K| | 100K| | |
| 5 | TABLE ACCESS FULL | TAB02 | 1 | 100K| 95M| | 100K| | |
———————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“A”.”C1″=”B”.”C1″)
2 – filter(“A”.”A1″<10)
3 – filter(ROW_NUMBER() OVER ( PARTITION BY “C2” ORDER BY “C1”)<10)
（４）パラレル・パーティション・ワイズ処理の拡張
最後に、SQL作業領域の削減に有効なパラレル・パーティション・ワイズ処理の拡張について説明します。
パラレル・パーティション・ワイズ処理が、第60回で説明したOracle12cR2からのDISTICT演算子に続き、Oracle18cから分析ファンクションも可能になりました。分析ファンクションは、SQL作業領域を多く使用してしまう場合もあるので、Oracle18cからパラレル・パーティション・ワイズ処理が可能になり、改善するための選択肢が増えています。
以下のように、パラレル実行の分散ファンクション（ウィンドウ関数）が、Oracle18cからパーティション・ワイズ処理で動作するようになっています（以下の実行計画は、列c3でレンジ・パーティション化しているときの実行で、Oracle18cはWINDOW SORTをデータ再分散しないで処理しています）。
実行計画(12cR2)
——————————————————————————————-
| Id | Operation | Name | | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
——————————————————————————————-
| 0 | SELECT STATEMENT | | | | | | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | | Q1,01 | P->S | QC (RAND) |
| 3 | WINDOW SORT | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | | | | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | | 1 | 4 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| TAB1 | | 1 | 4 | Q1,00 | PCWP | |
実行計画(18c)
——————————————————————————————–
| Id | Operation | Name | | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
——————————————————————————————–
| 0 | SELECT STATEMENT | | | | | | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX PARTITION RANGE ALL| | | 1 | 4 | Q1,00 | PCWC | |
| 4 | WINDOW SORT | | | | | Q1,00 | PCWP | |
| 5 | TABLE ACCESS FULL | TAB1 | | 1 | 4 | Q1,00 | PCWP | |
2. E-RowsとA-Rows
ここでは、実行計画のE-Rows(Estimate Rows)とA-Rows(Actual Rows)の比較方法について説明します。
第27回でオプティマイザ統計の精度を確認するには、実行計画のE-RowsとA-Rowsを比較してくださいと説明しましたが、単純に比較できない場合があることを触れていませんでした。E-RowsとA-Rowsは、以下のように値の意味が少し異なるので、単純に比較できない場合があります。
- E-Rows（1回の操作で処理される見積り行数）
- A-Rows（その操作で処理されたすべての行数）
そのため、E-Rows×StartsとA-Rowsを比較する必要があるので注意してください（Startsは、その操作が実行された回数で、SQL監視ではExecsになります）。
もう少し分かりやすいように、いくつかの例を使用して説明します。まずは、ネステッド・ループ結合の場合です。以下のようにネステッド・ループ結合は、駆動表の行数だけ内部表のアクセスが実行されるので、内部表のStartsにアクセした回数（駆動表のA-Rows）、E-Rowsに1回の見積り行数が出力されます（この例では、表tab01のStartsが100、E-Rowsが1になっています）。そのため、E-Rows×StartsとA-Rowsを比較する必要があります。
——————————————————————————
| Id | Operation | Name | Starts | E-Rows | | A-Rows |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 100 |
| 1 | NESTED LOOPS | | 1 | 101 | | 100 |
| 2 | NESTED LOOPS | | 1 | 101 | | 100 |
|* 3 | TABLE ACCESS FULL | TAB02 | 1 | 101 | | 100 |
|* 4 | INDEX UNIQUE SCAN | PK_TAB01 | 100 | 1 | | 100 |
| 5 | TABLE ACCESS BY INDEX ROWID| TAB01 | 100 | 1 | | 100 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
3 – storage(“T2”.”C2″<1)
filter(“T2”.”C2″<1)
4 – access(“T1″.”C1″=”T2”.”C1″)
パラレル実行のときは、Startsに実行したPXプロセスの数、E-Rowsにすべての見積り行数が出力されるので、比較でStartsを使用する必要はありません。ただし、シリアル実行でStartsが1以外になる処理は、以下のネステッド・ループ結合のように、パラレル実行でも同じように比較する必要があります。
2 WHERE t1.c1=t2.c1 AND t2.c2 < 1;
————————————————————————————————————-
| Id | Operation | Name | Starts | E-Rows | | TQ |IN-OUT| PQ Distrib | A-Rows |
————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | | | | | 100 |
| 1 | PX COORDINATOR | | 1 | | | | | | 100 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 101 | | Q1,00 | P->S | QC (RAND) | 0 |
| 3 | NESTED LOOPS | | 2 | 101 | | Q1,00 | PCWP | | 100 |
| 4 | NESTED LOOPS | | 2 | 101 | | Q1,00 | PCWP | | 100 |
| 5 | PX BLOCK ITERATOR | | 2 | | | Q1,00 | PCWC | | 100 |
|* 6 | TABLE ACCESS STORAGE FULL| TAB02 | 28 | 101 | | Q1,00 | PCWP | | 100 |
|* 7 | INDEX UNIQUE SCAN | PK_TAB01 | 100 | 1 | | Q1,00 | PCWP | | 100 |
| 8 | TABLE ACCESS BY INDEX ROWID| TAB01 | 100 | 1 | | Q1,00 | PCWP | | 100 |
また、BROADCASTやデータ分散なし（Small Table Replicate）は、A-Rowsが実際の行数×パラレル度になります（以下の例は、パラレル度が4のデータ分散なしになるので、10000×4=40000になっています）。これは第39回で説明したように、対象データのすべてを、各PXプロセスで処理するからです。正しく比較するには、A-Rows÷パラレル度とE-Rowsを比較することになります。
2 WHERE t1.c1=t2.c2 AND t2.c3 < 10;
—————————————————————————————————
| Id | Operation | Name | Starts | E-Rows || TQ |IN-OUT| PQ Distrib | A-Rows |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | || | | | 9000 |
| 1 | PX COORDINATOR | | 1 | || | | | 9000 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 10000 || Q1,00 | P->S | QC (RAND) | 0 |
|* 3 | HASH JOIN | | 4 | 10000 || Q1,00 | PCWP | | 9000 |
|* 4 | TABLE ACCESS FULL | TAB02 | 4 | 10000 || Q1,00 | PCWP | | 40000 |
| 5 | PX BLOCK ITERATOR | | 4 | 1000K|| Q1,00 | PCWC | | 1000K|
|* 6 | TABLE ACCESS FULL| TAB01 | 52 | 1000K|| Q1,00 | PCWP | | 1000K|
BROADCASTのときは、PX RECEIVEのA-Rowsが行数×パラレル度になります（以下の例は、パラレル度が2になるので、10×2=20になっています）。
2 FROM (SELECT col1,MAX(id),MAX(col2) FROM t1 GROUP BY col1) A, t2 WHERE A.col1 = t2.col3;
———————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows | | TQ |IN-OUT| PQ Distrib | A-Rows |
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | | | | | 9 |
| 1 | PX COORDINATOR | | 1 | | | | | | 9 |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 9 | | Q1,02 | P->S | QC (RAND) | 0 |
|* 3 | HASH JOIN | | 2 | 9 | | Q1,02 | PCWP | | 9 |
| 4 | PX RECEIVE | | 2 | 10 | | Q1,02 | PCWP | | 20 |
| 5 | PX SEND BROADCAST | :TQ10001 | 0 | 10 | | Q1,01 | P->P | BROADCAST | 0 |
| 6 | VIEW | | 2 | 10 | | Q1,01 | PCWP | | 10 |
| 7 | HASH GROUP BY | | 2 | 10 | | Q1,01 | PCWP | | 10 |
| 8 | PX RECEIVE | | 2 | 10 | | Q1,01 | PCWP | | 20 |
| 9 | PX SEND HASH | :TQ10000 | 0 | 10 | | Q1,00 | P->P | HASH | 0 |
| 10 | HASH GROUP BY | | 2 | 10 | | Q1,00 | PCWP | | 20 |
| 11 | PX BLOCK ITERATOR | | 2 | 10000 | | Q1,00 | PCWC | | 10000 |
|* 12 | TABLE ACCESS FULL| TAB02 | 28 | 10000 | | Q1,00 | PCWP | | 10000 |
| 13 | PX BLOCK ITERATOR | | 2 | 100K| | Q1,02 | PCWC | | 100K|
|* 14 | TABLE ACCESS FULL | TAB01 | 26 | 100K| | Q1,02 | PCWP | | 100K|
このように実行計画によって比較する方法が異なるので注意してください。
3. おわりに
今回はTEMP領域の続きとA-Rowsについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。