実行計画の解析方法(1)www.doppo1.net実行計画の解析方法(1)本ページでは結合を含まない単一テーブルに対しての実行計画の解析方法を記載します。基本的にはインデントの深いものから順番に実行される。・テーブルの結合(ネステッドループ結合、ソートマージ結合、ハッシュ結合、直積結合)は一テーブルずつ結合される。・実行計画を立てる際にORACLEはパフォーマンスがよい実行計画が立てられるように内部的にSQL文を書き換える場合がある。どのように書き換えられたかを通常の方法では確認することはできないがヒントや初期化パラメータにより一部制御することができるものがある。・コストベースで実行計画が立てられた場合該当オペレーションに対する行数(rows)、処理時間等(Time)が表示されるがこの値は統計情報から導き出した予測のため正しいとは限らない。(SQLが複雑になるほど見積もりとのかい離が大きくなる)・実行計画上のコストが小さいにもかかわらず実際の処理時間が長い場合は実行計画が最善であるか見直したほうがよい。本ページで記載されているSQLは全て以下のSQLで作成されたテーブルとインデックスを使用しています。DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST',TABNAME=>'AAA',CASCADE=>TRUE);統計情報は以下の通りで実データと合致した値となっています。テーブルフルスキャンこの実行計画はAAAというテーブルに対してテーブルフルスキャンを実行する実行計画です。STATEMENT」はSELECT文を実行していることを表しているだけで実行計画的な意味はありません。FULL」の記載と、Name=AAAの記載からAAAというテーブルをフルスキャンしていることが読み取れます。上述の通りTimeはCBOの見積もりであって実測値ではありません。見積もりは統計情報が正確でもSQLが複雑になるほど精度が落ちるので注意してください。Id=1のOperationは一段落下がっていますが、実行計画は段落が深いオペレーションから実施されるため以下の例は1→0の順番に実行されることになります。インデックスユニークスキャンこの実行計画は以下のように処理します。1PK_AAAを使用したインデックスユニークスキャンによりCOL1=1の行のrowidを取得する(id=2の箇所)。(プライマリキーに対応したインデックスでも明示的にノンユニークインデックスで作成した場合はインデックスレンジスキャンになる)。Informationからは以下の情報が読み取れます。InformationがあるオペレーションはIdの左に「*」がつきます。21で取得したrowidに対応するブロックを読み込み、その中にあるCOL1=1の行データを読み込む。(id=1の箇所)rowidはその行に対するポインタのようなものでrowidが分かればダイレクトに該当データブロックの該当行にアクセスすることができます。ORACLEでは表や索引のI/O最小単位はブロックのため数バイトの1行のデータだけ読み込む場合であっても必ずブロックサイズ分のI/Oが発生します。インデックスファストフルスキャンこの実行計画は以下のように処理します。1インデックスファストフルスキャンによりPK_AAAの全ブロック読み込む(Id=2)。索引セグメントをマルチブロックリードにより全ブロックを読み込むオペレーションです。表ブロックを読まずに索引ブロックだけ読み込めば結果を返すことが可能です。索引は表よりサイズが小さいため表のフルスキャンよりも索引のファストフルインデックススキャンのほうがI/Oが少なくなり処理時間が短縮されるためこの実行計画が選択されています。AGGREGATE」はSUM()やCOUNT()等の集計関数で集計する際に選択されるオペレーションです。このSQLの場合はCOUNT(*)の結果を集計しています。1で抽出される予想行数(Rows)は1万件でしたが、2で件数の集計をかけるため行数は1行になっています。インデックスレンジスキャンこの実行計画は以下のように処理します。1PK_AAAをインデックスレンジスキャンによりCOL1>9500に該当するブロック読み込む(Id=3)。索引のデータ構造はソートされているためCOL1>9500のデータを含まないリーフブロックを読み込むことはありません。Rows=500となっておりこのケースではオプティマイザの見積もりは正しいことがわかります。21で抽出したrowidからテーブルへのアクセスおよびフィルタ処理(Id=2)Id=3のリーフブロックから取得した500個のrowidを元に該当のテーブルブロックへ500回アクセスします。見積もりは正しいとは限らないためレンジスキャンのRowsの値が実際のテーブルアクセス回数と一致するとは限りません。実際に何回アクセスしたのかを確認するにはsql_traceかstatistics_lebel=allを設定することで行ソースレベルの実統計を確認します。Id=2のRowsは5で実際は100であるため正しくありません。(10000-9900)/10000で1/100になるという予測から500行が5秒になるという判断をしています。このように抽出条件が増えるたび見積もりが実態とかい離していく可能性が高いことに注意する必要があります。11g以降では列相関統計という機能により複数列の条件でもより正確な見積もりを算出する機能が追加されています。本サイトはリンクフリーです。本サイトの情報により発生した問題等については一切責任を負えません。管理人への連絡はftpsaba0123@hotmail.comまでどうぞ。