6 実行計画の生成と表示

SQLチューニングを行うには、実行計画についての十分な理解が不可欠です。

この章のトピックは、次のとおりです:

6.1 実行計画の概要

文を実行するためにOracle Databaseが使用するステップの組合せが実行計画です。

各処理では、データベースからデータ行を物理的に検索するか、文を発行したユーザーのためにデータ行を準備します。実行計画には、文がアクセスする各表へのアクセス・パスと、適切な結合方法に基づく表の順序(結合順序)が含まれています。

6.2 計画の生成と表示について

EXPLAIN PLAN文は、SELECT、UPDATE、INSERTおよびDELETE文に関して、オプティマイザによって選択された実行計画を表示します。

この項では、次の項目について説明します。

6.2.1 計画の説明について

文の実行計画とは、その文を実行するためにデータベースで行われる一連の処理です。

行ソース・ツリーは、実行計画の中核です。このツリーには、次の情報が表示されます。

• 文によって参照される表の順序

• 文で言及される各表へのアクセス方法

• 文の結合操作の影響を受ける表の結合方法

• フィルタ、ソート、集計などのデータ操作

PLAN TABLEには、行ソース・ツリーの他、次の情報が含まれています。

• 最適化。各操作のコストとカーディナリティについて。

• パーティション化。アクセスされたパーティションのセットなど。

• パラレル実行。結合入力の配分方法など。

EXPLAIN PLANの結果を使用して、オプティマイザが特定の実行計画(たとえば、ネステッド・ループ結合)を選択するかどうかを判断できます。また、オプティマイザの決定(たとえば、オプティマイザがハッシュ結合でなくネステッド・ループ結合を選択した理由)を理解するためにも役立ちます。

関連項目:

• SQLの行ソース生成

• EXPLAIN PLAN文について学習するには、Oracle Database SQL言語リファレンスを参照してください

6.2.2 実行計画の変化理由

実行計画は基礎となるオプティマイザ入力が変化するたびに変化します。

EXPLAIN PLANの出力は、EXPLAINされたSQL文をデータベースがどのように実行するかを示します。実行環境とEXPLAIN PLAN環境は異なるため、この計画は、SQL文で使用される実際の実行計画とは異なる可能性があります。

ノート:

実行計画の変更から生じる可能性があるSQLパフォーマンスの低下を回避するには、SQL計画の管理を使用することを検討してください。

この項では、次の項目について説明します。

関連項目:

• 「SQL計画管理の概要」

• DBMS_SPMパッケージについて学習するには、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください

6.2.2.1 スキーマの相違

スキーマは、様々な理由で異なる場合があります。

主な理由は次のとおりです。

• 実行とEXPLAIN PLANのデータベースが異なる場合。

• 文をEXPLAINするユーザーが、文を実行するユーザーとは異なる場合。2人のユーザーが同じデータベース内の異なるオブジェクトを指していれば、異なる実行計画が発生します。

• 2つの操作間でスキーマが変更された場合(多くは索引の変更)。

6.2.2.2 コストの相違

スキーマが同じであっても、コストに違いがある場合、オプティマイザは異なる実行計画を選択する可能性があります。

コストに影響を与えるいくつかの要因には次のものがあります。

• データ量と統計

• バインド変数の型と値

• グローバルまたはセッション・レベルで設定された初期化パラメータ

6.2.3 排除行数の最少化のガイドライン

EXPLAIN PLANを調べると、排除された行を確認できます。

データベースは、次のような状況で行を排除することがよくあります。

• 全体スキャン

• 選択性のないレンジ・スキャン

• 遅延した述語フィルタ

• 誤った結合順序

• 遅延したフィルタ処理

例6-1の計画では、最後のステップは非常に選択性のないレンジ・スキャンです。このレンジ・スキャンは76,563回実行され、11,432,983行にアクセスし、アクセスした行の99パーセントを排除して76,563行を保持します。11,432,983行にアクセスした結果、必要な行が76,563行のみであると認識された理由について考えます。

例6-1 EXPLAIN PLAN内の排除行数の確認

Rows      Execution Plan
--------  ----------------------------------------------------
      12  SORT AGGREGATE
       2   SORT GROUP BY
   76563    NESTED LOOPS
   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)
   76563     TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL
11432983      INDEX RANGE SCAN (object id 186024)

6.2.4 EXPLAIN PLANを使用した実行計画の評価のガイドライン

実行計画の操作のみでは、よく調整された文と最適には機能しない文を区別できません。

たとえば、文による索引の使用がEXPLAIN PLAN出力で示されたとしても、その文が効率的に機能するとはかぎりません。効率的でない索引もあります。この場合、次を調べることをお薦めします。

• 使用される索引の列

• その索引の選択性(アクセスされる表の一部)

EXPLAIN PLANを使用してアクセス計画を判断し、後からテストによってそれが最適な計画であることを確認するのが最もよい方法です。計画を評価する際は、文の正確なリソース使用量を調べてください。

この項では、次の項目について説明します。

6.2.4.1 V$SQL_PLANビューを使用した計画の評価のガイドライン

EXPLAIN PLANコマンドを実行して計画を表示するかわりに、V$SQL_PLANビューを問い合せて計画を表示できます。

V$SQL_PLANには、共有SQL領域に格納されたすべての文の実行計画が含まれます。その定義は、PLAN_TABLEに類似しています。

V$SQL_PLANがEXPLAIN PLANよりも優れている点は、特定の文の実行に使用されたコンパイル環境を知らなくてもかまわないということです。EXPLAIN PLANの場合は、文の実行時に同じ計画を取得するために同一環境をセットアップする必要があります。

V$SQL_PLAN_STATISTICSビューは、出力行数や経過時間など、計画に含まれる操作ごとに実際の実行統計を提供します。出力行数を除き、すべての統計は累積されます。たとえば、結合操作の統計には、2つの入力の統計も含まれます。V$SQL_PLAN_STATISTICSの統計は、STATISTICS_LEVEL初期化パラメータをALLに設定してコンパイルされたカーソルに使用できます。

V$SQL_PLAN_STATISTICS_ALLビューを使用すると、行数と経過時間に関してオプティマイザにより提供される見積りを並べて表示できます。このビューでは、各カーソルのV$SQL_PLANおよびV$SQL_PLAN_STATISTICS情報が結合されます。

関連項目:

• 「PLAN_TABLE列」

• V$SQL_PLAN_MONITORビューについては、「データベース操作の監視」を参照してください

• V$SQL_PLANビューの詳細は、『Oracle Databaseリファレンス』を参照してください

• STATISTICS_LEVEL初期化パラメータについては、『Oracle Databaseリファレンス』を参照してください

6.2.5 EXPLAIN PLANの制限事項

Oracle Databaseでは、日付バインド変数の暗黙的な型変換を実行する文でのEXPLAIN PLANをサポートしません。

一般にバインド変数では、EXPLAIN PLANが実際の実行計画を表していない場合があります。

TKPROFは、SQL文のテキストからバインド変数の型を判断することはできません。型はVARCHARであると想定され、それ以外の場合はエラー・メッセージが表示されます。この制限事項は、SQL文に適切な型変換を入れることで対処できます。

関連項目:

• 「アプリケーション・トレースの実行」

• 「引数トラップを回避するためのガイドライン」

• SQLデータ型についてさらに学習するには、『Oracle Database SQL言語リファレンス』を参照してください

6.2.6 PLAN_TABLEの作成のガイドライン

PLAN_TABLEは、グローバル一時表に対するパブリック・シノニムとして自動的に作成されます。

この一時表に、すべてのユーザーに対するEXPLAIN PLAN文の出力が保持されます。PLAN_TABLEは、EXPLAIN PLAN文が実行計画について記述している行を挿入するデフォルトのサンプル出力表です。

PLAN_TABLE表は各ユーザーに対し自動的に設定されますが、SQLスクリプトcatplan.sqlを使用して、グローバル一時表とPLAN_TABLEシノニムを手動で作成できます。このスクリプトの名前と位置は、使用するオペレーティング・システムによって異なります。UNIXおよびLinux上では、このスクリプトは$ORACLE_HOME/rdbms/adminディレクトリにあります。

たとえば、SQL*Plusセッションを開始し、SYSDBA権限で接続し、そして次のようにスクリプトを実行します。

@$ORACLE_HOME/rdbms/admin/catplan.sql

データベースのバージョンを更新した場合は、列が変更される可能性があるため、ローカルのPLAN_TABLE表を削除して再作成することをお薦めします。表を指定する場合は、スクリプトの実行が失敗したり、TKPROFが失敗する場合があります。

名前PLAN_TABLEを使用しない場合は、catplan.sqlスクリプトの実行後に新しいシノニムを作成します。次に例を示します。

CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$

関連項目:

• 表内の列の説明については、「PLAN_TABLE列」を参照してください

• CREATE SYNONYMについて学習するには、『Oracle Database SQL言語リファレンス』を参照してください

6.3EXPLAIN PLAN文を使用した計画出力の生成

EXPLAIN PLAN文により、オプティマイザがSQL文に対して選択した実行計画を確認できます。

この項では、次の項目について説明します。

6.3.1 1つの文に対するEXPLAIN PLANの実行

データベースで提供されるスクリプトを使用して計画を説明します。

EXPLAIN PLAN文の基本的な使用方法は次のとおりです。

• SQLスクリプトcatplan.sqlを使用して、使用しているスキーマ内にPLAN_TABLEという名前のサンプル出力表を作成します。

• SQL文の前に、EXPLAIN PLAN FOR句を含めます。

• EXPLAIN PLAN文を発行した後、Oracle Databaseから提供されるスクリプトまたはパッケージを使用して最新のPLAN TABLE出力を表示します。

• EXPLAIN PLANの出力実行順序は、最も右端にインデントされている行から始まります。次のステップは、その行の親です。2つの行が等しくインデントされている場合、通常、一番上の行が最初に実行されます。

  ノート:

  • この章のEXPLAIN PLAN出力表は、utlxpls.sqlスクリプトで表示したものです。

  • この章のEXPLAIN PLAN出力のステップは、ご使用のデータベースによって異なる可能性があります。データベース構成によって、オプティマイザは異なる実行計画を選択する場合があります。

SQL文をEXPLAINする場合は、文の直前でEXPLAIN PLAN FOR句を使用します。次に例を示します。

EXPLAIN PLAN FOR
  SELECT last_name FROM employees;

前述の計画は、計画をEXPLAINし、PLAN_TABLE表に出力を格納します。PLAN_TABLEから実行計画を選択できるようになります。

関連項目:

• 「PLAN_TABLEの作成のガイドライン」

• 「PLAN_TABLE出力の表示」

• EXPLAIN PLANの構文およびセマンティクスについては、『Oracle Database SQL言語リファレンス』を参照してください

6.3.2 文IDを使用したEXPLAIN PLANの実行

複数の文があるときは、文の識別子を指定し、その識別子で特定の実行計画を識別できます。

SET STATEMENT IDを使用する前に、その文と同じ識別子を持つ既存の行を削除してください。次の例では、st1が文の識別子として指定されています。

例6-2 STATEMENT ID句でのEXPLAIN PLANの使用方法

EXPLAIN PLAN
  SET STATEMENT_ID = 'st1' FOR
  SELECT last_name FROM employees;

6.3.3 デフォルト以外の表へのEXPLAIN PLAN出力の指示

INTO句を指定して、別の表を指定できます。

次の文は、my_plan_tableに出力を送ります。

EXPLAIN PLAN
  INTO my_plan_table FOR
  SELECT last_name FROM employees;

次の文のように、INTO句を使用する場合は、文のIDを指定できます。

EXPLAIN PLAN
   SET STATEMENT_ID = 'st1'
   INTO my_plan_table FOR
   SELECT last_name FROM employees;

関連項目:

EXPLAIN PLAN構文の詳細は、Oracle Database SQL言語リファレンスを参照してください。

6.4 PLAN_TABLE出力の表示

スクリプトまたはパッケージを使用して、計画出力を表示できます。

計画をEXPLAINした後、Oracle Databaseから提供される次のSQLスクリプトまたはPL/SQLパッケージを使用して最新のPLAN TABLE出力を表示します。

• utlxpls.sql

  このスクリプトは、シリアル処理のためのPLAN TABLE出力を表示します。例6-4は、utlxpls.sqlスクリプトを使用した場合のPLAN TABLE出力の例です。

• utlxplp.sql

  このスクリプトは、パラレル実行列を含むPLAN TABLE出力を表示します。

• DBMS_XPLAN.DISPLAYテーブル・ファンクション

  このファンクションは、PLAN TABLE出力を表示するオプションを受け入れます。次を指定できます。

  • PLAN_TABLEとは別の表を使用している場合のPLAN TABLE名

  • EXPLAIN PLANに文の識別子を設定した場合の文の識別子

  • 詳細レベルを決定するフォーマット・オプション: BASIC、SERIAL、TYPICALおよびALL

  DBMS_XPLANを使用してPLAN_TABLE出力を表示する例を次に示します。

  SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
  
  SELECT PLAN_TABLE_OUTPUT 
    FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

  この項では、次の項目について説明します。

  関連項目:

  DBMS_XPLANパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

6.4.1 実行計画の表示: 例

この例では、EXPLAIN PLANを使用して、IDが103より小さい従業員のemployee_id、job_title、salaryおよびdepartment_nameを選択するSQL文を確認します。

例6-3 EXPLAIN PLANの使用方法

EXPLAIN PLAN FOR
  SELECT e.employee_id, j.job_title, e.salary, d.department_name
  FROM   employees e, jobs j, departments d
  WHERE  e.employee_id < 103
  AND    e.job_id = j.job_id 
  AND    e.department_id = d.department_id;

例6-4 EXPLAIN PLAN出力

次の出力表では、例6-3にあるSQL文を実行するためにオプティマイザで選択する実行計画が示されています。

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."EMPLOYEE_ID"<103)
   5 - access("E"."JOB_ID"="J"."JOB_ID")
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"

------------------------------------------------------------------------------------------------
| Id | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |               |     3 |   189 |     8  (13)| 00:00:01 |
|  1 |  NESTED LOOPS                   |               |       |       |            |          |
|  2 |   NESTED LOOPS                  |               |     3 |   189 |     8  (13)| 00:00:01 |
|  3 |    MERGE JOIN                   |               |     3 |   141 |     5  (20)| 00:00:01 |
|  4 |     TABLE ACCESS BY INDEX ROWID | JOBS          |    19 |   513 |     2   (0)| 00:00:01 |
|  5 |      INDEX FULL SCAN            | JOB_ID_PK     |    19 |       |     1   (0)| 00:00:01 |
|* 6 |     SORT JOIN                   |               |     3 |    60 |     3  (34)| 00:00:01 |
|  7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     3 |    60 |     2   (0)| 00:00:01 |
|* 8 |       INDEX RANGE SCAN          | EMP_EMP_ID_PK |     3 |       |     1   (0)| 00:00:01 |
|* 9 |    INDEX UNIQUE SCAN            | DEPT_ID_PK    |     1 |       |     0   (0)| 00:00:01 |
| 10 |   TABLE ACCESS BY INDEX ROWID   | DEPARTMENTS   |     1 |    16 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("E"."JOB_ID"="J"."JOB_ID")
       filter("E"."JOB_ID"="J"."JOB_ID")
   8 - access("E"."EMPLOYEE_ID"<103)
   9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

6.4.2 PLAN_TABLE出力のカスタマイズ

文の識別子を指定した場合は、PLAN_TABLEを問い合せるための独自のスクリプトを書くことができます。

次に例を示します。

• START WITH ID = 0およびSTATEMENT_IDを指定します。

• CONNECT BY句を使用して親から子へツリーを移動します。結合キーは、STATEMENT_ID = PRIOR STATMENT_IDおよびPARENT_ID = PRIOR IDです。

• 疑似列LEVEL(CONNECT BYに関連付けられている)を使用して子をインデントします。

  SELECT  cardinality "Rows", lpad(' ',level-1) || operation
          ||' '||options||' '||object_name "Plan"
  FROM    PLAN_TABLE
  CONNECT BY prior id = parent_id
          AND prior statement_id = statement_id
    START WITH id = 0
          AND statement_id = 'st1'
    ORDER BY id;
  
     Rows Plan
  ------- ----------------------------------------
          SELECT STATEMENT
           TABLE ACCESS FULL EMPLOYEES

  Rows列のNULLは、オプティマイザが表に統計を持っていないことを示します。表をANALYZEすると、次の内容が表示されます。

     Rows Plan
  ------- ----------------------------------------
    16957 SELECT STATEMENT
    16957  TABLE ACCESS FULL EMPLOYEES

  COSTも選択できます。これは、実行計画を比較する場合や、オプティマイザが複数の中からある実行計画を選択した理由を理解する場合に便利です。

  ノート:

  これらの単純な例は、再帰的SQLの場合には有効ではありません。