実行計画の解析方法(1)
本ページでは結合を含まない単一テーブルに対しての実行計画の解析方法を記載します。関連ページ：実行計画の解析方法(2)
実行計画を読む上での前提知識
・実行計画は基本的にはインデントの深いものから順番に実行される。・テーブルの結合(ネステッドループ結合、ソートマージ結合、ハッシュ結合、直積結合)は一テーブルずつ結合される。
・実行計画を立てる際にORACLEはパフォーマンスがよい実行計画が立てられるように内部的にSQL文を書き換える場合がある。(ビューのマージやネストの解除、where条件の補完等) どのように書き換えられたかを通常の方法では確認することはできないがヒントや初期化パラメータにより一部制御することができるものがある。
・コストベースで実行計画が立てられた場合該当オペレーションに対する行数(rows)、処理時間等(Time)が表示されるがこの値は統計情報から導き出した予測のため正しいとは限らない。 (SQLが複雑になるほど見積もりとのかい離が大きくなる)
・実行計画上のコストが小さいにもかかわらず実際の処理時間が長い場合は実行計画が最善であるか見直したほうがよい。
本ページで使用した検証用データの定義
本ページで記載されているSQLは全て以下のSQLで作成されたテーブルとインデックスを使用しています。CREATE TABLE AAA (COL1 NUMBER ,COL2 NUMBER); ALTER TABLE AAA ADD CONSTRAINT PK_AAA PRIMARY KEY(COL1); BEGIN FOR I IN 1..10000 LOOP INSERT INTO AAA VALUES(I,I); COMMIT; END LOOP; END; / EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST',TABNAME=>'AAA',CASCADE=>TRUE);統計情報は以下の通りで実データと合致した値となっています。
select table_name,blocks ,num_rows from user_tables where table_name = 'AAA'; TABLE_NAME BLOCKS NUM_ROWS ---------- ---------- ---------- AAA 20 10000 select table_name,NUM_ROWS,BLEVEL ,LEAF_BLOCKS, DISTINCT_KEYS ,AVG_LEAF_BLOCKS_PER_KEY , AVG_DATA_BLOCKS_PER_KEY ,CLUSTERING_FACTOR from user_indexes where table_name = 'AAA'; TABLE_NAME NUM_ROWS BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR ---------- ---------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- AAA 10000 1 18 10000 1 1 18 select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT, utl_raw.CAST_TO_NUMBER(LOW_VALUE) LOW_VALUE,utl_raw.CAST_TO_NUMBER(HIGH_VALUE) HIGH_VALUE, DENSITY,NUM_NULLS,AVG_COL_LEN from user_tab_columns where table_name = 'AAA'; TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS AVG_COL_LEN ---------- --------------- ------------ ---------- ---------- ---------- ---------- ----------- AAA COL1 10000 1 10000 .0001 0 4 AAA COL2 10000 1 10000 .0001 0 4
例1 テーブルフルスキャン
この実行計画はAAAというテーブルに対してテーブルフルスキャンを実行する実行計画です。 Id=0の「SELECT STATEMENT」はSELECT文を実行していることを表しているだけで実行計画的な意味はありません。 Id=1は「TABLE ACCESS FULL」の記載と、Name=AAAの記載からAAAというテーブルをフルスキャンしていることが読み取れます。 上述の通りTimeはCBOの見積もりであって実測値ではありません。見積もりは統計情報が正確でもSQLが複雑になるほど精度が落ちるので注意してください。また、Id=1のOperationは一段落下がっていますが、実行計画は段落が深いオペレーションから実施されるため以下の例は1→0の順番に実行されることになります。
select * from aaa; 実行計画 ---------------------------------------------------------- Plan hash value: 864433273 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 80000 | 8 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| AAA | 10000 | 80000 | 8 (0)| 00:00:01 | --------------------------------------------------------------------------
例2 インデックスユニークスキャン
この実行計画は以下のように処理します。①PK_AAAを使用したインデックスユニークスキャンによりCOL1=1の行のrowidを取得する(id=2の箇所)。
PK_AAAはプライマリキーに対応したユニークインデックスのためインデックスレンジスキャンではなくインデックスユニークスキャンになります (プライマリキーに対応したインデックスでも明示的にノンユニークインデックスで作成した場合はインデックスレンジスキャンになる)。 また、下にあるPredicate Informationからは以下の情報が読み取れます。
・col1=1の条件評価は「access」であるため索引を利用して条件に一致する索引ブロックを無駄なブロックアクセスなく読み込んでいる(索引ブランチからたどり、col1=1のデータが入っているリーフブロックのみ読み込んでいる)
・col2=1の条件評価は「filter」であるためいったん表ブロックを読み込んだ後、条件に該当する行があるかどうか判断し、 条件に合致しない行であれば切り捨てて(フィルタして)いる(=該当の条件に一致しない無駄なブロックも読み込んでいる可能性がある)
なお、Predicate InformationがあるオペレーションはIdの左に「*」がつきます。
②①で取得したrowidに対応するブロックを読み込み、その中にあるCOL1=1の行データを読み込む。(id=1の箇所)
rowidはその行に対するポインタのようなものでrowidが分かればダイレクトに該当データブロックの該当行にアクセスすることができます。 ただし、ORACLEでは表や索引のI/O最小単位はブロックのため数バイトの1行のデータだけ読み込む場合であっても必ずブロックサイズ分のI/Oが発生します。
select * from aaa where col1 = 1 and col2 = 1;
実行計画
----------------------------------------------------------
Plan hash value: 3774071719
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| AAA | 1 | 8 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_AAA | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COL2"=1)
2 - access("COL1"=1)
例3 インデックスファストフルスキャン
この実行計画は以下のように処理します。①インデックスファストフルスキャンによりPK_AAAの全ブロック読み込む(Id=2)。
インデックスファストフルスキャンとは通常のインデックスレンジスキャンのようにBツリー構造をたどりリーフブロック順(索引キー順)にアクセスするのではなく、 索引セグメントをマルチブロックリードにより全ブロックを読み込むオペレーションです。 このSQLはaaa表の件数を返すSQLでありPK_AAAはプライマリキーの索引である(NULLデータがないことが保障される)ため 表ブロックを読まずに索引ブロックだけ読み込めば結果を返すことが可能です。 索引は表よりサイズが小さいため表のフルスキャンよりも索引のファストフルインデックススキャンのほうがI/Oが少なくなり処理時間が短縮されるためこの実行計画が選択されています。
②①で抽出した件数をカウントする(Id=1)
「SORT AGGREGATE」はSUM()やCOUNT()等の集計関数で集計する際に選択されるオペレーションです。このSQLの場合はCOUNT(*)の結果を集計しています。 ①で抽出される予想行数(Rows)は1万件でしたが、②で件数の集計をかけるため行数は1行になっています。
select count(*) from aaa; 実行計画 ---------------------------------------------------------- Plan hash value: 1020877103 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| PK_AAA | 10000 | 7 (0)| 00:00:01 | ------------------------------------------------------------------------
例4 インデックスレンジスキャン
この実行計画は以下のように処理します。①PK_AAAをインデックスレンジスキャンによりCOL1＞9500に該当するブロック読み込む(Id=3)。
索引のデータ構造はソートされているためCOL1＞9500のデータを含まないリーフブロックを読み込むことはありません。 Rows=500となっておりこのケースではオプティマイザの見積もりは正しいことがわかります。
②①で抽出したrowidからテーブルへのアクセスおよびフィルタ処理(Id=2)
Id=3のリーフブロックから取得した500個のrowidを元に該当のテーブルブロックへ500回アクセスします。 この例ではId=3のRows=500の見積もりが正しいため実際のテーブルブロックへのアクセス数と一致しますが、 見積もりは正しいとは限らないためレンジスキャンのRowsの値が実際のテーブルアクセス回数と一致するとは限りません。 実際に何回アクセスしたのかを確認するにはsql_traceかstatistics_lebel=allを設定することで行ソースレベルの実統計を確認します。
Id=2のRowsは5で実際は100であるため正しくありません。 この見積もりは索引から取得した500行に対してCOL2＞9900)の条件で絞り込むと (10000-9900)/10000で1/100になるという予測から500行が5秒になるという判断をしています。 このように抽出条件が増えるたび見積もりが実態とかい離していく可能性が高いことに注意する必要があります。 なお、11g以降では列相関統計という機能により複数列の条件でもより正確な見積もりを算出する機能が追加されています。
select count(*) from aaa where col1 > 9500 and col2 > 9900;
実行計画
----------------------------------------------------------
Plan hash value: 518189053
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| AAA | 5 | 40 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PK_AAA | 500 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL2">9900)
3 - access("COL1">9500)
折りたたみ
展開