[[Oracleメモ]] > 実行計画の読み方(Oracle)
#setlinebreak(on)
* 実行計画の読み方(Oracle) [#h51118ce]
#html(<div style="margin-left:20px;">)
【関連】
・[[表結合の基礎知識]]
例として、従業員マスタ(EMP)、部門マスタ(DEPT)からデータを検索する場合の、実行計画の読み方を記載する。
また、従業員マスタには 10万件、部門マスタには 100件のデータがあり、従業員マスタには emp_id に
部門マスタには dept_id にそれぞれ索引がある状態とする。
&size(16){0.準備};
#html(<div style="padding:0px;margin:0px 0px 0px 20px;">)
SQL*PLUSを起動し、以下のコマンドを発行する。
※実行計画、実行統計が中途半端な位置で折り返されないようにしておく。
#html(<div style="margin-left:10px;background:#000000;color:#ffffff;padding:2px 10px;width:500px;">)
set linesize 1000
column plan_plus_exp format a200
set autotrace traceonly
#html(</div>)
#html(</div>)
&size(16){1.テーブル結合がない場合 - 索引を使用して任意の1件を取得する場合};
#html(<div style="padding:0px;margin:0px 0px 0px 20px;">)
テーブル結合を行わない場合の実行計画は、以下の通り表示される。
#html(<div style="padding:0px;margin:10px 0px 0px -25px;">)
#ref(no_join.png,nolink)
#html(</div>)
Operation 列で一番右側にインデントされている行が一番最初にアクセスされているデータとなる。
つまり、上図の場合は Id = 2 の行 が最初に行われている事になる。
この実行計画は、以下の事を表している。
(A) emp表の索引（EMP_PK）を使って対象レコード(EMP_ID=5のレコード)のROWIDを取得する。
※上図 Id = 2 の行。
#html(<div style="padding:0px;margin:10px 0px 0px 0px;">)
#ref(no_join_01.png,nolink)
#html(</div>)
(B) 上記(A)で索引から取得した ROWID から実際のEMP表のデータを取得する。
※上図の Id = 1 の行。
#html(<div style="padding:0px;margin:10px 0px 0px 0px;">)
#ref(no_join_02.png,nolink)
#html(</div>)
#html(</div>)
&size(16){2.テーブル結合がある場合 - 索引を使用したNESTED LOOP結合の場合};
#html(<div style="padding:0px;margin:0px 0px 0px 20px;">)
EMP表のIDが5のデータを取得する。(10万件中の1件)
#html(<div style="padding:0px;margin:10px 0px 0px -25px;">)
#ref(join.png,nolink)
#html(</div>)
この実行計画は、以下の事を表している。
駆動表(外部表) は EMP表 である。
(A) まず、emp表の索引を使ってEMP表の対象レコード(EMP_ID=5のレコード)のROWIDが取得される。
※上図 Id = 3 の行。
#html(<div style="padding:0px;margin:10px 0px 0px 0px;">)
#ref(join1_01.png,nolink)
#html(</div>)
(B) 上記(A)で索引から取得した ROWIDから、EMP表の実際のデータを取得する。
※上図の Id = 2 の行。
#html(<div style="padding:0px;margin:10px 0px 0px 0px;">)
#ref(join1_02.png,nolink)
#html(</div>)
(C) 上記(A,B)で取得したEMP表のDEPT_IDを使用して、DEPT表の索引からDEPT表の対象データのROWIDが取得される。
※上図の Id = 5 の行。
#html(<div style="padding:0px;margin:10px 0px 0px 0px;">)
#ref(join1_03.png,nolink)
#html(</div>)
(E) 次に、上記までに取得したDEPT表のROWIDを使用して、DEPT表の実際のデータを取得する。
※上図の Id = 4 の行。
(F) 取得したDEPT表のデータと、上記までに取得したデータとの結合(NESTED LOOP結合)が行われる。
※上図の Id = 1 の行。
#html(<div style="padding:0px;margin:10px 0px 0px 0px;">)
#ref(join1_05.png,nolink)
#html(</div>)
#html(</div>)
&size(16){3.テーブル結合がある場合 - ハッシュ結合の場合};
#html(<div style="padding:0px;margin:0px 0px 0px 20px;">)
EMP表のIDが90000より大きいデータを取得する。(10万件中の1万件)
#html(<div style="padding:0px;margin:10px 0px 0px -25px;">)
#ref(join2.png,nolink)
#html(</div>)
この実行計画は、以下の事を表している。
駆動表(外部表) は 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にハッシュ関数がかけられ、結合できるかを
メモリ上のハッシュテーブルで確認し、ハッシュ値が等しいレコードを結合する。
#html(</div>)
&size(16){3-X.もしハッシュ結合されるケースを無理やり索引スキャンしたら};
#html(<div style="padding:0px;margin:0px 0px 0px 20px;">)
上記の ハッシュ結合のケースでは、DEPT表の索引が使用されていない。
これは、オプティマイザによって、索引を使用するよりDEPT表をフルスキャンした方がコストが低いと判断された為である。
ここでは、オプティマイザヒントを使用して、上記のケースを無理矢理、索引を使用したネステッドループ結合した場合に
実行計画と実行統計がどのように変化するのかを見てみる。
#html(<div style="padding:0px;margin:10px 0px 0px -25px;">)
#ref(join2_X.png,nolink)
#html(</div>)
まず、オプティマイザが算出したコストを見ると、100倍以上に増えている事が分かる。
統計の consistent gets の値も8倍以上も増えている。
※consistent gets は読み取り一貫性モードで読み取られたブロック数を表している。
ここでは、結合条件を指定するヒント句でアクセスパスを制御したが、ルールベースで動作させている時にも、これと同じ状況になる事がある。
※ちなみに上記のSQLに RULE ヒントを付けるか、DBMS_STATS.DELETE_TABLE_STATS で統計情報を削除してから実行すると、同じ結果になる。
#html(</div>)
#html(</div>)
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
