前回のエントリで
DBMS_XPLAN.DISPLAY_CURSOR の format引数に’TYPICAL’と
‘ALL ALLSTATS LAST’ を主に指定すると説明しました。
本エントリでは、format引数 ‘ALL ALLSTATS LAST’の機能を説明し、
その一例として、CBOの見積りミスを簡単に確認することができることを説明したいと思います。
### format引数 ‘ALL ALLSTATS LAST’
format引数 ‘ALL ALLSTATS LAST’を指定すると、
実行計画ツリーを示す PLAN_TABLE_OUTPUT、
フィルタ条件やアクセス情報を示す Predicate Informationに加えて、
複雑なSQLを分割したクエリブロック情報や表のエイリアス情報を示すQuery Block Name / Object Alias、
アクセスした列に関する情報を示すColumn Projection Information が表示されます。
また、PLAN_TABLE_OUTPUTの列に、オペレーション単位の各種実行統計が追加されます。
format引数’TYPICAL’を指定した場合は、確認できるのはCBOによって見積もられた見積統計
(見積もり行数、見積もりバイトなど)しか確認できませんでしたが、
実行時の実行統計(実際にオペレーションを実行した結果得られた行数など）
が確認できます。
また、format引数 ‘LAST’が指定されていることで、
直近のSQL実行の統計情報を表示します。
逆に’LAST’を指定しないと、得られる統計情報が共有カーソル単位の累積値となってしま
い（同じSQLを複数回実行すると複数回実行分の合算値となる）、1回実行分の統計を確認したい
ような通常の用途では不適切であることに注意してください。
なお、前回のエントリで説明しましたが、format引数 ‘ALLSTAT’、’LAST’は
調査対象のSQLの実行前にstatistics_levelパラメータをALLに設定するか、
SQLに /*+ gather_plan_statistics */ ヒントを指定した場合でのみ有効となる
ことに注意してください。
### CBOの見積もりミスを確認する
このように、format引数 ‘ALL ALLSTATS LAST’ を指定したDBMS_XPLAN.DISPLAY_CURSOR
を用いて実行計画を取得すると、様々な情報が取得できます。
CBOが作成した実行計画が、本当に最適な実行計画であるか疑問がある場合には、
オペレーション単位で確認できる様々な統計の1つである見積行数(E-Rows)と
実行時行数(A-Rows)が非常に有効です。
CBOは、主にI/Oコストの観点から最適と思われる実行計画を作成します。
この際に参考にするのがオプティマイザ統計です。
しかし、オプティマイザ統計は、データの特性を集約したサマリ情報
（具体的には行の平均長や列値の最小値・最大値、ヒストグラムなど）に過ぎないため、
仮にオプティマイザ統計が最新の状態であっても、CBOのコスト予測が外れて最適な
実行計画が作成されない場合があります。
CBOの予測が外れたことを端的に示すのが、見積行数と実行時行数の差です。
たとえば、あるオペレーションの見積もり行数が10行だったにもかかわらず、
実行時行数が10000行だった場合は、そのオペレーションを実行した結果返される行数に
関する、CBOの予測が外れていることを示しているため、その予測に基づいて作成された
実行計画が最適でない可能性があります。
### 例) 列値に偏りがあるテーブル
では、CBOの予測が外れる典型的な状況について実際に実行計画を見てゆきましょう。
若干わざとらしい感じですが、上記のような分布のデータを例にとって説明します。
flg1 flg2
——- ——-
X A ↑
X B │
X C │
X A │
X B │1500件
X C │
: │
X A │
X B │
X C ↓
A X ↑
B X │
C X │
A X │
B X │1500件
C X │
: │
A X │
B X │
C X ↓
X X ←1件
このようなデータに対して、
SELECT sum(val1) FROM tbl0 WHERE flg1 = ‘X’ AND flg2 = ‘X’
というSQLを実行することを考えます。
通常のオプティマイザ統計収集方法(*1) は、このような列データの偏りを持つ
表に対して、ヒストグラムを収集したとしても、行数を正確に見積もることはできません。
ヒストグラムは単一列のデータの分布を踏まえた行数見積もりには有効ですが、
複数の列のデータを分布を踏まえた行数見積もりには有効ではないからです。
今回のように、列flg1と列flg2のデータの分布が独立ではない場合でも、
CBOは列flg1と列flg2のデータの分布が独立であると判断して行数を見積もります。
具体的には、上記のデータ分布においては、
flg1=’X’の分布 : 全体の約50%
flg2=’X’の分布 : 全体の約50%
であるため、条件 flg1 = ‘X’ AND flg2 = ‘X’ の選択率を 50% x 50% = 25% と算出し、
見積もり行数を3001 x 25 % ≒ 750行 とします。
(*1) Oracle Databae 11.1 より導入された拡張列統計を使用しない(デフォルト)の場合
### 見積もり行数と実際の行数を比較する
では実際に、CBOの見積もり行数と、実際の行数を比較するため、
SQLを実行後、その実行計画をformat引数 ‘ALL ALLSTATS LAST’ を指定した
DBMS_XPLAN.DISPLAY_CURSORで取得します。
SQL> SELECT sum(val1) FROM tbl0 WHERE flg1 = ‘X’ AND flg2 = ‘X’;
SUM(VAL1)
———-
1
SQL> select * from table(DBMS_XPLAN.display_cursor(’42fxsw7fcxuht’,0, ‘ALL ALLSTATS LAST’));
PLAN_TABLE_OUTPUT
—————————————————————————————————————————————————————-
SQL_ID 42fxsw7fcxuht, child number 0
————————————-
SELECT sum(val1) FROM tbl0 WHERE flg1 = ‘X’ AND flg2 = ‘X’
Plan hash value: 418409521
———————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
———————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 307 (100)| | 1 |00:00:00.01 | 1025 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.01 | 1025 |
|* 2 | TABLE ACCESS FULL| TBL0 | 1 | 750 | 5250 | 307 (0)| 00:00:04 | 1 |00:00:00.01 | 1025 |
———————————————————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / TBL0@SEL$1
Predicate Information (identified by operation id):
—————————————————
2 – filter((“FLG1″=’X’ AND “FLG2″=’X’))
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) SUM(“VAL1”)[22]
2 – “VAL1″[NUMBER,22]
31行が選択されました。
上記の実行計画で注目していただきたいのは、operation id = 2の TABLE ACCESS FULL
オペレーションにおけるE-RowsとA-Rowsです。
E-RowsはEstimated Rowsの略で、そのオペレーションを実行したとき得られるであろうと、
CBOによって予測された見積もり行数を示す統計値です。
また、A-RowsはActual Rowsの略で、そのオペレーションを実際に実行した結果、
得られた実行時行数を示す統計値です。
———————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
———————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 307 (100)| | 1 |00:00:00.01 | 1025 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.01 | 1025 |
|* 2 | TABLE ACCESS FULL| TBL0 | 1 | 750 | 5250 | 307 (0)| 00:00:04 | 1 |00:00:00.01 | 1025 |
^^^^^^^ ←★ ^^^^^^^←★
———————————————————————————————————————
すなわち、これはTABLE ACCESS FULLオペレーションを実行し、
flg1 = ‘X’ AND flg2 = ‘X’という条件に合致する行のみを抽出した処理を行うと、
得られる行数は750行(← E-Rows)であろうと、CBOは予測しているにもかかわらず、
実際に得られた行数は1行(←A-Rows)であることを示しています。
見積もり行数と実行時行数の差から、CBOによる行数見積もりが大幅に外れていることがわかります。
行数見積もりの予測ミスが、実行計画の作成ミスにつながっています。
CBOは見積行数が750行であることに基づき、TBL0へのアクセスパスとして
TABLE ACCESS FULLオペレーションを選択しましたが、
実は、この場合は、索引を使用したINDEX RANGE SCANオペレーションが適切でした。
#### あらためて DBMS_XPLAN.DISPLAY_CURSOR(format => ‘ALL ALLSTATS LAST’)のススメ
このように、
format引数 ‘ALL ALLSTATS LAST’ を指定したDBMS_XPLAN.DISPLAY_CURSOR
で実行計画を取得すると、簡単に実行時行数と見積行数の差異を確認でき、
ひいては、CBOの見積もりミスを確認できます。
見積もりミスは、CBOによる実行計画の選択ミスの可能性を示唆しているため、
より適切な実行計画を検討する材料とすることができます。
ちなみに、DBMS_XPLAN.DISPLAY_CURSORが存在しなかった9.2以前では、
EXPLAIN PLANまたはV$SQL_PLANで得られた予測行数を含む実行計画と、
SQLトレースから得られた実行時行数を含む実行計画を、あわせて確認することで、
実行時行数と見積行数の差異を確認していました(*2)。
これは、SQLの数が多い場合非常に面倒な作業でしたが、DBMS_XPLAN.DISPLAY_CURSOR
と使うと、作業負荷を大幅に削減できます。
(*2) 厳密には9.2ではDBMS_XPLAN.DISPLAY_CURSOR が内部的に参照している
V$SQL_PLAN_STATISTICS_ALLを直接確認することで、同様の作業が実現できます。
CBOの見積もりミスが疑われる状況においては、
format引数 ‘ALL ALLSTATS LAST’ を指定したDBMS_XPLAN.DISPLAY_CURSOR
で実行計画を取得することをお勧めします。
[…] « DISPLAY_CURSORでCBOの見積りミスを簡単に確認する […]