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

実行計画についてのよくある誤解 ～Oracle Database版～

渡部です。OracleでSQLチューニングを行うには実行計画を理解する必要がありますが、 適切な説明が少ないこともあり、誤解している人をよく見ます。 過去のDatabase Lounge Tokyo #5発表 のスライドを下敷きにして、 Oracle Databaseの実行計画に関する「よくある誤解」を紹介しながら、実行計画について正しい理解ができるよう説明します。

ここで取り上げるOracle実行計画についての誤解

以下の誤解を取り上げます。

• 誤解1) 実行計画はEXPLAIN PLANで確認すれば十分！
• 誤解2) 実行計画はもっともインデントが深いステップから実行される！
• 誤解3) Rows統計の値が同じなら、アクセスしたデータ量は同程度の効率！
• 誤解4) 実行計画の各ステップは必ず1回だけ実行される

誤解1) 実行計画はEXPLAIN PLANで確認すれば十分！

昔のOracleでは、SQL*Plus単体で実行計画を確認する方法はEXPLAIN PLANしかありませんでした。 このため、実行計画の確認というと、EXPLAIN PLANについて言及されることが多かったのですが、 EXPLAIN PLANには大きな問題があります。

EXPLAIN PLANで確認した実行計画は、確認対象の環境やタイミングにおける実行計画と同じである保証はないのです。

実行計画は、実行時の実行環境、すなわちセッションレベルで変更可能な初期化パラメータや プログラミングインタフェース固有の部分（バインド変数の扱いなど）に左右されます。

仮に同一のSQLを実行をしたとしても、実行時の実行環境が異なると、異なる実行計画になる可能性があります。すなわち、EXPLAIN PLANで確認した実行計画が、実際のアプリケーションでSQLを実行したときの実行計画と異なる可能性がある。ということです。

この挙動を理解しておかないと、SQLパフォーマンス問題のトラブルシュートで見当違いの調査方針を立てることにつながります。

では、実際のアプリケーションでSQLを実行したときの実行計画を確認するにはどうしたらよいのでしょうか。SQL実行後、共有プールにキャッシュされている実行計画を確認するのが良い方法です。 具体的には、DBMS_PLAN.DISPLAY_CURSORなどを使用します。

また、AWR、Statspackなどの実行計画レポートも使用できます。これらは、共有プールにキャッシュされていた実行計画を保管し、レポート化したものであるためです。

ただ、1つ注意点があります。同一のSQLについて、複数の実行計画がキャッシュされる場合があります。その場合は、どちらの実行計画が使用されていたのかを、直近の実行時間、直近の実行計画作成時間などを参考に特定する必要があります。

誤解2) 実行計画は最もインデントが深いステップから実行される！

Webなどの解説で、

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

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

段階を追って、実行計画の読み方を説明しましょう。

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

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

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

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

誤解3) Rows統計の値が同じならば、アクセスしたデータの量は同じ

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

このため、Rows統計に着目してSQLチューニングを行うわけですが、「Rowsはそのステップが上位ステップに引き渡す行数を示しており、そのステップでアクセスした行数を示すわけではない」点に注意して下さい。

以下にRows統計の値が"1"である2つの実行計画を示します。 Rows統計の値が同じなので、SQL実行時の負荷も同程度だと考えがちですが、違います。 あらためて、「Rowsはそのステップが上位ステップに引き渡す行数を示しており、そのステップでアクセスした行数を示すわけではない」ことに注意しましょう。

Rows統計の値が同じということは、「そのステップが上位ステップに引き渡す行数が同じ」ことを示しているにすぎず、「そのステップでアクセスした行数が同じ」ことを示しているわけでは無いのです。

実際、この2つの実行計画のうち、左の実行計画はアクセスしたテーブルの行数が1ですが、 右の実行計画はアクセスしたテーブルの行数がテーブルの全ての行です。 SQL実行時の負荷（データI/O量）は大きく異なります。

誤解4) 実行計画の各ステップは必ず1回だけ実行される

実行計画はステップの親子関係からなるツリー構造をしています。 各ステップには1つオペレーションが対応しているため、各ステップは必ず1回だけ実行されると誤解しがちです。たいていの場合、それは正しいのですが例外もあります。

以下の図では、

SELECT cid, cname, pa.pid, pname 
   FROM ch, pa
   WHERE ch.pid = pa.pid and pa.pid IN (1,2,3)

というSQLの実行計画を示しています。

Id=3,4のステップのStarts実行統計が3であることに注意してください。 これは、Id=3,4のステップの実行回数が3であることを示します。

実行計画の実行イメージを深くイメージすると、理解が深まるでしょう。

Id=2のステップである、TABLE ACCESS FULLで3行のデータを得た結果、 各行ごとに、計3回、Id=4のINDEX UNIQUE SCAN 、Id=3のTABLE ACCESS BY INDEX ROWIDが実行されているのです。

「実行計画の読み方」も読んでみてください！

このエントリは、「Oracle SQL実行計画の読みかた」の内容を、切り口を変えて再構成した内容になっています。

このエントリでは一部説明を簡略化部分がありますので、分からない点があれば、 「Oracle SQL実行計画の読みかた」にも目を通してみてください。