LoginSignup
2

posted at

updated at

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

はじめに

この記事は NTTテクノクロス Advent Calendar 2022 の10日目の記事です。

こんにちはNTTテクノクロスで最近はDB関連のお仕事をしている田島です。

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

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

欲しい情報とは

以下の情報が順序良く並んでいると見やすいと思いますので、その観点で説明していこうと思います。

  1. 実行順
  2. アクセス方法(インデックスを使用しているのか等)
  3. 検索条件
  4. 結合方式

環境

今回使用したテーブル構造

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
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の段下げ(空白)の数+1を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 |
------------------------------------------------------------

実行順のルール

  1. Id=0から実行
  2. 自分のIdの次が自分のlevelより大きければそちらを先に実行し、levelの大きいものが実行済みであれば自分を実行
  3. 実行後自分と同じ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つあります。詳細はマニュアルで確認してみてください。

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

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

まとめ

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

おわりに

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

それでは引き続きNTTテクノクロス Advent Calendar 2022の11日目もお楽しみください。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
2