実行計画の読み方(Oracle) - 闘うITエンジニアの覚え書き

SQL*PLUSを起動し、以下のコマンドを発行する。
※実行計画、実行統計が中途半端な位置で折り返されないようにしておく。

テーブル結合を行わない場合の実行計画は、以下の通り表示される。

Operation 列で一番右側にインデントされている行が一番最初にアクセスされているデータとなる。
つまり、上図の場合は Id = 2 の行 が最初に行われている事になる。

この実行計画は、以下の事を表している。

(A) emp表の索引（EMP_PK）を使って対象レコード(EMP_ID=5のレコード)のROWIDを取得する。
　※上図 Id = 2 の行。

(B) 上記(A)で索引から取得した ROWID から実際のEMP表のデータを取得する。
　※上図の Id = 1 の行。

EMP表のIDが5のデータを取得する。(10万件中の1件)

この実行計画は、以下の事を表している。

駆動表(外部表) は EMP表 である。

(A) まず、emp表の索引を使ってEMP表の対象レコード(EMP_ID=5のレコード)のROWIDが取得される。
　※上図 Id = 3 の行。

(B) 上記(A)で索引から取得した ROWIDから、EMP表の実際のデータを取得する。
　※上図の Id = 2 の行。

(C) 上記(A,B)で取得したEMP表のDEPT_IDを使用して、DEPT表の索引からDEPT表の対象データのROWIDが取得される。
　※上図の Id = 5 の行。

(E) 次に、上記までに取得したDEPT表のROWIDを使用して、DEPT表の実際のデータを取得する。
　※上図の Id = 4 の行。
(F) 取得したDEPT表のデータと、上記までに取得したデータとの結合(NESTED LOOP結合)が行われる。
　※上図の Id = 1 の行。

EMP表のIDが90000より大きいデータを取得する。(10万件中の1万件)

この実行計画は、以下の事を表している。

駆動表(外部表) は DEPT表 である。
※ハッシュ結合ではデータの少ない方が、メモリ上に展開される。

(A) DEPT表が全表検索(フルスキャン)され、結合条件列(DEPT_ID)にハッシュ関数をかけた後、メモリ上にハッシュテーブルが作成される。
　※上図 Id = 2 の行。

(B) EMP表の索引からEMP表の対象データ(EMP_ID > 90000のレコード)のROWIDが取得される。
　※上図 Id = 4 の行。

(C) 上記(B)で取得したEMP表のROWIDから、EMP表の実際のデータが取得される。
　※上図 Id = 3 の行。

(D) 上記(A)と(C)で取得したデータの結合が行われる。
　※上図 Id = 1 の行。

　もう少し詳細に書くと、
　上記(C)で取得したデータのDEPT_IDにハッシュ関数がかけられ、結合できるかを
　メモリ上のハッシュテーブルで確認し、ハッシュ値が等しいレコードを結合する。

上記の ハッシュ結合のケースでは、DEPT表の索引が使用されていない。
これは、オプティマイザによって、索引を使用するよりDEPT表をフルスキャンした方がコストが低いと判断された為である。

ここでは、オプティマイザヒントを使用して、上記のケースを無理矢理、索引を使用したネステッドループ結合した場合に
実行計画と実行統計がどのように変化するのかを見てみる。

まず、オプティマイザが算出したコストを見ると、100倍以上に増えている事が分かる。
統計の consistent gets の値も8倍以上も増えている。
※consistent gets は読み取り一貫性モードで読み取られたブロック数を表している。

ここでは、結合条件を指定するヒント句でアクセスパスを制御したが、ルールベースで動作させている時にも、これと同じ状況になる事がある。
※ちなみに上記のSQLに RULE ヒントを付けるか、DBMS_STATS.DELETE_TABLE_STATS で統計情報を削除してから実行すると、同じ結果になる。