Oracle® 実行計画の見方について

# はじめに
この記事は [NTTテクノクロス Advent Calendar 2022](https://qiita.com/advent-calendar/2022/ntt-tx) の１０日目の記事です。

こんにちは[ＮＴＴテクノクロス](https://www.ntt-tx.co.jp/)で最近はＤＢ関連のお仕事をしている田島です。

# Oracle実行計画の見方について
性能問題が発生したときに実行計画を見るのですが、私にとってはわかりにくいフォーマットになっているので、見方を整理してみます。

## 欲しい情報とは

以下の情報が順序良く並んでいると見やすいと思いますので、その観点で説明していこうと思います。
1. 実行順
1. アクセス方法（インデックスを使用しているのか等）
1. 検索条件
1. 結合方式

### 環境
今回使用したテーブル構造
```SQL:table
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
```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());を発行して実行計画を表示させます。
細かい指定方法等はマニュアルを見てください。

```SQL:実行計画取得方法
explain plan for
対象SQL;
select * from table(dbms_xplan.display());
```
今回の対象となる実行計画

```SQL:実行計画
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と順番を付加した実行計画の一部を以下に示しています。

```SQL
------------------------------------------------------------
| 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 |
------------------------------------------------------------
```

実行順のルール
1. Id=0から実行
1. 自分のIdの次が自分のlevelより大きければそちらを先に実行し、levelの大きいものが実行済みであれば自分を実行
1. 実行後自分と同じ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以外は主キーを使用してアクセスされています。

```SQL
----------------------------------------------------------------
| 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に対応する検索条件が確認できます。

```SQL
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部分に*がついているものに対応しており、横に並べると以下のようになります。

```SQL

----------------------------------------------------------------
| 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つあります。詳細はマニュアルで確認してみてください。

1. ネステッド・ループ結合
1. ハッシュ結合
1. ソート/マージ結合

今回はIdが2,3,4でネステッド・ループ結合が使用され、Id=1でハッシュ結合が使用されてます。



### まとめ

全体をまとめると以下のようになります。インデックスからテーブルへのアクセスはテーブルに含めています。
実行順、アクセス方法、検索条件、結合方式がわかりやすくなったと思います。

```mermaid
graph TD;
    id1[tbl1:TBL1_PK:T1.ID=1] --> id3;
    id2[tbl2:TBL2_PK:T2.ID=1] --> id3;
    id3[NESTED LOOPS] --> id5;
    id4[tbl3:TBL3_PK:T2.ID3=T3.ID,T3.DATA='aaa'] --> id5;
    id5[NESTED LOOPS] --> id7;
    id6[tbl4] --> id7
    id7[HASH JOIN OUTER:T3.ID=T4.ID+]


```

# おわりに
今回は実行計画の見方について整理してみました。
SQLチューニングを実施する場合にはまず、現在の動作を実行計画で確認してからチューニング箇所を特定する必要があります。このため実行計画の見方を理解しておきたいですね。

それでは引き続き[NTTテクノクロス Advent Calendar 2022](https://qiita.com/advent-calendar/2022/ntt-tx)の１１日目もお楽しみください。