はじめに
この記事は NTTテクノクロス Advent Calendar 2022 の１０日目の記事です。
こんにちはＮＴＴテクノクロスで最近はＤＢ関連のお仕事をしている田島です。
Oracle実行計画の見方について
性能問題が発生したときに実行計画を見るのですが、私にとってはわかりにくいフォーマットになっているので、見方を整理してみます。
欲しい情報とは
以下の情報が順序良く並んでいると見やすいと思いますので、その観点で説明していこうと思います。
- 実行順
- アクセス方法（インデックスを使用しているのか等）
- 検索条件
- 結合方式
環境
今回使用したテーブル構造
create table tbl1(id number,data varchar2(30), constraint tbl1_pk primary key(id));
create table tbl2(id number,id2 number,id3 number,data varchar2(30), constraint tbl2_pk primary key(id,id2));
create table tbl3(id number,data varchar2(30), constraint tbl3_pk primary key(id));
create table tbl4(id number,id2 number,id3 number,data varchar2(30), constraint tbl4_pk primary key(id,id2));
SQL
実行するSQL
select
t1.id,t1.data,
t2.id2,t2.data,
t3.id,t3.data,
t4.id2,t4.data
from tbl1 t1
left join tbl2 t2 on t1.id=t2.id
left join tbl3 t3 on t2.id3=t3.id
left join tbl4 t4 on t3.id=t4.id
where t1.id = 1
and t3.data='aaa';
実行計画
実行計画のみ取得するには以下のようにexplain plan forを対象SQLの前につけて実行し、実行後にselect * from table(dbms_xplan.display());を発行して実行計画を表示させます。
細かい指定方法等はマニュアルを見てください。
explain plan for
対象SQL;
select * from table(dbms_xplan.display());
今回の対象となる実行計画
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2090955529
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 159 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 116 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 116 | 2 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 86 | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | TBL1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | TBL1_PK | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL2 | 1 | 56 | 0 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | TBL2_PK | 1 | | 0 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | TBL3_PK | 1 | | 0 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | TBL3 | 1 | 30 | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | TBL4 | 1 | 43 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."ID"="T4"."ID"(+))
6 - access("T1"."ID"=1)
8 - access("T2"."ID"=1)
9 - access("T2"."ID3"="T3"."ID")
10 - filter("T3"."DATA"='aaa')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
32行が選択されました。
実行順
実行順を説明する前にOperationの段下げ(空白)の数＋１をlevelと定義します。（内部的にplan_tableをconnectで接続しており、その疑似列のlevelとなります。）
例えばId=0の場合がlevel1でId=6,8がlevel7となります。
説明しやすいようにlevelと順番を付加した実行計画の一部を以下に示しています。
------------------------------------------------------------
| Id | Operation |level|順番|
------------------------------------------------------------
| 0 | SELECT STATEMENT | 1 | 12 |
|* 1 | HASH JOIN OUTER | 2 | 11 |
| 2 | NESTED LOOPS | 3 | 9 |
| 3 | NESTED LOOPS | 4 | 7 |
| 4 | NESTED LOOPS | 5 | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID | 6 | 2 |
|* 6 | INDEX UNIQUE SCAN | 7 | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| 6 | 4 |
|* 8 | INDEX RANGE SCAN | 7 | 3 |
|* 9 | INDEX UNIQUE SCAN | 5 | 6 |
|* 10 | TABLE ACCESS BY INDEX ROWID | 4 | 8 |
| 11 | TABLE ACCESS FULL | 3 | 10 |
------------------------------------------------------------
実行順のルール
- Id=0から実行
- 自分のIdの次が自分のlevelより大きければそちらを先に実行し、levelの大きいものが実行済みであれば自分を実行
- 実行後自分と同じlevelでそのlevelより小さいものがなければそのIdを実行し、なければひとつlevelを小さくしたところに戻る
Idが0～6まではlevelが上がっていくのでId=6を最初に実行します。
その後自分と同じlevelのId=8がいますが、Id=7が自分より小さいレベルのため、戻ってId5を実行します。
Id=5実行後同じlevelのId7がいますが、それを実行するにはそれより大きなlevelのId=8がいるためそちらを先に実行し、戻ってId=7を実行。
あとは同様に実行順を見ていきます。
Idと実行順の並びが異なっているのと、段下げは途中に入る行数が大きくなるとどのレベルか、ぱっと見わかりづらいですね。
アクセス方法（インデックスを使用しているのか等）
実行順が分かればインデックスを使用しているのかはNameで確認できます。
ただし、主キーなどで名前を指定しないとSYSから始まる名前に設定されてしまうため、見づらくなります。（例：SYS_C007604）
今回の実行計画ではtbl4以外は主キーを使用してアクセスされています。
----------------------------------------------------------------
| Id | Operation | Name |順番|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 |
|* 1 | HASH JOIN OUTER | | 11 |
| 2 | NESTED LOOPS | | 9 |
| 3 | NESTED LOOPS | | 7 |
| 4 | NESTED LOOPS | | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID | TBL1 | 2 |
|* 6 | INDEX UNIQUE SCAN | TBL1_PK | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL2 | 4 |
|* 8 | INDEX RANGE SCAN | TBL2_PK | 3 |
|* 9 | INDEX UNIQUE SCAN | TBL3_PK | 6 |
|* 10 | TABLE ACCESS BY INDEX ROWID | TBL3 | 8 |
| 11 | TABLE ACCESS FULL | TBL4 | 10 |
----------------------------------------------------------------
検索条件
以下の部分でIdに対応する検索条件が確認できます。
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."ID"="T4"."ID"(+))
6 - access("T1"."ID"=1)
8 - access("T2"."ID"=1)
9 - access("T2"."ID3"="T3"."ID")
10 - filter("T3"."DATA"='aaa')
Id部分に*がついているものに対応しており、横に並べると以下のようになります。
----------------------------------------------------------------
| Id | Operation | Name |順番|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 |
|* 1 | HASH JOIN OUTER | | 11 |access("T3"."ID"="T4"."ID"(+))
| 2 | NESTED LOOPS | | 9 |
| 3 | NESTED LOOPS | | 7 |
| 4 | NESTED LOOPS | | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID | TBL1 | 2 |
|* 6 | INDEX UNIQUE SCAN | TBL1_PK | 1 |access("T1"."ID"=1)
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL2 | 4 |
|* 8 | INDEX RANGE SCAN | TBL2_PK | 3 |access("T2"."ID"=1)
|* 9 | INDEX UNIQUE SCAN | TBL3_PK | 6 |access("T2"."ID3"="T3"."ID")
|* 10 | TABLE ACCESS BY INDEX ROWID | TBL3 | 8 |filter("T3"."DATA"='aaa')
| 11 | TABLE ACCESS FULL | TBL4 | 10 |
----------------------------------------------------------------
結合方式
テーブルとテーブルか結合結果とテーブルを結合する方式が大きく3つあります。詳細はマニュアルで確認してみてください。
- ネステッド・ループ結合
- ハッシュ結合
- ソート/マージ結合
今回はIdが2,3,4でネステッド・ループ結合が使用され、Id=1でハッシュ結合が使用されてます。
まとめ
全体をまとめると以下のようになります。インデックスからテーブルへのアクセスはテーブルに含めています。
実行順、アクセス方法、検索条件、結合方式がわかりやすくなったと思います。
おわりに
今回は実行計画の見方について整理してみました。
SQLチューニングを実施する場合にはまず、現在の動作を実行計画で確認してからチューニング箇所を特定する必要があります。このため実行計画の見方を理解しておきたいですね。
それでは引き続きNTTテクノクロス Advent Calendar 2022の１１日目もお楽しみください。