• Oracle Database
• SQLチューニング
• Toad
• 対外講演まとめ
• 2020.04.24
• 渡部 亮太

Oracle SQL実行計画の読み方

渡部です。SQLチューニングの基本は、実行計画への理解です。 過去のdb tech showcase発表のスライドがよくまとまっている（自画自賛！）ため、これを用いて実行計画の読みかたを説明したいと思います。

db tech showcase 2017「Oracle入門セミナー 実行計画を読んでみよう！」

2017年9月のdb tech showcase 2017の発表を下敷きにして、実行計画の読みかたを説明します。

• db tech showcase 2017「Oracle入門セミナー 実行計画を読んでみよう！」 https://cosol.jp/techdb/2017/09/dbts2017-oracle-read-execution-plan-html/

実行計画とは

実行計画は、SQLがOracleで実行される手順を示します。

実行計画は、Oracleの内部コンポーネントであるコストベースオプティマイザ(CBO; Cost-Based Optimizer)が作成します。

実行計画を作成する元ネタは、オプティマイザ統計です。 このため、オプティマイザ統計が最新でないと適切な実行計画が作成されません。

不適切な実行計画でSQLが実行されると、本来であれば実現できる処理パフォーマンスを得られません。

実行計画に含まれる情報

後述するEXPLAIN PLAN や DBMS_XPLANパッケージを使うと、実行計画を確認できます。 確認結果には様々な情報が含まれています。

• ツリー構造をしたステップ
• 各ステップの統計値
• sql_id: SQL文の識別子（Oracleが自動的に付与）
• 実行計画のハッシュ値
• SQL文
• 補足情報

実行計画の確認方法によっては、一部の情報が表示されない場合があります。

実行計画の確認方法

実行計画を確認する方法には、様々な方法があります。 ここでは主な方法として3つを紹介しています。

• a) EXPLAIN PLAN実行後にDBMS_XPLAN.DISPLAYを実行して、実行計画を確認
• b) SQL実行後にDBMS_XPLAN.DISPLAY_CURSORを実行して、共有プール上の実行計画を確認
• c) 初期化パラメータSTATISTICS_LEVEL=ALLを設定したうえでSQLを実行し、DBMS_XPLAN.DISPLAY_CURSORに、引数format=>'ALLSTATS LAST'を指定して、共有プール上の実行計画を確認

いずれの方法でも、実行計画または統計値を確認できます。 しかし、確認方法によっては、それらが実行したときの情報ではなく、Oracleが予測した情報が表示される点に注意して下さい。

特に、a) EXPLAN PLANを用いる方法はお手軽で便利ですが、実行計画と統計値が予測情報です。すなわち、実際にSQLを実行したときの実行計画と統計値とは異なる場合があります。この点は、パフォーマンス問題の調査において、問題となりがちですので注意してください。

実行計画の正確性の観点から、 b) または c) のDBMS_XPLAN.DISPLAY_CURSORを使う方法がオススメです。 特に c) の方法を使うと、統計値についても実測値を確認できますので、非常に有用です。

オペレーションとステップ

これから、実行計画の各ステップで使用されるオペレーションを説明します。

• ステップ : 実行計画の実行手順における実行要素。親子関係がある
• オペレーション: 各ステップで実行される操作の種類。TABLE ACCESS FULL、 INDEX RANGE SCAN などがある

テーブルフルスキャン / TABLE ACCESS FULL

TABLE ACCESS FULLは、テーブル全体を読み出すオペレーションです。

ここでは、

select * from tab0 where col_noix = 'A'

というSQLを実行し、大量のデータから、col_noix = 'A'という条件を満たす少量のデータを取得しています。

このように、テーブルから読み出すデータが少量の場合、オペレーションを含む実行計画は非効率な場合があります。そのような場合はSQLチューニングの余地があります。

インデックス経由のテーブルアクセス（一意） / INDEX UNIQUE SCAN

INDEX UNIQUE SCANは、一意制約または主キー制約が設定された列に対するインデックスを使っていることを示すオペレーションです。 列に一意制約または主キー制約を設定すると、その列にはインデックスが自動的に作成されます。その列を検索条件に指定するSQLを実行すると、INDEX UNIQUE SCANオペレーションを使った実行計画が選択されることがあります。

ここでは、

select * from tab0 where id = '1';

というSQLを実行し、id列に設定したインデックスを経由して、テーブルにアクセスしています（TABLE ACCESS BY INDEX ROWIDオペレーション）。 id列には、PK_TAB0という名前の主キー制約（および同名の索引）が設定されています。

検索条件に設定した列に一意制約または主キー制約が設定されているため、 検索条件に合致するデータは常に1件です（検索条件に合致するデータが全く存在しない場合を除く）。

インデックス経由のテーブルアクセス（範囲） / INDEX RANGE SCAN

INDEX RANGE SCANは、通常のインデックス（一意制約または主キー制約が設定されていない列に対するインデックス）を使っていることを示すオペレーションです。

検索条件に設定した列に一意制約または主キー制約が設定されていないため、 検索条件に合致するデータが複数件の場合があります。

上記でも、2件のデータが検索条件に合致した状況を例にしています。

実行計画のツリー構造

実行計画を構成するステップには親子関係があり、ツリー構造を構成します。

• 親ステップは子ステップから「行」を受け取ります。
• 2つ以上の子ステップを持つことがあります
  • たいてい子ステップは2つか1つです。
  • 3つ以上の子ステップを持つのは、一部の特殊なオペレーションだけです。

ツリー構造のたどり方 / 最も深いインデントのステップから実行されるとは限らない！

実行計画を理解するには、各ステップのオペレーションの意味を理解することにくわえて、 ステップの親子関係から構成されるツリー構造をどの様に辿るかを理解する必要があります。

ただし、ツリー構造の辿り方を誤解している方が多いです。Web上の資料でも誤った記載が多くあります。

まず、ツリー構造を整理します。

次に、ツリー構造の辿り方のルールを示します。

1. 最上位から親→子方向にたどってゆく
2. 複数の「子ステップ」がある場合、表形式表示で上にある「子ステップ」に進む
3. そのステップから、さらに親→子方向にたどってゆく
4. 最下位の「子ステップ」に到達したら、実行を開始する。
5. 実行を終了したら、「親ステップ」に戻る
6. その「親ステップ」に、他の「子ステップ」がある場合、表形式表示で上にある「子ステップ」に進み、3. へ。他の「子ステップ」がない場合、その「親ステップ」を実行し、その「親ステップ」の「さらに親のステップ」に戻り、6.を繰り返す

このルールに従うと、例の実行計画のツリー構造の辿り方は以下の様になります。

実行計画は、最も深いインデントのステップから実行されます

などという解説をよく見かけますが、これは誤りです。 かならずしも、最も深いインデントのステップから実行されるわけではないことに注意してください。

テーブルの結合と実行計画

次にテーブルとテーブルを結合した場合の実行計画について説明します。

テーブルとテーブルを結合した場合に選択される実行計画の種類は以下の3つです。

• ネステッドループ結合
• ハッシュ結合
• ソートマージ結合

ネステッドループ結合の実行計画を読んでみる

ネステッドループ結合の実行計画を例にして、実行計画を読む手順を学習します。

実行計画を読む手順は以下の通りです。

1. インデント表形式からツリー構造をイメージする
2. ツリー構造の実行順序を理解する
3. オペレーションと実行順序から、各ステップの処理内容をイメージする

今回とりあげる実行計画は以下の通りです。 Id=1のステップで"NESTED LOOPS"オペレーションが使用されていることから、ネステッドループ結合の実行計画であることが分かります。

インデント表形式からツリー構造をイメージ

まず、

1. インデント表形式からツリー構造をイメージする

から始めます。

ツリー構造の実行順序を理解

次に、

2. ツリー構造の実行順序を理解する を行います。

このとき、先ほど説明したツリー構造の辿り方のルールを基づき、理解します。

1. 最上位から親→子方向にたどってゆく
2. 複数の「子ステップ」がある場合、表形式表示で上にある「子ステップ」に進む
3. そのステップから、さらに親→子方向にたどってゆく
4. 最下位の「子ステップ」に到達したら、実行を開始する。
5. 実行を終了したら、「親ステップ」に戻る
6. その「親ステップ」に、他の「子ステップ」がある場合、表形式表示で上にある「子ステップ」に進み、3. へ。他の「子ステップ」がない場合、その「親ステップ」を実行し、その「親ステップ」の「さらに親のステップ」に戻り、6.を繰り返す

各ステップの処理内容をイメージ

最後に、

3. オペレーションと実行順序から、各ステップの処理内容をイメージする を行います。

オペレーションの意味はすでに説明しました。 オペレーションに対応する処理が、ツリー構造の実行順序で実行されると理解して、処理内容をイメージします。

今回の例をもうすこし具体的に説明すると、以下の様に言えるでしょうか。

1. Id=3 INDEX UNIQUE SCANで、検索条件pid=1に合致する1つの行を特定（行の識別子ROWIDを取得)
2. Id=2 TABLE ACCESS BY INDEX ROWIDで、インデックスで特定された行データを得る
3. Id=5 INDEX RANGE SCANで、検索条件pid=1に合致する複数の行（ここでは3つの行）を特定
4. Id=4 TABLE ACCESS BY INDEX ROWIDで、インデックスで特定された行データを得る
5. Id=1 NESTED LOOPSで、テーブルPAとテーブルCHから得られたデータを結合

実行計画の統計値を理解する

これまでの説明で、実行計画の動作イメージを掴めたはずです。

あとは、補足として、実行計画の統計値について説明します。

実行計画の主役はツリー構造をしたステップですが、 Oracle Databaseでは、各ステップの統計値を確認することもできます。

統計値を解釈する際には、以下の点に注意してください。

• 統計値の種類によって、統計値の解釈方法が異なる
  • 統計値が下位ステップ（全ての子のステップ）を含めた累積値である場合
  • 統計値がそのステップ単体（子のステップは含まず、そのステップだけ）の統計値である場合
• 見積統計と実行統計の2種類がある
  • 見積統計は、文字通りあくまでも「見積」にすぎないため、SQLチューニングでは実行統計の方が重要
  • 見積統計を実行統計と勘違いすると、完全に的を外したチューニング作業になることがあるため、注意！

以下のスライドでは、Rows、Bytes、Cost、Timeという統計値の意味と、それらの統計値が累積値であるか否かがをまとめています。

実行計画のRows統計

Rows統計はSQLチューニングにおいて重要な統計値です。 一般にRows統計は小さい値であることが望ましいとされています。可能であれば、実行計画の処理ステップの早い段階で、Rows統計値を小さい値にできるような、実行計画でSQLを実行することが望ましいです。

このため、Rows統計に着目してSQLチューニングを行うわけですが、以下の点に注意して下さい。

• 「Rows統計」は見積統計であるため、実行統計である「A-Rows」を用いる方がより適切
• Rowsはそのステップが上位ステップに引き渡す行数を示しており、そのステップでアクセスした行数を示すわけではない

個人的には、そのステップでアクセスした行数を示す実行統計値が欲しいのですが・・・ 残念ながらOracleにはそのような統計値はないのですよね・・・

実行統計の重要性

すでに説明していますが、実行計画の統計値には見積統計と実行統計の2種類があります。

見積統計は、文字通りあくまでも「見積」にすぎませんので、実際の統計値とは異なる場合があります。 よって、SQLチューニングの際には見積統計よりも実行統計に着目すべきです。

ここで注意していただきたいのは、実行計画の確認方法によって、実行計画の統計値が見積統計のものと、実行統計のものあるという点です。

• a) EXPLAIN PLAN実行後にDBMS_XPLAN.DISPLAYを実行して、実行計画を確認
• b) SQL実行後にDBMS_XPLAN.DISPLAY_CURSORを実行して、共有プール上の実行計画を確認
• c) 初期化パラメータSTATISTICS_LEVEL=ALLを設定したうえでSQLを実行し、DBMS_XPLAN.DISPLAY_CURSORに、引数format=>'ALLSTATS LAST'を指定して、共有プール上の実行計画を確認

のうち、実行統計の統計値を確認できる方法は c)だけです。

実行統計の統計値が見積統計なのか、実行統計なのかは、いくつかの例外がありますが、統計値の名前に"E-"が付くか、"A-"が付くかで見分けられます。

見積統計と実行統計の値が異なる場合

見積統計は、オプティマイザ統計という実際のデータを集約したサマリ情報を元にOracleが計算した予測値に過ぎませんから、見積統計と実行統計が大幅に異なる値になることがあります。

これは、Oracleが見積（予測）に失敗していることを示しています。

この問題に対処する方法は、より正確な見積ができるように、それを支援する情報を追加することです。すなわち、 オプティマイザ統計を改善して、より細かい情報を取得するようにします。

ここでは、ヒストグラムと呼ばれる列値の頻度情報を新たに収集することで解決しました。 ある列について、どの列の値がどれだけ存在しているかをオプティマイザ統計に追加することで、 より精度の高い見積（予測）ができるようにしたというわけです。

Starts実行統計 : オペレーションの実行回数

最後に実行統計ならではの統計値として、Starts実行統計を紹介します。

Starts実行統計は、そのステップにおけるオペレーションの実行回数を示す実行統計です。

SQLによっては、あるステップが複数回実行されることがあります。Starts実行統計を見ると、この動作を把握することができます。これにより実行計画でやっていることが理解しやすくなる場合があります。