実行計画の説明と表示

6 実行計画の説明と表示

SQLチューニングには、文を説明する方法と文の計画を表示する方法に関する知識が不可欠です。

6.1 実行計画の概要

実行計画は、SQL文を実行するためにデータベースが実行する一連の操作です。

6.1.1実行計画のコンテンツ

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

計画は一連のステップで構成されています。各ステップでは、データベースからデータ行を物理的に取得するか、文を発行したユーザーのためにデータ行を準備します。次の計画は、employees表とdepartments表の結合を示しています。

SQL_ID  g9xaqjktdhbcd, child number 0
-------------------------------------
SELECT employee_id, last_name, first_name, department_name from
employees e, departments d WHERE e.department_id = d.department_id and
last_name like 'T%' ORDER BY last_name

Plan hash value: 1219589317

----------------------------------------------------------------------------------------
| Id | Operation                    | Name        |Rows | Bytes |Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |             |     |       |    5 (100)|          |
|  1 |  NESTED LOOPS                |             |   5 |   190 |    5   (0)| 00:00:01 |
|  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   5 |   110 |    2   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | EMP_NAME_IX |   5 |       |    1   (0)| 00:00:01 |
|* 4 |   TABLE ACCESS FULL          | DEPARTMENTS |   1 |    16 |    1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("LAST_NAME" LIKE 'T%')
       filter("LAST_NAME" LIKE 'T%')
   4 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

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

文で参照されている表の結合順序

前述の計画では、employeesは外部行ソース、departmentsは内部行ソースです。
文に記述された各表へのアクセス・パス

前述の計画では、オプティマイザはemployeesへのアクセスに索引スキャンの使用を選択し、departmentsへのアクセスに全体スキャンの使用を選択しています。
文の結合操作に影響される表の結合方法

前述の計画では、オプティマイザはネステッド・ループ結合を選択しています。
フィルタ、ソート、集計などのデータ操作

前述の計画では、オプティマイザはTで始まる姓でフィルタ処理して、department_idで照合しています。

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

最適化。各操作のコストとカーディナリティについて。
パーティション化。アクセスされたパーティションのセットなど。
パラレル実行。結合入力の配分方法など。

6.1.2 実行計画の変化理由

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

ノート:

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

関連項目:

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

6.1.2.1 スキーマの相違

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

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

実行とEXPLAIN PLANのデータベースが異なる場合。
文をEXPLAINするユーザーが、文を実行するユーザーとは異なる場合。2人のユーザーが同じデータベース内の異なるオブジェクトを指していれば、異なる実行計画が発生します。
2つの操作間でスキーマが変更された場合(多くは索引の変更)。

6.1.2.2 コストの相違

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

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

データ量と統計
バインド変数の型と値
グローバルまたはセッション・レベルで設定された初期化パラメータ

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

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

6.2.1EXPLAIN PLAN文について

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

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

EXPLAIN PLAN文が発行されると、オプティマイザは実行計画を選択し、実行計画の各ステップを表す行を指定されたPLAN TABLEに挿入します。SQLトレース機能の一部としてEXPLAIN PLAN文を発行することもできます。

EXPLAIN PLAN文は、DDL文ではなくDML文です。そのため、EXPLAIN PLAN文で加えられた変更内容は暗黙的にコミットされません。

関連項目:

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

6.2.1.1 PLAN_TABLEについて

PLAN_TABLEは、EXPLAIN PLAN文が実行計画について記述している行を挿入するデフォルトのサンプル出力表です。

Oracle Databaseは、SYSスキーマのグローバル一時表PLAN_TABLE$を自動的に作成し、シノニムとしてPLAN_TABLEを作成します。PLAN_TABLEに必要なすべての権限は、PUBLICに付与されています。そのため、すべてのセッションが、そのセッションの一時表領域内にPLAN_TABLEの専用プライベート・コピーを保持します。

SQLスクリプトcatplan.sqlを使用すると、グローバル一時表とPLAN_TABLEシノニムを手動で作成できます。このスクリプトの名前と位置は、使用するオペレーティング・システムによって異なります。UNIXおよびLinux上では、このスクリプトは$ORACLE_HOME/rdbms/adminディレクトリにあります。たとえば、SQL*Plusセッションを開始し、SYSDBA権限で接続し、そして次のようにスクリプトを実行します。

@$ORACLE_HOME/rdbms/admin/catplan.sql

配布メディアのSQLスクリプトの中に、サンプル出力表PLAN_TABLEの定義があります。使用する出力表は、列の名前およびデータ型がこのサンプル表と同じである必要があります。このスクリプトの一般的な名前は、utlxplan.sqlです。正確な名前および位置は、使用するオペレーティング・システムによって異なります。

関連項目:

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

6.2.1.2 EXPLAIN PLANの制限事項

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

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

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

関連項目:

「アプリケーション・トレースの実行」
「引数トラップを回避するためのガイドライン」
SQLデータ型についてさらに学習するには、『Oracle Database SQL言語リファレンス』を参照してください

6.2.2SQL文の説明: 基本ステップ

EXPLAIN PLANを使用して、SQL文の計画をPLAN_TABLEに格納します。

前提条件

このタスクでは、PLAN_TABLEという名前のサンプル出力表がスキーマに存在していると仮定しています。この表が存在していない場合は、SQLスクリプトcatplan.sqlを実行します。

EXPLAIN PLANを実行するには、次の権限が必要です。

実行計画を保持するように指定した既存の出力表に行を挿入するための権限が必要です。
出力する実行計画の適用対象であるSQL文を実行するための権限も必要です。このSQL文でビューにアクセスする場合は、このビューの基礎になっているすべての表およびビューへのアクセス権限が必要です。このビューが別のビューに基づき、さらにこの別のビューが、ある表に基づいている場合、別のビューとそのビューの基礎になっている表へのアクセス権限が必要です。

EXPLAIN PLANで作成された実行計画を検証する場合は、出力表へ問い合せる権限が必要です。

文を説明するには:

SQL*PlusまたはSQL Developerを起動して、データベースに必要な権限を持つユーザーとしてログインします。

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

次の例では、employees表の問合せの計画について説明します。

EXPLAIN PLAN FOR
  SELECT e.last_name, d.department_name, e.salary
  FROM   employees e, departments d
  WHERE  salary < 3000
  AND    e.department_id = d.department_id
  ORDER BY salary DESC;

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

次の例では、DBMS_XPLAN.DISPLAY関数を使用します。
```
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
```

計画出力を確認します。

たとえば、次の計画はハッシュ結合を示しています。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
Plan hash value: 3556827125

------------------------------------------------------------------------------
| Id | Operation           | Name        |Rows | Bytes |Cost (%CPU)| Time    |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |             |   4 |   124 |   5  (20)| 00:00:01 |
|  1 |  SORT ORDER BY      |             |   4 |   124 |   5  (20)| 00:00:01 |
|* 2 |   HASH JOIN         |             |   4 |   124 |   4   (0)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL| EMPLOYEES   |   4 |    60 |   2   (0)| 00:00:01 |
|  4 |    TABLE ACCESS FULL| DEPARTMENTS |  27 |   432 |   2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   3 - filter("SALARY"<3000)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) INTERNAL_FUNCTION("E"."SALARY")[22],
       "E"."LAST_NAME"[VARCHAR2,25], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
   2 - (#keys=1) "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22],
       "D"."DEPARTMENT_NAME"[VARCHAR2,30], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
   3 - "E"."LAST_NAME"[VARCHAR2,25], "SALARY"[NUMBER,22],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   4 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]

Note
-----
   - this is an adaptive plan

計画操作によって、子からのデータが要求されます。EXPLAIN PLAN出力の実行順序は次のとおりです。

実行は、子のない最初の操作から始まります。上の例では、EMPLOYEES (ID 3)の全体スキャンです。
EMPLOYEES は、そのデータを親(ID 2)に返します。
その後、実行はハッシュ結合の次の子に進み、DEPARTMENTS (ID 4)の全体スキャンを実行します。
DEPARTMENTSには子がないため、データを親(ID 2)に返します。
ハッシュ結合では、2つの表の行が結合され、SORT ORDER BY (ID 1)に渡されます。
最後に、SELECTはデータをクライアントに返します。

ノート:

この例では、追加の結合などの操作がさらに含まれている場合、実行は各操作の同じパターンに従ってステップ5から計画の最後(SELECTがクライアントにデータを返す最後のステップ)まで続きます。

ここで説明するEXPLAIN PLAN出力のステップは、データベースによっては異なる場合があります。これは、オプティマイザがデータベース構成に応じて異なるEXECUTION PLANを選択する可能性があるためです。

関連項目:

「PLAN_TABLEについて」
「PLAN_TABLE出力の表示について」
EXPLAIN PLANの構文およびセマンティクスについては、『Oracle Database SQL言語リファレンス』を参照してください
実行計画の読取り方法。このOracleブログ記事では、EXECUTION PLANの読取り方法について説明しますが、同じ実行順序がEXPLAIN PLANに適用されるため、両方のタイプの計画のプロセスをよりよく理解できるようになります。

6.2.3EXPLAIN PLANでの文IDの指定: 例

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

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

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

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

6.2.4EXPLAIN PLAN出力の別の場所の指定: 例

EXPLAIN PLANのINTO句で、出力を格納する別の表を指定します。

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

CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$

次の文は、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;

関連項目:

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

6.2.5 CONTAINERS問合せに対するEXPLAIN PLANの出力: 例

CONTAINERS句を使用すると、ユーザーが作成した表およびビューと、Oracleが提供する表およびビューの両方を問い合せることができます。すべてのコンテナ間でユーザーが作成した表およびビューを問い合せることができます。

次の例に、CONTAINERS句を使用した問合せのEXPLAIN PLANの出力を示します。

SQL> explain plan for select con_id, count(*) from containers(sys.dba_tables) where con_id < 10 group by con_id order by con_id;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 891225627

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |   234K|  2970K|   145 (100)| 00:00:01 |       |       |
|   1 |  PX COORDINATOR                  |            |       |       |            |          |       |       |
|   2 |   PX SEND QC (ORDER)             | :TQ10001   |   234K|  2970K|   145 (100)| 00:00:01 |       |       |
|   3 |    SORT GROUP BY                 |            |   234K|  2970K|   145 (100)| 00:00:01 |       |       |
|   4 |     PX RECEIVE                   |            |   234K|  2970K|   145 (100)| 00:00:01 |       |       |
|   5 |      PX SEND RANGE               | :TQ10000   |   234K|  2970K|   145 (100)| 00:00:01 |       |       |
|   6 |       HASH GROUP BY              |            |   234K|  2970K|   145 (100)| 00:00:01 |       |       |
|   7 |        PX PARTITION LIST ITERATOR|            |   234K|  2970K|   139 (100)| 00:00:01 |     1 |     9 |
|   8 |         CONTAINERS FULL          | DBA_TABLES |   234K|  2970K|   139 (100)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------

15 rows selected.

この計画の行8では、CONTAINERSは値CONTAINERS FULLでOperation列に表示されます。同じ行のName列には、CONTAINERSへの引数が示されます。

デフォルトのパーティション化

CONTAINERS句を使用する問合せは、デフォルトでパーティション化されます。計画の行7で、Operation列のPX PARTITION LIST ITERATORは、問合せがパーティション化されていることを示しています。コンテナの反復は、このパーティション・イテレータに実装されます。同じ行で、PstartとPstopの値1および9は、問合せのcon_id < 10述語から派生します。

デフォルトの並列処理

CONTAINERS句を使用する問合せは、デフォルトでパラレル実行サーバーを使用します。前述の計画の行1で、Operation列のPX COORDINATORはパラレル実行サーバーが使用されることを示しています。各コンテナはパラレル実行プロセス(P00*)に割り当てられます。パラレル実行プロセスが、問合せEXECUTION PLANのCONTAINERS FULLに対応する部分を実行すると、プロセスは作業対象として割り当てられたコンテナに切り替わります。再帰的SQL文を実行して、ベース・オブジェクトから行を取得します。

6.3 実行計画の表示

実行計画を表示する最も簡単な方法は、DBMS_XPLAN表示関数またはV$ビューを使用することです。

6.3.1 PLAN_TABLE出力の表示について

PLAN TABLE出力を表示するには、SQLスクリプトまたはDBMS_XPLANパッケージを使用できます。

計画をEXPLAINした後、Oracle Databaseから提供される次のSQLスクリプトまたはPL/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'));
```
utlxpls.sql

このスクリプトは、シリアル処理のためのPLAN TABLE出力を表示します
utlxplp.sql

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

関連項目:

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

6.3.1.1 DBMS_XPLAN表示関数

DBMS_XPLAN表示関数を使用すると計画を表示できます。

表示関数は、PLAN TABLE出力を表示するオプションを受け入れます。次を指定できます。

PLAN_TABLEとは別の表を使用している場合のPLAN TABLE名
EXPLAIN PLANに文の識別子を設定した場合の文の識別子
詳細レベルを決定するフォーマット・オプション: BASIC、SERIAL、TYPICAL、ALLおよび場合によってはADAPTIVE

表6-1 DBMS_XPLAN表示関数

表示関数	ノート
`DISPLAY`	このテーブル・ファンクションは、PLAN TABLEの内容を表示します。また、この表の列の名前がPLAN TABLE(統計情報が含まれている場合は`V$SQL_PLAN_STATISTICS_ALL`)の列の名前と同じである場合にかぎり、このテーブル・ファンクションを使用して、表に格納されている計画を(統計情報を含めて、または統計情報を含めずに)表示できます。指定した表に述語を適用して、表示する計画の行を選択できます。 `format`パラメータでは、計画のレベルを制御します。有効な値は、`BASIC`、`TYPICAL`、`SERIAL`および`ALL`です。
`DISPLAY_AWR`	このテーブル・ファンクションは、AWR内に格納されている実行計画のコンテンツを表示します。 `format`パラメータでは、計画のレベルを制御します。有効な値は、`BASIC`、`TYPICAL`、`SERIAL`および`ALL`です。
`DISPLAY_CURSOR`	このテーブル・ファンクションは、カーソル・キャッシュにロードされている任意のカーソルの実行計画を表示します。実行計画に加えて、様々な計画統計(I/O、メモリー、タイミングなど)をレポートできます(`V$SQL_PLAN_STATISTICS_ALL VIEWS`に基づく)。 `format`パラメータでは、計画のレベルを制御します。有効な値は、`BASIC`、`TYPICAL`、`SERIAL`および`ALL`および`ADAPTIVE`です。`ADAPTIVE`を指定すると、出力には次のものが含まれます。最終計画。実行が完了していない場合、出力には現在の計画が示されます。このセクションには、計画に影響を及ぼす実行時の最適化に関するノートも含まれます。推奨計画。レポート作成モードでは、出力には、実行統計に基づいて選択される計画が含まれます。動的計画。出力には、オプティマイザによって選択されたデフォルトの計画とは異なる計画の部分がまとめられます。再最適化。出力には再最適化により以降の実行で選択される予定の計画が表示されます。
`DISPLAY_PLAN`	このテーブル・ファンクションは、`CLOB`出力タイプの様々な形式でPLAN TABLEの内容を表示します。 `format`パラメータでは、計画のレベルを制御します。有効な値は、`BASIC`、`TYPICAL`、`SERIAL`および`ALL`および`ADAPTIVE`です。`ADAPTIVE`を指定すると、出力にはデフォルトの計画が含まれます。動的なサブプランそれぞれについて、計画では、置き換えられる可能性のある元の行ソースおよびそれらを置き換える行ソースのリストが示されます。 `format`引数にアウトライン表示が指定されると、この関数は動的サブプラン内の各オプションに対するヒントを表示します。計画が適応問合せ計画でない場合、関数ではデフォルト計画が表示されます。`ADAPTIVE`を指定しないと、計画はそのまま表示されますが、動的な行ソースがあれば、Noteセクション内の追加のコメントに示されます。
`DISPLAY_SQL_PLAN_BASELINE`	このテーブル・ファンクションは、SQLプラン・ベースラインの指定したSQLハンドルの1つ以上の実行計画を表示します。このファンクションは、計画ベースラインに保存された計画の情報を使用して計画の詳細を表示します。SQL管理ベースに格納されている`plan_id`は、生成された計画の`plan_id`と一致しないことがあります。格納された`plan_id`と生成された`plan_id`の不一致は、生成された計画が再生成不可能な計画であることを意味します。このような計画は無効とみなされ、SQLコンパイル時にオプティマイザによってバイパスされます。
`DISPLAY_SQLSET`	このテーブル・ファンクションは、SQLチューニング・セットに格納されている指定した文の実行計画を表示します。 `format`パラメータでは、計画のレベルを制御します。有効な値は、`BASIC`、`TYPICAL`、`SERIAL`および`ALL`です。

関連項目:

DBMS_XPLAN表示関数についてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照

6.3.1.2 計画関連のビュー

動的パフォーマンス・ビューとデータ・ディクショナリ・ビューを問い合せることで、実行計画に関する情報を取得できます。

表6-2 実行計画のビュー

ビュー	説明
`V$SQL`	カーソルの統計をリストして、入力した元のSQLテキストの子ごとに1行ずつ格納します。 Oracle Database 19c以降の`V$SQL.QUARANTINED`は、文によって消費されるリソースが多すぎるために、Resource Managerが文を終了したかどうかを示します。Oracle Databaseは、隔離された計画を記録およびマーキングし、そうした計画を使用した文が実行されないようにします。`AVOIDED_EXECUTIONS`列は、隔離された文のために試行されたが防止された実行の回数を示します。
`V$SQL_SHARED_CURSOR`	特定の子カーソルが既存の子カーソルと共有されない理由を示します。それぞれの列は、カーソルが共有されない具体的な理由を示します。 `USE_FEEDBACK_STATS`列には、再最適化のために子カーソルが一致していないかどうかが示されます。
`V$SQL_PLAN`	共有SQL領域に保存された文ごとの計画を格納します。このビューの定義は、`PLAN_TABLE`と同様です。このビューには、すべての最終計画に出現するすべての行のスーパーセットが含まれます。`PLAN_LINE_ID`には連番が振られますが、単一の最終計画では、IDが連番にならない場合もあります。 `EXPLAIN PLAN`のかわりに、`V$SQL_PLAN`を問い合せて計画を表示することもできます。`V$SQL_PLAN`が`EXPLAIN PLAN`よりも優れている点は、特定の文の実行に使用されたコンパイル環境を知らなくてもかまわないということです。`EXPLAIN PLAN`の場合は、文の実行時に同じ計画を取得するために同一環境をセットアップする必要があります。
`V$SQL_PLAN_STATISTICS`	出力行数や経過時間など、計画に含まれる操作ごとに実際の実行統計を示します。出力行数を除き、すべての統計は累積されます。たとえば、結合操作の統計には、2つの入力の統計も含まれます。`V$SQL_PLAN_STATISTICS`の統計は、`STATISTICS_LEVEL`初期化パラメータを`ALL`に設定してコンパイルされたカーソルに使用できます。
`V$SQL_PLAN_STATISTICS_ALL`	(ソートまたはハッシュ結合に)SQLメモリーを使用する行ソースのメモリー使用量統計が含まれます。このビューは、`V$SQL_PLAN`内の情報を`V$SQL_PLAN_STATISTICS`および`V$SQL_WORKAREA`からの実行統計と連結します。 `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.3.2 実行計画の表示: 基本ステップ

DBMS_XPLAN.DISPLAY関数は、説明された計画を表示する簡単な方法です。

デフォルトでは、DISPLAY関数はTYPICALのフォーマット設定を使用します。この場合は、計画に関する最も一般的な情報(操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト)が表示されます。プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。

実行計画を表示するには:

SQL*PlusまたはSQL Developerを起動して、計画を説明したセッションにログインします。
計画を説明します。
DBMS_XPLAN.DISPLAYを使用してPLAN_TABLEを問い合せます。

次のように問合せを指定します。
```
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY);
```
または、statement_idパラメータを使用して文IDを指定します。
```
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'statement_id));
```

例6-2 文ID ex_plan1のEXPLAIN PLAN

この例では、文ID ex_plan1を使用するemployeesの問合せを説明して、PLAN_TABLEを問い合せます。

EXPLAIN PLAN 
  SET statement_id = 'ex_plan1' FOR
  SELECT phone_number 
  FROM   employees
  WHERE  phone_number LIKE '650%';

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(statement_id => 'ex_plan1'));

出力例は次のように表示されます。

Plan hash value: 1445457117

---------------------------------------------------------------------------
|Id | Operation         | Name      |Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|  0| SELECT STATEMENT  |           |   1 |    15 |     2   (0)| 00:00:01 |
|* 1|  TABLE ACCESS FULL| EMPLOYEES |   1 |    15 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PHONE_NUMBER" LIKE '650%')

例6-3 文ID ex_plan2のEXPLAIN PLAN

この例では、文ID ex_plan2を使用するemployeesの問合せを説明し、BASICフォーマットを使用して計画を表示します。

EXPLAIN PLAN 
  SET statement_id = 'ex_plan2' FOR
  SELECT last_name 
  FROM   employees
  WHERE  last_name LIKE 'Pe%';

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));

出力例は次のように表示されます。

----------------------------------------
| Id  | Operation        | Name        |
----------------------------------------
|   0 | SELECT STATEMENT |             |
|   1 |  INDEX RANGE SCAN| EMP_NAME_IX |
----------------------------------------

関連項目:

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

6.3.3 適応問合せ計画の表示: チュートリアル

適応オプティマイザは、実行時の統計に基づいて計画を適応させることを可能にするオプティマイザの機能です。すべての適応メカニズムにより、デフォルト・プランと異なる文の最終プランを実行できます。

適応問合せ計画は、現在の文の実行中にサブプランの中から選択されます。対照的に、自動再最適化では、現在の文の実行後に行われる自動再最適化の実行時にのみ計画が変更されます。

計画のNotesセクションのコメントに基づいて、データベースでSQL文の適応問合せ最適化が使用されたかどうかを判断できます。コメントには、行ソースが動的かどうか、または自動再最適化が計画に適用されたかどうかが示されます。

前提条件

このチュートリアルでは、次のことが前提となっています。

STATISTICS_LEVEL初期化パラメータがALLに設定されています。
データベースでは、適応実行にデフォルト設定が使用されています。

ユーザーoeとして、次の別々の問合せを発行します。

SELECT o.order_id, v.product_name
FROM   orders o,
       (  SELECT order_id, product_name
          FROM   order_items o, product_information p
          WHERE  p.product_id = o.product_id
          AND    list_price < 50
          AND    min_price < 40  ) v
WHERE  o.order_id = v.order_id

SELECT product_name
FROM   order_items o, product_information p  
WHERE  o.unit_price = 15 
AND    quantity > 1
AND    p.product_id = o.product_id

各問合せを実行する前に、DBMS_XPLAN.DISPLAY_PLANを問い合せてデフォルトの計画(つまり、適応メカニズムを適用する前にオプティマイザによって選択される計画)を確認します。
各問合せの実行後、DBMS_XPLAN.DISPLAY_CURSORを問い合せて最終計画と適応問合せ計画を確認します。
SYSにより、oeに次の権限が付与されています。
- GRANT SELECT ON V_$SESSION TO oe
- GRANT SELECT ON V_$SQL TO oe
- GRANT SELECT ON V_$SQL_PLAN TO oe
- GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO oe

適応最適化の結果を確認するには:

SQL*Plusを起動し、データベースにユーザーoeとして接続します。

ordersを問い合せます。

たとえば、次の文を使用します。

SELECT o.order_id, v.product_name
FROM   orders o,
       (  SELECT order_id, product_name
          FROM   order_items o, product_information p
          WHERE  p.product_id = o.product_id
          AND    list_price < 50
          AND    min_price < 40  ) v
WHERE  o.order_id = v.order_id;

カーソル内に計画を表示します。

たとえば、次のコマンドを実行します。

SET LINESIZE 165
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));

次のサンプル出力はページに収まるようにあらかじめ折り返されています。この計画では、オプティマイザは、ネステッド・ループ結合を選択します。元のオプティマイザの見積りは、E-Rows列に示されています。一方、実行中に収集された実際の統計は、A-Rows列に示されています。MERGE JOIN操作では、見積られた行数と実際の行数の差異は大きなものになります。

--------------------------------------------------------------------------------------------
|Id| Operation             | Name          |Start|E-Rows|A-Rows|A-Time|Buff|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT      |                |   1|   | 269|00:00:00.09|1338|    |    |     |
| 1|  NESTED LOOPS         |                |   1|  1| 269|00:00:00.09|1338|    |    |     |
| 2|   MERGE JOIN CARTESIAN|                |   1|  4|9135|00:00:00.03|  33|    |    |     |
|*3|    TABLE ACCESS FULL  |PRODUCT_INFORMAT|   1|  1|  87|00:00:00.01|  32|    |    |     |
| 4|    BUFFER SORT        |                |  87|105|9135|00:00:00.01|   1|4096|4096|1/0/0|
| 5|     INDEX FULL SCAN   | ORDER_PK       |   1|105| 105|00:00:00.01|   1|    |    |     |
|*6|   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK |9135|  1| 269|00:00:00.03|1305|    |    |     |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")

ステップ2で実行したものと同じordersの問合せを実行します。

ステップ3で実行したものと同じSELECT文を使用して、カーソル内に実行計画を表示します。

次の例は、オプティマイザがハッシュ結合を使用する別の計画を選択したことを示しています。Noteセクションには、オプティマイザが統計フィードバックを使用して、問合せの2回目の実行に対するコストの見積りを調整したことが示されています。これは自動再最適化のよい例です。

--------------------------------------------------------------------------------------------
|Id| Operation              |Name    |Start|E-Rows|A-Rows|A-Time|Buff|Reads|OMem|1Mem|O/1/M|
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT       |               | 1 |   |269|00:00:00.02|60|1|     |     |     |
| 1|  NESTED LOOPS          |               | 1 |269|269|00:00:00.02|60|1|     |     |     |
|*2|   HASH JOIN            |               | 1 |313|269|00:00:00.02|39|1|1000K|1000K|1/0/0|
|*3|    TABLE ACCESS FULL   |PRODUCT_INFORMA| 1 | 87| 87|00:00:00.01|15|0|     |     |     |
| 4|    INDEX FAST FULL SCAN|ORDER_ITEMS_UK | 1 |665|665|00:00:00.01|24|1|     |     |     |
|*5|   INDEX UNIQUE SCAN    |ORDER_PK       |269|  1|269|00:00:00.01|21|0|     |     |     |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")
 
Note
-----
   - statistics feedback used for this statement

V$SQLを問い合せて、パフォーマンスが改善されていることを確認します。

次の問合せは、2つの文のパフォーマンスを示しています(出力例も示します)。
```
SELECT CHILD_NUMBER, CPU_TIME, ELAPSED_TIME, BUFFER_GETS
FROM   V$SQL
WHERE  SQL_ID = 'gm2npz344xqn8';
 
CHILD_NUMBER   CPU_TIME ELAPSED_TIME BUFFER_GETS
------------ ---------- ------------ -----------
           0      92006       131485        1831
           1      12000        24156          60
```
実行された2つ目の文(子番号1)では、統計フィードバックが使用されました。CPU時間、経過時間およびバッファ取得はいずれも、大幅に低下しています。

order_itemsの問合せに対する計画をEXPLAINします。

たとえば、次の文を使用します。

EXPLAIN PLAN FOR
  SELECT product_name 
  FROM   order_items o, product_information p  
  WHERE  o.unit_price = 15
  AND    quantity > 1  
  AND    p.product_id = o.product_id

PLAN TABLE内に計画を表示します。

たとえば、次の文を実行します。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

出力例は次のように表示されます。

-------------------------------------------------------------------------------
|Id| Operation                 | Name             |Rows|Bytes|Cost (%CPU)|Time|
-------------------------------------------------------------------------------
| 0| SELECT STATEMENT             |                      |4|128|7 (0)|00:00:01|
| 1|  NESTED LOOPS                |                      | |   |     |        |
| 2|   NESTED LOOPS               |                      |4|128|7 (0)|00:00:01|
|*3|    TABLE ACCESS FULL         |ORDER_ITEMS           |4|48 |3 (0)|00:00:01|
|*4|    INDEX UNIQUE SCAN         |PRODUCT_INFORMATION_PK|1|   |0 (0)|00:00:01|
| 5|   TABLE ACCESS BY INDEX ROWID|PRODUCT_INFORMATION   |1|20 |1 (0)|00:00:01|
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("O"."UNIT_PRICE"=15 AND "QUANTITY">1)
   4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")

この計画では、オプティマイザは、ネステッド・ループ結合を選択します。

以前にEXPLAINした問合せを実行します。

たとえば、次の文を使用します。

SELECT product_name 
FROM   order_items o, product_information p  
WHERE  o.unit_price = 15
AND    quantity > 1  
AND    p.product_id = o.product_id

カーソル内に計画を表示します。

たとえば、次のコマンドを実行します。

SET LINESIZE 165
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'+ADAPTIVE'));

出力例は次のように表示されます。実行時(ステップ4)に収集された統計に基づいて、オプティマイザは、ネステッド・ループ結合ではなく、ハッシュ結合を選択します。ダッシュ(-)は、オプティマイザによって考慮されたが最終的に選択されなかった、ネステッド・ループ計画におけるステップを表します。スイッチは、適応問合せ計画機能を示しています。

-------------------------------------------------------------------------------
|Id | Operation                     | Name     |Rows|Bytes|Cost(%CPU)|Time    |
-------------------------------------------------------------------------------
|  0| SELECT STATEMENT              |                     |4|128|7(0)|00:00:01|
| *1|  HASH JOIN                    |                     |4|128|7(0)|00:00:01|
|- 2|   NESTED LOOPS                |                     | |   |    |        |
|- 3|    NESTED LOOPS               |                     | |128|7(0)|00:00:01|
|- 4|     STATISTICS COLLECTOR      |                     | |   |    |        |
| *5|      TABLE ACCESS FULL        | ORDER_ITEMS         |4| 48|3(0)|00:00:01|
|-*6|     INDEX UNIQUE SCAN         | PRODUCT_INFORMATI_PK|1|   |0(0)|00:00:01|
|- 7|    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION |1| 20|1(0)|00:00:01|
|  8|   TABLE ACCESS FULL           | PRODUCT_INFORMATION |1| 20|1(0)|00:00:01|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   5 - filter("O"."UNIT_PRICE"=15 AND "QUANTITY">1)
   6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

関連項目:

「適応問合せ計画」
「表 6-1」
「適応最適化の制御」
STATISTICS_LEVEL初期化パラメータについて学習するには、『Oracle Databaseリファレンス』を参照してください。
DBMS_XPLANについてさらに学習するには、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

6.3.4 実行計画の表示: 例

ここでは、実行計画の様々な表示方法の例を示します。

6.3.4.1 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の場合には有効ではありません。

6.3.4.2 パラレル実行計画の表示: 例

パラレル問合せの計画は、重要な点でシリアル問合せの計画と異なります。

6.3.4.2.1 EXPLAIN PLANおよびパラレル問合せについて

パラレル問合せのチューニングは、パラレルでない問合せのチューニングの場合と同様に駆動表を選択することにより、開始されます。ただし、選択を管理するルールは異なります。

シリアルの場合は、制限条件を適用した後に最も少ない行が生成される駆動表が最適です。データベースは、一意でない索引を使用して少数の行を大きな表に結合します。

たとえば、customer、accountおよびtransactionで構成された表階層の場合について考えます。

図6-1 表階層

「図 6-1 表階層」の説明

この例では、customerが最も小さな表で、transactionが最も大きな表です。通常のOLTP問合せでは、特定の顧客のアカウントに関する取引情報を取得します。問合せはcustomer表から駆動されます。目標は論理I/Oを最少化することで、これにより、通常、物理I/OやCPUタイムを含むその他の重要なリソースも最少化されます。

パラレル問合せの場合は、通常では最も大きな表が駆動表になります。この場合はパラレル問合せを使用するのは効率的ではありません。各表からアクセスされる行がごくわずかであるためです。ここで、前月に特定のタイプの取引を持つすべての顧客を識別する必要が生じた場合を考えてみます。customer表には制限条件がないため、問合せはtransaction表から行うほうが効率的です。データベースは、transaction表から取り出した行をaccount表に結合し、最終的には結果セットをcustomer表に結合します。この場合、accountおよびcustomer表で使用される索引は、通常、最初の問合せで使用される一意でない索引ではなく、選択性の高い主キーまたは一意の索引になります。transaction表は大きく、列に選択性がないため、transaction表から駆動されるパラレル問合せを使用したほうが有効です。

パラレル操作には次のものがあります。

PARALLEL_TO_PARALLEL
PARALLEL_TO_SERIAL

PARALLEL_TO_SERIAL操作は、パラレル操作からの行が問合せコーディネータによって使用される場合、常に発生するステップです。この問合せでは発生しない他の種類の操作には、SERIAL操作があります。このような操作が発生した場合は、これらもボトルネックになる可能性があるため、パフォーマンスの改善のためにパラレル操作を検討します。
PARALLEL_FROM_SERIAL
PARALLEL_TO_PARALLEL

各ステップのワークロードがほぼ同じである場合、通常は、PARALLEL_TO_PARALLEL操作によって最適なパフォーマンスが得られます。
PARALLEL_COMBINED_WITH_CHILD
PARALLEL_COMBINED_WITH_PARENT

PARALLEL_COMBINED_WITH_PARENT操作は、ステップを親ステップと同時に実行する場合に発生します。

パラレル・ステップで多数の行が生成される場合、行の生成される速さにQCの処理が追いつかない場合があります。この状況を改善する方法はほとんどありません。

関連項目:

「PLAN_TABLE列」のOTHER_TAG列

6.3.4.2.2 EXPLAIN PLANによるパラレル問合せの表示: 例

EXPLAIN PLANをパラレル問合せとともに使用すると、データベースは1つのパラレル計画をコンパイルおよび実行します。この計画は、QC計画にパラレル・サポートに固有の行ソースを割り当てることで、シリアル計画から導出されます。

2つのパラレル実行サーバー・セットPQモデルで要求される、表キューの行ソース(PX SendおよびPX Receive)、グラニュル・イテレータおよびバッファ・ソートは、パラレル計画に直接挿入されます。この計画は、パラレルで実行された場合はすべてのパラレル実行サーバーで、またシリアルで実行された場合はすべてのQCで、同じ計画となります。

例6-4 パラレル問合せのEXPLAIN PLAN

次の単純な例は、パラレル問合せのEXPLAIN PLANを示しています。

CREATE TABLE emp2 AS SELECT * FROM employees;

ALTER TABLE emp2 PARALLEL 2;

EXPLAIN PLAN FOR
  SELECT SUM(salary) 
  FROM   emp2 
  GROUP BY department_id;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------------
|Id | Operation              | Name   |Rows| Bytes |Cost %CPU| TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------
|0| SELECT STATEMENT         |        |107| 2782 | 3 (34) |       |      |          |
|1|  PX COORDINATOR          |        |   |      |        |       |      |          |
|2|   PX SEND QC (RANDOM)    |:TQ10001|107| 2782 | 3 (34) | Q1,01 | P->S |QC (RAND) |
|3|    HASH GROUP BY         |        |107| 2782 | 3 (34) | Q1,01 | PCWP |          |
|4|     PX RECEIVE           |        |107| 2782 | 3 (34) | Q1,01 | PCWP |          |
|5|      PX SEND HASH        |:TQ10000|107| 2782 | 3 (34) | Q1,00 | P->P |HASH      |
|6|       HASH GROUP BY      |        |107| 2782 | 3 (34) | Q1,00 | PCWP |          |
|7|        PX BLOCK ITERATOR |        |107| 2782 | 2 (0)  | Q1,00 | PCWP |          |
|8|         TABLE ACCESS FULL|EMP2    |107| 2782 | 2 (0)  | Q1,00 | PCWP |          |
-------------------------------------------------------------------------------------

パラレル実行サーバーの1つのセットがパラレルにEMP2をスキャンし、2つ目のセットは、GROUP BY操作に対する集計を実行します。PX BLOCK ITERATOR行ソースは、EMP2表が複数のピースに分割されて、スキャンのワークロードがパラレル実行サーバー間で分けられることを表します。PX SENDおよびPX RECEIVE行ソースは、2つのパラレル実行サーバー・セットをパラレル・スキャンからの行フローとして接続するパイプを表し、HASH表キューを介して再びパーティション化されてから、上位スレーブ・セットにより読み取られて集計されます。PX SEND QC行ソースは、QCにランダム(RAND)な順序で送信される集計値を表します。PX COORDINATOR行ソースは、計画ツリーで下に表示されるパラレル計画を制御しスケジュールするQC(問合せコーディネータ)を表します。

6.3.4.3 ビットマップ索引計画の表示: 例

ビットマップ索引を使用する索引行ソースは、索引のタイプを示す語BITMAPとともにEXPLAIN PLAN出力に表示されます。

例6-5 ビットマップ検索によるEXPLAIN PLAN

この例では、述語c1=2によってビットマップが生成され、そこから減算が行われます。このビットマップから、c2=6に対応するビットマップ内のビットが減算されます。同様に、c2 IS NULLに対応するビットマップ内のビットが減算され、この計画の中に2つのMINUS行ソースがある理由がわかります。NULL減算は、NOT NULL制約が列に設定されていないかぎり、意味上の正確さを保つために必要です。TO ROWIDSオプションは、表アクセスに必要な行IDを生成します。

ノート:

ビットマップ結合索引を使用した問合せは、ビットマップ結合索引のアクセス・パスを指示します。ビットマップ結合索引の操作は、ビットマップ索引と同じです。

EXPLAIN PLAN FOR  SELECT * 
  FROM   t
  WHERE  c1 = 2 
  AND    c2 <> 6 
  OR     c3 BETWEEN 10 AND 20;

SELECT STATEMENT
   TABLE ACCESS T BY INDEX ROWID
      BITMAP CONVERSION TO ROWID
         BITMAP OR
            BITMAP MINUS
               BITMAP MINUS
                  BITMAP INDEX C1_IND SINGLE VALUE
                  BITMAP INDEX C2_IND SINGLE VALUE
               BITMAP INDEX C2_IND SINGLE VALUE
            BITMAP MERGE
               BITMAP INDEX C3_IND RANGE SCAN

6.3.4.4 結果キャッシュ計画の表示: 例

問合せにresult_cacheヒントが含まれる場合、ResultCache演算子が実行計画に挿入されます。

たとえば、次の問合せを考えてみます。

SELECT /*+ result_cache */ deptno, avg(sal) 
FROM   emp 
GROUP BY deptno;

次のコマンドを使用して、この問合せに対するEXPLAIN PLANを表示できます。

EXPLAIN PLAN FOR 
  SELECT /*+ result_cache */ deptno, avg(sal) 
  FROM emp 
  GROUP BY deptno;

SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY());

この問合せのEXPLAIN PLAN出力は、次のようになります。

--------------------------------------------------------------------------------
|Id| Operation          | Name                     |Rows|Bytes|Cost(%CPU)|Time |
--------------------------------------------------------------------------------
|0| SELECT STATEMENT    |                          | 11 | 77 | 4 (25)| 00:00:01|
|1|  RESULT CACHE       |b06ppfz9pxzstbttpbqyqnfbmy|    |    |       |         |
|2|   HASH GROUP BY     |                          | 11 | 77 | 4 (25)| 00:00:01|
|3|    TABLE ACCESS FULL| EMP                      |107 | 749| 3 (0) | 00:00:01|
--------------------------------------------------------------------------------

このEXPLAIN PLANでは、ResultCache演算子はそのCacheId(b06ppfz9pxzstbttpbqyqnfbmy)によって識別されます。このCacheIdを使用して、V$RESULT_CACHE_OBJECTSビューに対して問合せを実行できます。

6.3.4.5 パーティション・オブジェクトの計画の表示: 例

EXPLAIN PLANを使用して、特定の問合せのパーティション・オブジェクトへのOracle Databaseのアクセス方法を確認します。

プルーニング後にアクセスされたパーティションは、PARTITION START列とPARTITION STOP列に表示されます。レンジ・パーティションの行ソース名は、PARTITION RANGEです。ハッシュ・パーティションの場合、行ソース名はPARTITION HASHです。

結合されるいずれかの表のPLAN TABLEのDISTRIBUTION列にPARTITION(KEY)が存在する場合、結合はパーシャル・パーティション・ワイズ結合を使用して実装されます。パーシャル・パーティション・ワイズ結合が可能なのは、結合される表のいずれかが結合列でパーティション化されており、かつ、表がパラレル化されている場合です。

EXPLAIN PLAN出力の結合行ソースの前にパーティション行ソースがある場合、結合はフル・パーティション・ワイズ結合を使用して実装されます。フル・パーティション・ワイズ結合が可能なのは、両方の結合表がそれぞれの結合列で同一レベル・パーティション化されている場合のみです。次に、いくつかの種類のパーティションに対する実行計画の例を示します。

6.3.4.5.1 EXPLAIN PLANによるレンジ・パーティション化およびハッシュ・パーティション化の表示: 例

この例では、hire_dateに基づいてレンジでパーティション化されているemp_range表を使用してプルーニングについて説明します。

Oracle Databaseサンプル・スキーマの表employeesおよびdepartmentsが存在することを想定しています。

CREATE TABLE emp_range 
PARTITION BY RANGE(hire_date) 
( 
  PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1992','DD-MON-YYYY')),
  PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1994','DD-MON-YYYY')),
  PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1996','DD-MON-YYYY')),
  PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1998','DD-MON-YYYY')),
  PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY')) 
) 
AS SELECT * FROM employees;

最初の例では、次の文を検討します。

EXPLAIN PLAN FOR 
  SELECT * FROM emp_range;

次のような結果が表示されます。

--------------------------------------------------------------------
|Id| Operation           | Name      |Rows| Bytes|Cost|Pstart|Pstop|
--------------------------------------------------------------------
| 0| SELECT STATEMENT    |           |  105| 13965 | 2 |   |       |
| 1|  PARTITION RANGE ALL|           |  105| 13965 | 2 | 1 |     5 |
| 2|   TABLE ACCESS FULL | EMP_RANGE |  105| 13965 | 2 | 1 |     5 |
--------------------------------------------------------------------

データベースでは、表アクセス行ソースに加えて、パーティション行ソースが作成されます。これが、アクセスされるパーティションのセットに対して繰り返されます。この例では、述語がプルーニングに使用されていないので、パーティション・イテレータはすべてのパーティション(ALLオプション)を対象とします。PLAN_TABLEのPARTITION_START列とPARTITION_STOP列は、1から5のすべてのパーティションへのアクセスを示します。

次の例では、次の文を検討します。

EXPLAIN PLAN FOR 
  SELECT * 
  FROM   emp_range 
  WHERE  hire_date >= TO_DATE('1-JAN-1996','DD-MON-YYYY');

-----------------------------------------------------------------------
| Id | Operation                | Name   |Rows|Bytes|Cost|Pstart|Pstop|
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT         |         | 3 | 399 |   2 |     |     |
|  1 |  PARTITION RANGE ITERATOR|         | 3 | 399 |   2 |   4 |   5 |
| *2 |   TABLE ACCESS FULL      |EMP_RANGE| 3 | 399 |   2 |   4 |   5 |
-----------------------------------------------------------------------

前の例では、hire_dateの述語を使用してその他のパーティションをプルーニングするため、パーティション行ソースはパーティション4から5を反復します。

最後に、次の文を検討します。

EXPLAIN PLAN FOR 
  SELECT *
  FROM   emp_range
  WHERE  hire_date < TO_DATE('1-JAN-1992','DD-MON-YYYY'); 

-----------------------------------------------------------------------
| Id  | Operation            | Name      |Rows|Bytes|Cost|Pstart|Pstop|
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |    1 |   133 | 2 |   |   |
|   1 |  PARTITION RANGE SINGLE|           |    1 |   133 | 2 | 1 | 1 |
|*  2 |   TABLE ACCESS FULL    | EMP_RANGE |    1 |   133 | 2 | 1 | 1 |
-----------------------------------------------------------------------

この例では、パーティション1のみがアクセスされ、それがコンパイル時に認識されます。したがって、パーティション行ソースは必要ありません。

ノート:

ハッシュ・パーティション・オブジェクトでは、パーティション行ソース名がPARTITION RANGEではなくPARTITION HASHであることを除き、同じ情報が表示されます。また、ハッシュ・パーティション化では、プルーニングが可能なのは等価述語かINリスト述語を使用している場合のみです。

6.3.4.5.2 コンポジット・パーティション・オブジェクトでのプルーニング情報: 例

Oracle Databaseでコンポジット・パーティション・オブジェクトのプルーニング情報を表示する方法を示すには、表emp_compを検討してください。この表は雇用日のレンジでパーティション化され、部門番号のハッシュでサプパーティション化されています。

CREATE TABLE emp_comp PARTITION BY RANGE(hire_date) 
      SUBPARTITION BY HASH(department_id) SUBPARTITIONS 3 
( 
PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1992','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1994','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1996','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1998','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY')) 
) 
AS SELECT * FROM employees;

最初の例では、次の文を検討します。

EXPLAIN PLAN FOR 
  SELECT * FROM emp_comp; 

-----------------------------------------------------------------------
|Id| Operation           | Name     | Rows  | Bytes |Cost|Pstart|Pstop|
-----------------------------------------------------------------------
| 0| SELECT STATEMENT    |          | 10120 |  1314K| 78 |    |       |
| 1|  PARTITION RANGE ALL|          | 10120 |  1314K| 78 |  1 |     5 |
| 2|   PARTITION HASH ALL|          | 10120 |  1314K| 78 |  1 |     3 |
| 3|    TABLE ACCESS FULL| EMP_COMP | 10120 |  1314K| 78 |  1 |    15 |
-----------------------------------------------------------------------

この例では、Oracle Databaseがコンポジット・オブジェクトの全パーティションの全サブパーティションにアクセスする場合の計画を示します。この目的を達成するために、2つのパーティション行ソースを使用します。1つはパーティションを反復するレンジ・パーティション行ソースで、もう1つはアクセスされる各パーティションのサブパーティションを反復するハッシュ・パーティション行ソースです。

次の例では、プルーニングを実行しないため、レンジ・パーティション行ソースはパーティション1から5を反復します。各パーティション内では、ハッシュ・パーティション行ソースは現在のパーティションのサブパーティション1から3を反復します。その結果、表アクセス行ソースがサブパーティション1から15にアクセスします。つまり、データベースはコンポジット・オブジェクトのすべてのサブパーティションにアクセスします。

EXPLAIN PLAN FOR 
  SELECT * 
  FROM   emp_comp 
  WHERE  hire_date = TO_DATE('15-FEB-1998', 'DD-MON-YYYY'); 

-----------------------------------------------------------------------
| Id | Operation              | Name    |Rows|Bytes |Cost|Pstart|Pstop|
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT       |          | 20 | 2660 | 17 |     |     |
|  1 |  PARTITION RANGE SINGLE|          | 20 | 2660 | 17 |   5 |   5 |
|  2 |   PARTITION HASH ALL   |          | 20 | 2660 | 17 |   1 |   3 |
|* 3 |    TABLE ACCESS FULL   | EMP_COMP | 20 | 2660 | 17 |  13 |  15 |
-----------------------------------------------------------------------

この例では、最後のパーティション5のみがアクセスされます。このパーティションはコンパイル時に認識されるので、計画では表示する必要がありません。ハッシュ・パーティション行ソースは、そのパーティション内のすべてのサブパーティションのアクセスを表示します。つまり、サブパーティション1から3が表示されることになりますが、これはemp_comp表のサブパーティション13から15に変換されます。

次の文を考えてみます。

EXPLAIN PLAN FOR 
  SELECT * 
  FROM   emp_comp 
  WHERE  department_id = 20; 

------------------------------------------------------------------------
| Id | Operation              |Name    |Rows | Bytes |Cost|Pstart|Pstop|
------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |          | 101 | 13433 | 78 |    |     |
|  1 |  PARTITION RANGE ALL   |          | 101 | 13433 | 78 |  1 |   5 |
|  2 |   PARTITION HASH SINGLE|          | 101 | 13433 | 78 |  3 |   3 |
|* 3 |    TABLE ACCESS FULL   | EMP_COMP | 101 | 13433 | 78 |    |     |
------------------------------------------------------------------------

前の例では、述語deptno=20により各パーティション内のハッシュ・ディメンションでのプルーニングが可能になります。したがって、Oracle Databaseでは単一のサブパーティションのアクセスのみが必要です。このサブパーティションの番号はコンパイル時に認識されるので、ハッシュ・パーティション行ソースは必要ありません。

最後に、次の文を検討します。

VARIABLE dno NUMBER; 
EXPLAIN PLAN FOR 
  SELECT * 
  FROM   emp_comp 
  WHERE  department_id = :dno; 

-----------------------------------------------------------------------
| Id| Operation              | Name    |Rows| Bytes |Cost|Pstart|Pstop|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT       |          | 101| 13433 | 78 |     |     |
| 1 |  PARTITION RANGE ALL   |          | 101| 13433 | 78 |   1 |   5 |
| 2 |   PARTITION HASH SINGLE|          | 101| 13433 | 78 | KEY | KEY |
|*3 |    TABLE ACCESS FULL   | EMP_COMP | 101| 13433 | 78 |     |     |
-----------------------------------------------------------------------

最後の2つの例は、deptno=20がdepartment_id = :dnoに置き換えられたこと以外は同じです。この最後の場合、サブパーティションの番号はコンパイル時には不明であり、ハッシュ・パーティション行ソースが割り当てられます。Oracle Databaseでは、各パーティション内の1つのサブパーティションにのみアクセスするため、この行ソースのオプションはSINGLEです。ステップ2では、PARTITION_STARTおよびPARTITION_STOPの両方がKEYに設定されます。この値は、Oracle Databaseでは実行時にサブパーティションの数が決まることを意味します。

6.3.4.5.3 パーシャル・パーティション・ワイズ結合の例

これらの例では、問合せオプティマイザがこの問合せのコストに基づいて異なる計画を選択する可能性があるため、PQ_DISTRIBUTEヒントにより、パーシャル・パーティション・ワイズ結合が明示的に強制されています。

例6-6 レンジ・パーティション化を使用したパーシャル・パーティション・ワイズ結合

次の例では、emp_range_didがパーティション化列department_idで結合され、パラレル化されます。dept2表がパーティション化されていないことにより、データベースでは、パーシャル・パーティション・ワイズ結合が使用可能になります。Oracle Databaseでは、結合前にdept2表を動的にパーティション化します。

CREATE TABLE dept2 AS SELECT * FROM departments;
ALTER TABLE dept2 PARALLEL 2;

CREATE TABLE emp_range_did PARTITION BY RANGE(department_id)
   (PARTITION emp_p1 VALUES LESS THAN (150),
    PARTITION emp_p5 VALUES LESS THAN (MAXVALUE) )
  AS SELECT * FROM employees;

ALTER TABLE emp_range_did PARALLEL 2;

EXPLAIN PLAN FOR 
  SELECT /*+ PQ_DISTRIBUTE(d NONE PARTITION) ORDERED */ e.last_name, 
         d.department_name 
  FROM   emp_range_did e, dept2 d 
  WHERE  e.department_id = d.department_id;

-------------------------------------------------------------------------------------------
|Id| Operation                    |Name   |Row|Byte|Cost|Pstart|Pstop|TQ|IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT             |             |284 |16188|6|  | |       |    |          |
| 1|  PX COORDINATOR              |             |    |     | |  | |       |    |          |
| 2|   PX SEND QC (RANDOM)        |:TQ10001     |284 |16188|6|  | | Q1,01 |P->S|QC (RAND) |
|*3|    HASH JOIN                 |             |284 |16188|6|  | | Q1,01 |PCWP|          |
| 4|     PX PARTITION RANGE ALL   |             |284 |7668 |2|1 |2| Q1,01 |PCWC|          |
| 5|      TABLE ACCESS FULL       |EMP_RANGE_DID|284 |7668 |2|1 |2| Q1,01 |PCWP|          |
| 6|     BUFFER SORT              |             |    |     | |  | | Q1,01 |PCWC|          |
| 7|      PX RECEIVE              |             | 21 | 630 |2|  | | Q1,01 |PCWP|          |
| 8|       PX SEND PARTITION (KEY)|:TQ10000     | 21 | 630 |2|  | |       |S->P|PART (KEY)|
| 9|        TABLE ACCESS FULL     |DEPT2        | 21 | 630 |2|  | |       |    |          |
-------------------------------------------------------------------------------------------

この実行計画は、dept2表がシリアルにスキャンされ、emp_range_didの同じパーティション化列値(department_id)を持つすべての行が、パーティション・キーを示すPART (KEY)、表キューを介して、パーシャル・パーティション・ワイズ結合を実行する同じパラレル実行サーバーに送られることを示します。

例6-7 コンポジット・パーティション化を使用したパーシャル・パーティション・ワイズ結合

次の例では、emp_compがパーティション化列で結合され、パラレル化されます。dept2表がパーティション化されていないことにより、パーシャル・パーティション・ワイズ結合が使用可能になります。データベースでは、結合前にdept2を動的にパーティション化します。

ALTER TABLE emp_comp PARALLEL 2; 

EXPLAIN PLAN FOR 
  SELECT /*+ PQ_DISTRIBUTE(d NONE PARTITION) ORDERED */ e.last_name, 
         d.department_name 
  FROM   emp_comp e, dept2 d 
  WHERE  e.department_id = d.department_id;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------------------------------
| Id| Operation              | Name  |Rows |Bytes |Cost|Pstart|Pstop|TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |        | 445 |17800| 5 |  |   |       |    |          |
| 1 |  PX COORDINATOR             |        |     |     |   |  |   |       |    |          |
| 2 |   PX SEND QC (RANDOM)       |:TQ10001| 445 |17800| 5 |  |   | Q1,01 |P->S| QC (RAND)|
|*3 |    HASH JOIN                |        | 445 |17800| 5 |  |   | Q1,01 |PCWP|          |
| 4 |     PX PARTITION RANGE ALL  |        | 107 | 1070| 3 |1 | 5 | Q1,01 |PCWC|          |
| 5 |      PX PARTITION HASH ALL  |        | 107 | 1070| 3 |1 | 3 | Q1,01 |PCWC|          |
| 6 |       TABLE ACCESS FULL     |EMP_COMP| 107 | 1070| 3 |1 | 15| Q1,01 |PCWP|          |
| 7 |     PX RECEIVE              |        |  21 |  630| 1 |  |   | Q1,01 |PCWP|          |
| 8 |      PX SEND PARTITION (KEY)|:TQ10000|  21 |  630| 1 |  |   | Q1,00 |P->P|PART (KEY)|
| 9 |       PX BLOCK ITERATOR     |        |  21 |  630| 1 |  |   | Q1,00 |PCWC|          |
|10 |        TABLE ACCESS FULL    |DEPT2   |  21 |  630| 1 |  |   | Q1,00 |PCWP|          |
-------------------------------------------------------------------------------------------

この計画は、オプティマイザが2つの列の一方からパーシャル・パーティション・ワイズ結合を選択することを示します。PX SENDのノード・タイプはPARTITION (KEY)で、PQ Distrib列にはパーティション・キーを示すテキストPART (KEY)が含まれています。これは、EMP_COMPのスキャンと結合を実行するパラレル実行サーバーに送られる結合列department_idに基づいて、dept2表が再びパーティション化されることを意味します。

6.3.4.5.4 フル・パーティション・ワイズ結合の例

この例では、emp_compとdept_hashがハッシュ・パーティション化列で結合され、フル・パーティション・ワイズ結合を使用できるようになります。

PARTITION HASH行ソースが、PLAN TABLE出力の結合行ソースの上に表示されます。

CREATE TABLE dept_hash
   PARTITION BY HASH(department_id)
   PARTITIONS 3
   PARALLEL 2
   AS SELECT * FROM departments;

EXPLAIN PLAN FOR 
  SELECT /*+ PQ_DISTRIBUTE(e NONE NONE) ORDERED */ e.last_name,
         d.department_name
  FROM   emp_comp e, dept_hash d
  WHERE  e.department_id = d.department_id;

-------------------------------------------------------------------------------------------
|Id| Operation                  | Name |Rows|Bytes|Cost|Pstart|Pstop|TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT           |          | 106 | 2544 |8|  |   |       |      |         |
| 1|  PX COORDINATOR            |          |     |      | |  |   |       |      |         |
| 2|   PX SEND QC (RANDOM)      |:TQ10000  | 106 | 2544 |8|  |   | Q1,00 | P->S |QC (RAND)|
| 3|    PX PARTITION HASH ALL   |          | 106 | 2544 |8|1 | 3 | Q1,00 | PCWC |         |
|*4|     HASH JOIN              |          | 106 | 2544 |8|  |   | Q1,00 | PCWP |         |
| 5|      PX PARTITION RANGE ALL|          | 107 | 1070 |3|1 | 5 | Q1,00 | PCWC |         |
| 6|       TABLE ACCESS FULL    |EMP_COMP  | 107 | 1070 |3|1 |15 | Q1,00 | PCWP |         |
| 7|      TABLE ACCESS FULL     |DEPT_HASH |  27 |  378 |4|1 | 3 | Q1,00 | PCWP |         |
-------------------------------------------------------------------------------------------

PX PARTITION HASH行ソースはPLAN TABLE出力で結合行ソースの上に表示されますが、PX PARTITION RANGE行ソースはemp_compのスキャンにまたがって表示されます。各パラレル実行サーバーは、emp_compのハッシュ・パーティション全体とdept_hashのパーティション全体の結合を実行します。

6.3.4.5.5 INLIST ITERATORおよびEXPLAIN PLANの例

INLIST ITERATOR操作は、索引がINリスト述語を実装する場合に、EXPLAIN PLAN出力に表示されます。

次の文を考えてみます。

SELECT * FROM emp WHERE empno IN (7876, 7900, 7902);

EXPLAIN PLAN出力は次のようになります。

OPERATION          OPTIONS           OBJECT_NAME
----------------   ---------------   -------------- 
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS       BY ROWID          EMP
INDEX              RANGE SCAN        EMP_EMPNO

INLIST ITERATOR操作は、INリスト述語内の各値に対して、計画内の次の操作を反復します。次の各項では、パーティション表およびパーティション索引で使用可能な3種類のINリスト列について説明します。

6.3.4.5.5.1 INリスト列が索引列である場合: 例

INリスト列empnoが索引列で、パーティション列ではない場合、INリスト演算子演算子は、計画で、表操作よりも前、かつパーティション操作よりも後に表示されます。

OPERATION        OPTIONS              OBJECT_NAME PARTIT_START PARTITI_STOP
---------------- ------------         ----------- ------------ ------------ 
SELECT STATEMENT 
PARTITION RANGE  ALL                             KEY(INLIST)    KEY(INLIST)
INLIST ITERATOR
TABLE ACCESS     BY LOCAL INDEX ROWID EMP          KEY(INLIST)  KEY(INLIST)
INDEX            RANGE SCAN           EMP_EMPNO    KEY(INLIST)  KEY(INLIST)

パーティションの開始キーおよび終了キーに対するKEY(INLIST)指定は、索引の開始キーおよび終了キーにINリスト述語が表示されることを指定します。

6.3.4.5.5.2 INリスト列が索引でありパーティション列である場合: 例

empnoが索引付けされている列で、それがパーティション列でもある場合、計画にはパーティション操作の前にINLIST ITERATOR操作が含まれています。

OPERATION        OPTIONS              OBJECT_NAME PARTITION_START PARTITION_STOP
---------------- ------------         ----------- --------------- --------------
SELECT STATEMENT
INLIST ITERATOR
PARTITION RANGE  ITERATOR                         KEY(INLIST)     KEY(INLIST)
TABLE ACCESS     BY LOCAL INDEX ROWID EMP         KEY(INLIST)     KEY(INLIST)
INDEX            RANGE SCAN           EMP_EMPNO   KEY(INLIST)     KEY(INLIST)

6.3.4.5.5.3 INリスト列がパーティション列である場合: 例

empnoがパーティション列で、索引が存在しない場合は、INLIST ITERATOR操作は割り当てられません。

OPERATION         OPTIONS        OBJECT_NAME   PARTITION_START   PARTITION_STOP
----------------  ------------   -----------   ---------------   --------------
SELECT STATEMENT
PARTITION RANGE   INLIST                       KEY(INLIST)       KEY(INLIST)
TABLE ACCESS      FULL           EMP           KEY(INLIST)       KEY(INLIST)

emp_empnoがビットマップ索引である場合、計画は次のとおりです。

OPERATION          OPTIONS           OBJECT_NAME
----------------   ---------------   -------------- 
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS       BY INDEX ROWID    EMP
BITMAP CONVERSION  TO ROWIDS
BITMAP INDEX       SINGLE VALUE      EMP_EMPNO

6.3.4.5.6 ドメイン索引およびEXPLAIN PLANの例

EXPLAIN PLANを使用して、ドメイン索引に対するユーザー定義のCPUおよびI/Oコストを導出できます。

EXPLAIN PLANは、ドメイン索引統計をPLAN_TABLEのOTHER列に表示します。たとえば、resume列にドメイン索引emp_resumeを持つユーザー定義演算子CONTAINSが表empに存在し、emp_resumeの索引タイプが演算子CONTAINSをサポートしている場合に、次の問合せをします。次の問合せの計画をEXPLAINします。

SELECT * FROM emp WHERE CONTAINS(resume, 'Oracle') = 1

次のような計画が表示されます。

OPERATION            OPTIONS      OBJECT_NAME     OTHER 
-----------------    -----------  ------------    ----------------
SELECT STATEMENT 
TABLE ACCESS         BY ROWID     EMP
DOMAIN INDEX                      EMP_RESUME      CPU: 300, I/O: 4

6.4 実行計画の比較

計画比較ツールは、参照プランと任意のテスト・プランのリストを受け取って、それらの間の相違点を強調表示します。計画は、行ごとではなく論理的に比較されます。

6.4.1 計画の比較の目的

計画の比較レポートでは、相違の原因を特定します。これは、ユーザーが計画の再現性の問題を絞り込む際に役立ちます。

計画の比較レポートは、特に次のシナリオで役立ちます。

パフォーマンスが低下している問合せの現在の計画とAWRで取得した古い計画を比較する場合。
SQL計画ベースラインは当初に意図していた計画の再現に失敗していて、新しい計画と意図していた計画の相違点を判断する場合。
ヒントの追加方法、パラメータの変更方法または索引の作成方法が、計画に与える影響を判断する場合。
SQLプロファイルに基づいて生成された計画またはSQLパフォーマンス・アナライザによって生成された計画が当初の計画と異なる点を判断する場合。

6.4.2 計画の比較のユーザー・インタフェース

DBMS_XPLAN.COMPARE_PLANSを使用すると、テキスト、XMLまたはHTML形式でレポートを生成できます。

計画の比較レポートの形式

レポートはサマリーから始まります。COMPARE PLANS REPORTセクションには、次の例に示すように、レポートを実行したユーザーや比較した計画の数などの情報が含まれます。

COMPARE PLANS REPORT
-------------------------------------------------------------------------
  Current user           : SH
  Total number of plans  : 2
  Number of findings     : 1
-------------------------------------------------------------------------

レポートのCOMPARISON DETAILSセクションには、次の情報が含まれています。

計画の情報

この情報には、計画の番号、計画のソース、計画の属性(ソースによって異なる)、解析スキーマおよびSQLテキストが含まれます。
計画

このセクションには、述語やノートを含む計画の行が表示されます。
比較結果

このセクションには、比較結果の概要が示されます。結合順序、結合方法、アクセス・パス、パラレル配分方法などの論理的な相違点が強調表示されています。結果は番号1から始まります。特定の問合せブロックに関連する結果の場合、テキストの冒頭はブロックの名前になります。特定のオブジェクトの別名に関連する結果の場合、テキストの冒頭は問合せブロックの名前とオブジェクトの別名になります。次に例を示します。
```
Comparison Results (1):
-----------------------------
 1. Query block SEL$1, Alias PRODUCTS@SEL$1: Some columns (OPERATION,
    OPTIONS, OBJECT_NAME) do not match between the reference
    plan (id: 2) and the current plan (id: 2).
```

DBMS_XPLAN.PLAN_OBJECT_LIST表タイプ

plan_object_listタイプにより、汎用オブジェクトのリストがDBMS_XPLAN.COMPARE_PLANS関数への入力として使用できるようになります。構文は次のとおりです。

TYPE plan_object_list IS TABLE OF generic_plan_object;

汎用オブジェクトは、すべての計画ソースから計画の共通属性を抽象化します。すべての計画ソースは、plan_object_listスーパークラスのサブクラスです。次の表に、様々な計画ソースのまとめを示します。オプション・パラメータがnullの場合は、複数のオブジェクトに対応できる点に注意してください。たとえば、cursor_cache_objectに子番号を指定しないと、指定したSQL IDのすべてのカーソル・キャッシュ文と一致します。

表6-3 PLAN_OBJECT_LISTの計画ソース

計画ソース	指定	説明
PLAN TABLE	`plan_table_object(owner, plan_table_name, statement_id, plan_id)`	このパラメータは次のとおりです。 `owner`—PLAN TABLEの所有者 `plan_table_name`—PLAN TABLEの名前 `statement_id`—文のID (オプション) `plan_id`—計画のID (オプション)
カーソル・キャッシュ	`cursor_cache_object(sql_id, child_number)`	このパラメータは次のとおりです。 `sql_id`—計画のSQL ID `child_number`—カーソル・キャッシュ内の計画の子番号(オプション)
AWR	`awr_object(sql_id, dbid, con_dbid, plan_hash_value)`	このパラメータは次のとおりです。 `sql_id`—計画のSQL ID `dbid`—データベースID (オプション) `con_dbid`—CDB ID (オプション) `plan_hash_value`—計画のハッシュ値(オプション)
SQLチューニング・セット	`sqlset_object (sqlset_owner, sqlset_name, sql_id, plan_hash_value)`	このパラメータは次のとおりです。 `sqlset_owner`—SQLチューニング・セットの所有者 `sqlset_name`—SQLチューニング・セットの名前 `sql_id`—計画のSQL ID `plan_hash_value`—計画のハッシュ値(オプション)
SQL計画管理	`spm_object (sql_handle, plan_name)`	このパラメータは次のとおりです。 `sql_handle`—SQL計画管理によって保護された計画のSQLハンドル `plan_name`—SQL計画ベースラインの名前(オプション)
SQLプロファイル	`sql_profile_object (profile_name)`	`profile_name`パラメータでは、SQLプロファイルの名前を指定します。
アドバイザ	`advisor_object (task_name, execution_name, sql_id, plan_id)`	このパラメータは次のとおりです。 `task_name`—アドバイザ・タスクの名前 `execution_name`—タスク実行の名前 `sql_id`—計画のSQL ID `plan_id`—アドバイザ計画ID (オプション)

DBMS_XPLAN.COMPARE_PLANS関数

計画の比較ツールのインタフェースは次の関数です。

DBMS_XPLAN.COMPARE_PLANS(
    reference_plan        IN generic_plan_object,
    compare_plan_list     IN plan_object_list,
    type                  IN VARCHAR2 := 'TEXT',
    level                 IN VARCHAR2 := 'TYPICAL',
    section               IN VARCHAR2 := 'ALL')
RETURN CLOB;

次の表では、比較する計画を指定するパラメータについて説明します。

表6-4 COMPARE_PLANS関数のパラメータ

パラメータ	説明
`reference_plan`	`generic_plan_object`タイプの1つのプランを指定します。
`compare_plan_list`	計画オブジェクトのリストを指定します。1つのオブジェクトは1つ以上の計画に対応します。

例6-8 子カーソルからの計画の比較

この例では、SQL IDが8mkxm7ur07za0で子カーソル番号が2の計画と、同じSQL IDで子カーソル番号が4の計画を比較します。

VAR v_report CLOB;

BEGIN
  :v_report := DBMS_XPLAN.COMPARE_PLANS(
    reference_plan    => CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 2),
    compare_plan_list => PLAN_OBJECT_LIST(CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 4)));
END;
/

PRINT v_report

例6-9 子カーソルからの計画とSQL計画ベースラインからの計画の比較

この例では、SQL IDが8mkxm7ur07za0で子カーソル番号が2の計画とSQL計画ベースラインからの計画を比較します。このベースライン問合せのSQLハンドルはSQL_024d0f7d21351f5dで、計画名はSQL_PLAN_sdfjkdです。

VAR v_report CLOB;
BEGIN
  :v_report := DBMS_XPLAN.COMPARE_PLANS( -
    reference_plan    => CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 2),
    compare_plan_list => PLAN_OBJECT_LIST(SPM_OBJECT('SQL_024d0f7d21351f5d', 'SQL_PLAN_sdfjkd')));
END;

PRINT v_report

例6-10 1つの計画と複数のソースからの計画の比較

この例では、サマリー・セクションのみが出力されます。このプログラムでは、SQL IDが8mkxm7ur07za0で子カーソル番号が2の計画と、次のリストに含まれるすべての計画を比較します。

SQL ID 8mkxm7ur07za0に対して生成された共有SQL領域内のすべての計画
SQL ID 6vfqvav0rgyadに対してSQLチューニング・セットSH. SQLT_WORKLOAD内で生成されたすべての計画
データベースID 5およびSQL ID 6vfqvav0rgyadに対して取得されるAWRのすべての計画
SQL_PLAN_sdfjkdという名前のハンドルSQL_024d0f7d21351f5dを持つ問合せに対する計画ベースライン
plan_id=38で識別されるsh.plan_tableに格納された計画
SQLプロファイル名pe3r3ejsfdで識別される計画
タスク名TASK_1228、実行名EXEC_1928およびSQL ID 8mkxm7ur07za0で識別されるSQLに格納されているすべての計画

VAR v_report CLOB
BEGIN
  :v_report := DBMS_XPLAN.COMPARE_PLANS(
    reference_plan    => CURSOR_CACHE_OBJECT('8mkxm7ur07za0', 2),
    compare_plan_list => plan_object_list(
         cursor_cache_object('8mkxm7ur07za0'),
         sqlset_object('SH', 'SQLT_WORKLOAD', '6vfqvav0rgyad'),
         awr_object('6vfqvav0rgyad', 5),
         spm_object('SQL_024d0f7d21351f5d', 'SQL_PLAN_sdfjkd'),
         plan_table_object('SH', 'plan_table', 38),
         sql_profile_object('pe3r3ejsfd'), 
         advisor_object('TASK_1228', 'EXEC_1928', '8mkxm7ur07za0')),
    type              => 'XML', 
    level             => 'ALL',
    section => 'SUMMARY');
END;
/

PRINT v_report

ノート:

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

6.4.3 実行計画の比較: チュートリアル

計画を比較するには、DBMS_XPLAN.COMPARE_PLANS関数を使用します。

このチュートリアルでは、2つの異なる問合せを比較します。この計画の比較レポートは、オプティマイザが一方の問合せでは結合消去変換を使用できたが、もう一方の問合せでは使用できなかったことを示しています。

前提条件

このチュートリアルでは、ユーザーshが次の問合せを発行したと仮定しています。

select count(*) 
from   products p, sales s 
where  p.prod_id = s.prod_id 
and    p.prod_min_price > 200;

select count(*) 
from   products p, sales s 
where  p.prod_id = s.prod_id 
and    s.quantity_sold = 43;

実行計画を比較するには:

SQL*Plusを起動し、管理権限でデータベースにログインします。

V$SQLを問い合せて、2つの問合せのSQL IDを確認します。

次の問合せは、文字列productsが含まれている問合せについてV$SQLを問い合せます。

SET LINESIZE 120
COL SQL_ID FORMAT a20
COL SQL_TEXT FORMAT a60

SELECT SQL_ID, SQL_TEXT
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%products%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%'
ORDER BY SQL_ID;

SQL_ID            SQL_TEXT
----------------- ------------------------------------------------
0hxmvnfkasg6q     select count(*) from products p, sales s where  
                  p.prod_id = s.prod_id and s.quantity_sold = 43

10dqxjph6bwum     select count(*) from products p, sales s where
                  p.prod_id = s.prod_id and p.prod_min_price > 200

shユーザーとしてデータベースにログインします。

前のステップで取得したSQL IDを指定して、DBMS_XPLAN.COMPARE_PLANS関数を実行します。

たとえば、次のプログラムを実行します。

VARIABLE v_rep CLOB

BEGIN
  :v_rep := DBMS_XPLAN.COMPARE_PLANS( 
    reference_plan    => cursor_cache_object('0hxmvnfkasg6q', NULL),
    compare_plan_list => plan_object_list(cursor_cache_object('10dqxjph6bwum', NULL)),
    type              => 'TEXT',
    level             => 'TYPICAL', 
    section           => 'ALL');
END;
/

レポートを印刷します。

たとえば、次の問合せを実行します。

SET PAGESIZE 50000
SET LONG 100000
SET LINESIZE 210
COLUMN report FORMAT a200
SELECT :v_rep REPORT FROM DUAL;

次のサンプル・レポートの「Comparison Results」セクションは、最初の問合せのみが結合消去変換を使用していることを示しています。

REPORT
---------------------------------------------------------------------------------------

COMPARE PLANS REPORT
---------------------------------------------------------------------------------------
  Current user           : SH
  Total number of plans  : 2
  Number of findings     : 1
---------------------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 0hxmvnfkasg6q
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : select count(*) from products p, sales s where
                        p.prod_id = s.prod_id and s.quantity_sold = 43

Plan
-----------------------------

 Plan Hash Value  : 3519235612

-------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      |       |  469 |          |
|   1 |   SORT AGGREGATE       |       |    1 |     3 |      |          |
|   2 |    PARTITION RANGE ALL |       |    1 |     3 |  469 | 00:00:01 |
| * 3 |     TABLE ACCESS FULL  | SALES |    1 |     3 |  469 | 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("S"."QUANTITY_SOLD"=43)

---------------------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 10dqxjph6bwum
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : select count(*) from products p, sales s where
                        p.prod_id = s.prod_id and p.prod_min_price > 200

Plan
-----------------------------

 Plan Hash Value  : 3037679890

---------------------------------------------------------------------------------------
|Id| Operation                         | Name           | Rows  | Bytes   |Cost |Time |
---------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                  |                |       |         |34|        |
| 1|   SORT AGGREGATE                  |                |     1 |      13 |  |        |
|*2|    HASH JOIN                      |                |781685 |10161905 |34|00:00:01|
|*3|     TABLE ACCESS FULL             | PRODUCTS       |    61 |     549 | 2|00:00:01|
| 4|     PARTITION RANGE ALL           |                |918843 | 3675372 |29|00:00:01|
| 5|      BITMAP CONVERSION TO ROWIDS  |                |918843 | 3675372 |29|00:00:01|
| 6|       BITMAP INDEX FAST FULL SCAN | SALES_PROD_BIX |       |         |  |        |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_MIN_PRICE">200)


Notes
-----
- This is an adaptive plan


Comparison Results (1):
-----------------------------
 1. Query block SEL$1: Transformation JOIN REMOVED FROM QUERY BLOCK occurred
    only in the reference plan (result query block: SEL$A43D1678).

関連項目:

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

6.4.4 実行計画の比較: 例

ここでは、shスキーマの表の問合せに関する計画の比較レポートを生成する方法の例を示します。

例6-11 説明された計画とカーソル内の計画の比較

この例では、shスキーマの表の問合せの計画について説明して、その問合せを実行します。

EXPLAIN PLAN 
  SET STATEMENT_ID='TEST' FOR
  SELECT c.cust_city, SUM(s.quantity_sold)
  FROM   customers c, sales s, products p
  WHERE  c.cust_id=s.cust_id
  AND    p.prod_id=s.prod_id
  AND    prod_min_price>100
  GROUP BY c.cust_city;

SELECT c.cust_city, SUM(s.quantity_sold)
FROM   customers c, sales s, products p
WHERE  c.cust_id=s.cust_id
AND    p.prod_id=s.prod_id
AND    prod_min_price>100
GROUP BY c.cust_city;

実行した問合せのSQL IDは、9mp7z6qq83k5yだと仮定しています。次のPL/SQLプログラムでは、PLAN_TABLEの計画と共有SQL領域の計画を比較します。

BEGIN
  :v_rep := DBMS_XPLAN.COMPARE_PLANS(
    reference_plan    => plan_table_object('SH', 'PLAN_TABLE', 'TEST', NULL),
    compare_plan_list => plan_object_list(cursor_cache_object('9mp7z6qq83k5y')),
    type              => 'TEXT', 
    level             => 'TYPICAL',
    section           => 'ALL');
END;
/

PRINT v_rep

次のサンプル・レポートは、計画が同じになることを示しています。

COMPARE PLANS REPORT
-------------------------------------------------------------------------
  Current user           : SH
  Total number of plans  : 2
  Number of findings     : 1
-------------------------------------------------------------------------

COMPARISON DETAILS
-------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : SH
 Plan Table Name        : PLAN_TABLE
 Statement ID           : TEST
 Plan ID                : 52
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : No SQL Text

Plan
-----------------------------
 Plan Hash Value  : 3473931970

--------------------------------------------------------------------------
| Id| Operation                | Name    | Rows | Bytes  |Cost| Time     |
--------------------------------------------------------------------------
|  0| SELECT STATEMENT         |         |   620|   22320|1213| 00:00:01 |
|  1|   HASH GROUP BY          |         |   620|   22320|1213| 00:00:01 |
|* 2|    HASH JOIN             |         |160348| 5772528|1209| 00:00:01 |
|  3|     TABLE ACCESS FULL    |CUSTOMERS| 55500|  832500| 414| 00:00:01 |
|* 4|     HASH JOIN            |         |160348| 3367308| 472| 00:00:01 |
|* 5|      TABLE ACCESS FULL   |PRODUCTS |    13|     117|   2| 00:00:01 |
|  6|      PARTITION RANGE ALL |         |918843|11026116| 467| 00:00:01 |
|  7|       TABLE ACCESS FULL  |SALES    |918843|11026116| 467| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C"."CUST_ID"="S"."CUST_ID")
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
* 5 - filter("PROD_MIN_PRICE">100)

Notes
-----
- This is an adaptive plan

--------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Cursor Cache
 SQL ID                 : 9mp7z6qq83k5y
 Child Number           : 0
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : select c.cust_city, sum(s.quantity_sold) from
                        customers c, sales s, products p where
                        c.cust_id=s.cust_id and p.prod_id=s.prod_id and
                        prod_min_price>100 group by c.cust_city

Plan
-----------------------------
 Plan Hash Value  : 3473931970

---------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows | Bytes  | Cost|Time     |
---------------------------------------------------------------------------
|  0| SELECT STATEMENT         |          |      |        |1213 |         |
|  1|   HASH GROUP BY          |          |   620|   22320|1213 |00:00:01 |
|* 2|    HASH JOIN             |          |160348| 5772528|1209 |00:00:01 |
|  3|     TABLE ACCESS FULL    |CUSTOMERS | 55500|  832500| 414 |00:00:01 |
|* 4|     HASH JOIN            |          |160348| 3367308| 472 |00:00:01 |
|* 5|      TABLE ACCESS FULL   |PRODUCTS  |    13|     117|   2 |00:00:01 |
|  6|      PARTITION RANGE ALL |          |918843|11026116| 467 |00:00:01 |
|  7|       TABLE ACCESS FULL  |SALES     |918843|11026116| 467 |00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("C"."CUST_ID"="S"."CUST_ID")
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
* 5 - filter("PROD_MIN_PRICE">100)

Notes
-----
- This is an adaptive plan

Comparison Results (1):
-----------------------------
 1. The plans are the same.

例6-12 ベースラインとSQLチューニング・セットの計画の比較

次の問合せの計画を比較するとします(副問合せに含まれるNO_MERGEヒントのみが異なっています)。

SELECT c.cust_city, SUM(s.quantity_sold)
FROM   customers c, sales s, 
       (SELECT prod_id FROM products WHERE prod_min_price>100) p
WHERE  c.cust_id=s.cust_id
AND    p.prod_id=s.prod_id
GROUP BY c.cust_city;

SELECT c.cust_city, SUM(s.quantity_sold)
FROM   customers c, sales s, 
       (SELECT /*+ NO_MERGE */ prod_id FROM products WHERE prod_min_price>100) 
WHERE  c.cust_id=s.cust_id
AND    p.prod_id=s.prod_id
GROUP BY c.cust_city;

最初の問合せの計画は、SQLハンドルSQL_c522f5888cc4613eのSQL計画管理ベースラインで取得されます。2番目の問合せの計画は、MYSTS1という名前のSQLチューニング・セットに格納されていて、SQL IDはd07p7qmrm13ncです。次のPL/SQLプログラムを実行して計画を比較します。

VAR v_rep CLOB

BEGIN 
  v_rep := DBMS_XPLAN.COMPARE_PLANS(
   reference_plan    => spm_object('SQL_c522f5888cc4613e'),
   compare_plan_list => plan_object_list(sqlset_object('SH', 'MYSTS1', 'd07p7qmrm13nc', null)),
   type              => 'TEXT',
   level             => 'TYPICAL',
   section           => 'ALL');
END;
/

PRINT v_rep

次の出力は、ヒントのない問合せに対応する参照プランのみがビューのマージを使用したことを示しています。


--------------------------------------------------------------------------- 
COMPARE PLANS REPORT
---------------------------------------------------------------------------
Current user       : SH 
Total number of plans : 2 
Number of findings     : 1
---------------------------------------------------------------------------

COMPARISON DETAILS
---------------------------------------------------------------------------
Plan Number            : 1 (Reference Plan)
Plan Found             : Yes
Plan Source            : SQL Plan Baseline
SQL Handle             : SQL_c522f5888cc4613e
Plan Name              : SQL_PLAN_ca8rpj26c8s9y7c2279c4 
Plan Database Version  : 19.0.0.0
Parsing Schema         : "SH"
SQL Text               : select c.cust_city, sum(s.quantity_sold) from 
                       customers c, sales s, (select prod_id from 
                       products where prod_min_price>100) p where 
                       c.cust_id=s.cust_id and p.prod id=s.prod_id 
                       group by c.cust_city

Plan
-----------------------------

Plan Hash Value  : 2082634180
---------------------------------------------------------------------------
| Id | Operation                | Name      |Rows |Bytes |Cost | Time     |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |           |     |      |  22 |          |
|  1 |   HASH GROUP BY          |           | 300 |11400 |  22 | 00:00:01 |
|  2 |    HASH JOIN             |           | 718 |27284 |  21 | 00:00:01 |
|  3 |     TABLE ACCESS FULL    | CUSTOMERS | 630 | 9450 |   5 | 00:00:01 |
|  4 |     HASH JOIN            |           | 718 |16514 |  15 | 00:00:01 |
|  5 |      TABLE ACCESS FULL   | PRODUCTS  | 573 | 5730 |   9 | 00:00:01 |
|  6 |      PARTITION RANGE ALL |           | 960 |12480 |   5 | 00:00:01 |
|  7 |       TABLE ACCESS FULL  | SALES     | 960 |12480 |   5 | 00:00:01 |
---------------------------------------------------------------------------

---------------------------------------------------------------------------
Plan Number            : 2
Plan Found             : Yes
Plan Source            : SQL Tuning Set 
SQL Tuning Set Owner   : SH
SQL Tuning Set Name    : MYSTS1
SQL ID                 : d07p7qmrm13nc
Plan Hash Value        : 655891922 
Plan Database Version  : 19.0.0.0 
Parsing Schema         : "SH"
SQL Text               : select c.cust_city, sum(s.quantity_sold) from 
                       customers c, sales s, (select /*+ NO_MERGE */ 
                       prod_id from products where prod_min_price>100) 
                       p where c.cust_id=s.cust_id and 
                       p.prod_id=s.prod_id group by c.cust_city

Plan
-----------------------------

Plan Hash Value  : 655891922
-------------------------------------------------------------------------
|Id | Operation                | Name      |Rows | Bytes |Cost| Time    |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT         |           |     |       | 23 |         |
| 1 |   HASH GROUP BY          |           | 300 |  9900 | 23 |00:00:01 |
| 2 |    HASH JOIN             |           | 718 | 23694 | 21 |00:00:01 |
| 3 |     HASH JOIN            |           | 718 | 12924 | 15 |00:00:01 |
| 4 |      VIEW                |           | 573 |  2865 |  9 |00:00:01 |
| 5 |       TABLE ACCESS FULL  | PRODUCTS  | 573 |  5730 |  9 |00:00:01 |
| 6 |      PARTITION RANGE ALL |           | 960 | 12480 |  5 |00:00:01 |
| 7 |       TABLE ACCESS FULL  | SALES     | 960 | 12480 |  5 |00:00:01 |
| 8 |     TABLE ACCESS FULL    | CUSTOMERS | 630 |  9450 |  5 |00:00:01 |
-------------------------------------------------------------------------

Notes
-----
- This is an adaptive plan

Comparison Results (1):
-----------------------------
1. Query block SEL$1: Transformation VIEW MERGE occurred only in the 
reference plan (result query block: SEL$F5BB74E1).

例6-13 索引の追加前後の計画の比較

この例では、問合せ計画の索引の効果をテストします。

EXPLAIN PLAN 
  SET STATEMENT_ID='TST1' FOR 
  SELECT COUNT(*) FROM products WHERE prod_min_price>100;

CREATE INDEX newprodidx ON products(prod_min_price); 

EXPLAIN PLAN 
  SET STATEMENT_ID='TST2' FOR 
  SELECT COUNT(*) FROM products WHERE prod_min_price>100;

次のPL/SQLプログラムを実行してレポートを生成します。

VAR v_rep CLOB

BEGIN
  :v_rep := DBMS_XPLAN.COMPARE_PLANS(
    reference_plan    => plan_table_object('SH','PLAN_TABLE','TST1',NULL),
    compare_plan_list => plan_object_list(plan_table_object('SH','PLAN_TABLE','TST2',NULL)),
    TYPE              => 'TEXT',
    level             => 'TYPICAL',
    section           => 'ALL');
END;
/

PRINT v_rep

次のレポートは、2つの計画の操作が異なっていることを示しています。

COMPARE PLANS REPORT
--------------------------------------------------------------------------
  Current user           : SH
  Total number of plans  : 2
  Number of findings     : 1
--------------------------------------------------------------------------

COMPARISON DETAILS
--------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : SH
 Plan Table Name        : PLAN_TABLE
 Statement ID           : TST1
 Plan ID                : 56
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : No SQL Text

Plan
-----------------------------
 Plan Hash Value  : 3421487369

--------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    1 |     5 |    2 | 00:00:01 |
|   1 |   SORT AGGREGATE     |          |    1 |     5 |      |          |
| * 2 |    TABLE ACCESS FULL | PRODUCTS |   13 |    65 |    2 | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("PROD_MIN_PRICE">100)

--------------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : SH
 Plan Table Name        : PLAN_TABLE
 Statement ID           : TST2
 Plan ID                : 57
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "SH"
 SQL Text               : No SQL Text

Plan
-----------------------------
 Plan Hash Value  : 2694011010

---------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    1 |     5 |    1 | 00:00:01 |
|   1 |   SORT AGGREGATE    |            |    1 |     5 |      |          |
| * 2 |    INDEX RANGE SCAN | NEWPRODIDX |   13 |    65 |    1 | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("PROD_MIN_PRICE">100)

Comparison Results (1):
-----------------------------
 1. Query block SEL$1, Alias PRODUCTS@SEL$1: Some columns (OPERATION, 
    OPTIONS, OBJECT_NAME) do not match between the reference plan 
    (id: 2) and the current plan (id: 2).

例6-14 可視索引と不可視索引の計画の比較

この例では、アプリケーションは次の問合せを実行します。

select count(*)
  from products p, sales s 
 where p.prod_id = s.prod_id
   and p.prod_status = 'obsolete';

この問合せの計画では、2つの索引sales_prod_bixおよびproducts_prod_status_bixが使用されます。データベースでは、両方の索引に対する可視と不可視のすべての組合せを使用して4つの計画が生成されます。SQL計画管理は、次の問合せのベースラインの計画を受け入れるとします。

可視のsales_prod_bixおよび可視のproducts_prod_status_bix
可視のsales_prod_bixおよび不可視のproducts_prod_status_bix
不可視のsales_prod_bixおよび可視のproducts_prod_status_bix

両方の索引を不可視にしてから、問合せを再度実行します。オプティマイザは、不可視の索引を使用できないため新しい計画を生成します。3つのベースライン計画(少なくとも1つの索引が可視であることに依存するものすべて)が再現に失敗します。そのため、オプティマイザは新しい計画を使用して問合せのSQL計画ベースラインに追加します。現在共有SQL領域にある計画(参照プラン)とベースラインの4つの計画すべてを比較するには、次のPL/SQLコードを実行します。

VAR v_rep CLOB

BEGIN
  :v_rep := DBMS_XPLAN.COMPARE_PLANS(
    reference_plan    => cursor_cache_object('45ns3tzutg0ds'),
    compare_plan_list => plan_object_list(spm_object('SQL_aec814b0d452da8a')),
    TYPE              => 'TEXT',
    level             => 'TYPICAL',
    section           => 'ALL');
END;
/

PRINT v_rep

次のレポートでは、5つのすべての計画を比較しています。

-----------------------------------------------------------------------------
COMPARE PLANS REPORT
-----------------------------------------------------------------------------
Current user       : SH
Total number of plans : 5
Number of findings     : 19
-----------------------------------------------------------------------------

COMPARISON DETAILS
-----------------------------------------------------------------------------
Plan Number            : 1 (Reference Plan)
Plan Found             : Yes
Plan Source            : Cursor Cache
SQL ID                 : 45ns3tzutg0ds
Child Number           : 0
Plan Database Version  : 19.0.0.0 
Parsing Schema         : "SH"
SQL Text               : select count(*) from products p, sales s where p.prod_id 
                       = s.prod_id and p.prod_status = 'obsolete'

Plan
-----------------------------

Plan Hash Value  : 1136711713
------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      |       |   15 |          |
|   1 |   SORT AGGREGATE         |          |    1 |    30 |      |          |
| * 2 |    HASH JOIN             |          |  320 |  9600 |   15 | 00:00:01 |
|   3 |     JOIN FILTER CREATE   | :BF0000  |  255 |  6375 |    9 | 00:00:01 |
| * 4 |      TABLE ACCESS FULL   | PRODUCTS |  255 |  6375 |    9 | 00:00:01 |
|   5 |     JOIN FILTER USE      | :BF0000  |  960 |  4800 |    5 | 00:00:01 |
|   6 |      PARTITION RANGE ALL |          |  960 |  4800 |    5 | 00:00:01 |
| * 7 |       TABLE ACCESS FULL  | SALES    |  960 |  4800 |    5 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 7 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))

Notes
-----
- baseline_repro_fail = yes

-----------------------------------------------------------------------------------
Plan Number            : 2
Plan Found             : Yes
Plan Source            : SQL Plan Baseline
SQL Handle             : SQL_aec814b0d452da8a
Plan Name              : SQL_PLAN_axk0nq3a55qna6e039463 
Plan Database Version  : 19.0.0.0
Parsing Schema         : "SH"
SQL Text               : select count(*) from products p, sales s where p.prod_id =
                       s.prod_id and p.prod_status = 'obsolete'

Plan
-----------------------------

Plan Hash Value  : 1845728355
-------------------------------------------------------------------------------------------
| Id| Operation                          | Name                    |Rows|Bytes|Cost| Time |
-------------------------------------------------------------------------------------------
|  0| SELECT STATEMENT                   |                         |  1|  30 |11 |00:00:01|
|  1|   SORT AGGREGATE                   |                         |  1|  30 |   |        |
| *2|    HASH JOIN                       |                         |320|9600 |11 |00:00:01|
|  3|     JOIN FILTER CREATE             | :BF0000                 |255|6375 | 5 |00:00:01|
| *4|      VIEW                          | index$_join$_001        |255|6375 | 5 |00:00:01|
| *5|       HASH JOIN                    |                         |   |     |   |        |
|  6|        BITMAP CONVERSION TO ROWIDS |                         |255|6375 | 1 |00:00:01|
| *7|         BITMAP INDEX SINGLE VALUE  | PRODUCTS_PROD_STATUS_BIX|   |     |   |        |
|  8|        INDEX FAST FULL SCAN        | PRODUCTS_PK             |255|6375 | 4 |00:00:01|
|  9|     JOIN FILTER USE                | :BF0000                 |960|4800 | 5 |00:00:01|
| 10|      PARTITION RANGE ALL           |                         |960|4800 | 5 |00:00:01|
|*11|       TABLE ACCESS FULL            | SALES                   |960|4800 | 5 |00:00:01|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 5 - access(ROWID=ROWID)
* 7 - access("P"."PROD_STATUS"='obsolete')
* 11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))

Comparison Results (4):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 4) in the reference plan are missing
in the current plan.
2. Query block SEL$1, Alias S@SEL$1: Some columns (ID) do not match between the reference
plan (id: 5) and the current plan (id: 9).
3. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, PARTITION_ID) do not 
match between the reference plan (id: 6) and the current plan (id: 10).
4. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, PARTITION_ID) do not
match between the reference plan (id: 7) and the current plan (id: 11).

-------------------------------------------------------------------------------------------
Plan Number            : 3
Plan Found             : Yes
Plan Source            : SQL Plan Baseline
SQL Handle             : SQL_aec814b0d452da8a
Plan Name              : SQL_PLAN_axk0nq3a55qna43c0d821 
Plan Database Version  : 19.0.0.0
Parsing Schema         : "SH"
SQL Text               : select count(*) from products p, sales s where p.prod_id = s.prod_id and 
                       p.prod_status = 'obsolete'

Plan
-----------------------------
Plan Hash Value  : 1136711713

------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    1 |    30 |   15 | 00:00:01 |
|   1 |   SORT AGGREGATE         |          |    1 |    30 |      |          |
| * 2 |    HASH JOIN             |          |  320 |  9600 |   15 | 00:00:01 |
|   3 |     JOIN FILTER CREATE   | :BF0000  |  255 |  6375 |    9 | 00:00:01 |
| * 4 |      TABLE ACCESS FULL   | PRODUCTS |  255 |  6375 |    9 | 00:00:01 |
|   5 |     JOIN FILTER USE      | :BF0000  |  960 |  4800 |    5 | 00:00:01 |
|   6 |      PARTITION RANGE ALL |          |  960 |  4800 |    5 | 00:00:01 |
| * 7 |       TABLE ACCESS FULL  | SALES    |  960 |  4800 |    5 | 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 4 - filter("P"."PROD_STATUS"='obsolete')
* 7 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."PROD_ID"))

Comparison Results (1):
-----------------------------
1. The plans are the same.

------------------------------------------------------------------------------
Plan Number            : 4
Plan Found             : Yes
Plan Source            : SQL Plan Baseline
SQL Handle             : SQL_aec814b0d452da8a
Plan Name              : SQL_PLAN_axk0nq3a55qna1b7aea6c 
Plan Database Version  : 19.0.0.0
Parsing Schema         : "SH"
SQL Text               : select count(*) from products p, sales s where p.prod_id = s.prod_id and 
                       p.prod_status = 'obsolete'

Plan
-----------------------------

Plan Hash Value  : 461040236
-------------------------------------------------------------------------------------
| Id | Operation                       | Name           |Rows|Bytes | Cost | Time   |
--------------------------------------------------------- ---------------------------
|  0 | SELECT STATEMENT                |                |  1 |   30 | 10 | 00:00:01 |
|  1 |   SORT AGGREGATE                |                |  1 |   30 |    |          |
|  2 |    NESTED LOOPS                 |                |320 | 9600 | 10 | 00:00:01 |
|* 3 |     TABLE ACCESS FULL           | PRODUCTS       |255 | 6375 |  9 | 00:00:01 |
|  4 |     PARTITION RANGE ALL         |                |  1 |    5 | 10 | 00:00:01 |
|  5 |      BITMAP CONVERSION COUNT    |                |  1 |    5 | 10 | 00:00:01 |
|* 6 |       BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX |    |      |    |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("P"."PROD_STATUS"='obsolete')
* 6 - access("P"."PROD_ID"="S"."PROD_ID")

Comparison Results (7):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 3) in the reference plan are missing
in the current plan.
2. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5) in the reference plan are missing
in the current plan.
3. Query block SEL$1, Alias S@SEL$1: Some lines (id: 7) in the reference plan are missing
in the current plan.
4. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5,6) in the current plan are missing
in the reference plan.
5. Query block SEL$1, Alias P@SEL$1: Some columns (OPERATION) do not match between the 
reference plan (id: 2) and the current plan (id: 2).
6. Query block SEL$1, Alias P@SEL$1: Some columns (ID, PARENT_ID, DEPTH) do not match 
between the reference plan (id: 4) and the current plan (id: 3).
7. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, DEPTH, POSITION, 
PARTITION_ID) do not match between the reference plan (id: 6) and the current plan (id: 4).

-------------------------------------------------------------------------------------------
Plan Number            : 5
Plan Found             : Yes
Plan Source            : SQL Plan Baseline
SQL Handle             : SQL_aec814b0d452da8a
Plan Name              : SQL_PLAN_axk0nq3a55qna0628afbd 
Plan Database Version  : 19.0.0.0
Parsing Schema         : "SH"
SQL Text               : select count(*) from products p, sales s where p.prod_id = s.prod_id and 
                       p.prod_status = 'obsolete'

Plan
-----------------------------

Plan Hash Value  : 103329725
-------------------------------------------------------------------------------------------
|Id| Operation                         | Name                     | Rows|Bytes|Cost|Time  |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                  |                          |    |     | 5 |        |
| 1|   SORT AGGREGATE                  |                          |  1 |  30 |   |        |
| 2|    NESTED LOOPS                   |                          |320 |9600 | 5 |00:00:01|
| 3|     VIEW                          | index$_join$_001         |255 |6375 | 5 |00:00:01|
| 4|      HASH JOIN                    |                          |    |     |   |        |
| 5|       BITMAP CONVERSION TO ROWIDS |                          |255 |6375 | 1 |00:00:01|
| 6|        BITMAP INDEX SINGLE VALUE  | PRODUCTS_PROD_STATUS_BIX |    |     |   |        |
| 7|       INDEX FAST FULL SCAN        | PRODUCTS_PK              |255 |6375 | 4 |00:00:01|
| 8|     PARTITION RANGE ALL           |                          |  1 |   5 | 5 |00:00:01|
| 9|      BITMAP CONVERSION TO ROWIDS  |                          |  1 |   5 | 5 |00:00:01|
|10|       BITMAP INDEX SINGLE VALUE   | SALES_PROD_BIX           |    |     |   |        |
-------------------------------------------------------------------------------------------

Comparison Results (7):
-----------------------------
1. Query block SEL$1, Alias P@SEL$1: Some lines (id: 3) in the reference plan are missing
in the current plan.
2. Query block SEL$1, Alias P@SEL$1: Some lines (id: 4) in the reference plan are missing
in the current plan.
3. Query block SEL$1, Alias S@SEL$1: Some lines (id: 5) in the reference plan are missing
in the current plan.
4. Query block SEL$1, Alias S@SEL$1: Some lines (id: 7) in the reference plan are missing
in the current plan.
5. Query block SEL$1, Alias S@SEL$1: Some lines (id: 9,10) in the current plan are missing 
in the reference plan.
6. Query block SEL$1, Alias P@SEL$1: Some columns (OPERATION) do not match between the 
reference plan (id: 2) and the current plan (id: 2).
7. Query block SEL$1, Alias S@SEL$1: Some columns (ID, PARENT_ID, DEPTH, POSITION, 
PARTITION_ID) do not match between the reference plan (id: 6) and the current plan (id: 8).

前述のレポートには、次の情報が示されています。

計画1は、共有SQL領域の参照プランです。この計画は、索引を使用しません(どちらも不可視になっています)。また、この計画はベースライン計画を再現しません。
計画2は、ベースラインにあります。この計画は、sales_prod_bixが不可視でproducts_prod_status_bixが可視であることを前提としています。
計画3は、ベースラインにあります。この計画は、両方の索引が不可視であることを前提としています。計画1と計画3は同じです。
計画4は、ベースライン内にあります。この計画は、sales_prod_bixが可視であり、products_prod_status_bixが不可視であることを前提としています。
計画5は、ベースラインにあります。この計画は、両方の索引が可視であることを前提としています。

この比較レポートは、計画1がそのベースラインからの計画を再現できなかったことを示しています。その理由は、実行時に使用可能なベースライン計画がなかったために、カーソル内の計画(計画1)がベースラインに追加されたからです。そのため、データベースでソフト解析が実行されて、索引のない計画が生成されました。現在のカーソルが無効になっているときに問合せが再度実行されると、比較レポートは、カーソル・プランがベースライン計画を再現したことを示します。

関連項目:

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

例6-15 再現に失敗したベースラインの比較

1つのユースケースとして、コストベース計画とSQL計画ベースラインを比較します。この例では、一意索引を作成します。データベースは、この索引を使用する計画ベースラインを取得します。索引を不可視にしてから、この問合せを再度実行します。索引が可視化されていないためオプティマイザは別の計画の選択を強制され、ベースライン計画は再現に失敗します。ベースライン計画と原価ベース計画についての計画の比較レポートは、この2つの計画にアクセス・パスの違いがあることを示しています。

データベースにユーザーhrとしてログインして、PLAN TABLEを作成します。

CREATE TABLE PLAN_TABLE (
 STATEMENT_ID                VARCHAR2(30),
 PLAN_ID                     NUMBER,
 TIMESTAMP                   DATE,
 REMARKS                     VARCHAR2(4000),
 OPERATION                   VARCHAR2(30),
 OPTIONS                     VARCHAR2(255),
 OBJECT_NODE                 VARCHAR2(128),
 OBJECT_OWNER                VARCHAR2(30),
 OBJECT_NAME                 VARCHAR2(30),
 OBJECT_ALIAS                VARCHAR2(65),
 OBJECT_INSTANCE             NUMBER(38),
 OBJECT_TYPE                 VARCHAR2(30),
 OPTIMIZER                   VARCHAR2(255),
 SEARCH_COLUMNS              NUMBER,
 ID                          NUMBER(38),
 PARENT_ID                   NUMBER(38),
 DEPTH                       NUMBER(38),
 POSITION                    NUMBER(38),
 COST                        NUMBER(38),
 CARDINALITY                 NUMBER(38),
 BYTES                       NUMBER(38),
 OTHER_TAG                   VARCHAR2(255),
 PARTITION_START             VARCHAR2(255),
 PARTITION_STOP              VARCHAR2(255),
 PARTITION_ID                NUMBER(38),
 OTHER                       LONG,
 DISTRIBUTION                VARCHAR2(30),
 CPU_COST                    NUMBER(38),
 IO_COST                     NUMBER(38),
 TEMP_SPACE                  NUMBER(38),
 ACCESS_PREDICATES           VARCHAR2(4000),
 FILTER_PREDICATES           VARCHAR2(4000),
 PROJECTION                  VARCHAR2(4000),
 TIME                        NUMBER(38),
 QBLOCK_NAME                 VARCHAR2(30),
 OTHER_XML                   CLOB);

次のDDL文を実行します。この文では、staffという表とstaff.employee_id列に対する索引を作成します。
```
CREATE TABLE staff AS (SELECT * FROM employees);
CREATE UNIQUE INDEX staff_employee_id ON staff (employee_id);
```

次の文を実行して、staffの問合せをSQL計画管理で保護し、索引を不可視にします。

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
SELECT COUNT(*) FROM staff WHERE employee_id = 20;
-- execute query a second time to create a baseline
SELECT COUNT(*) FROM staff WHERE employee_id = 20;
ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
ALTER INDEX staff_employee_id INVISIBLE;

計画を説明して、PLAN TABLEを問い合せます(出力例も示します)。

EXPLAIN PLAN SET STATEMENT_ID='STAFF' FOR SELECT COUNT(*) FROM staff 
  WHERE employee_id = 20;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'TYPICAL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 1778552452

------------------------------------------------------------------------
| Id  | Operation          | Name  |Rows  |Bytes |Cost (%CPU)|Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    1 |    4 |    2   (0)|00:00:01 |
|   1 |  SORT AGGREGATE    |       |    1 |    4 |           |         |
|*  2 |   TABLE ACCESS FULL| STAFF |    1 |    4 |    2   (0)|00:00:01 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------

   2 - filter("EMPLOYEE_ID"=20)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Failed to use SQL plan baseline for this statement

前述の出力が示すように、索引が不可視になっているため、オプティマイザは全表スキャンを選択します。SQL計画ベースラインでは索引が使用されているため、オプティマイザは計画を再現できません。

別のセッションで、SYSとしてログインし、SQL計画ベースラインのハンドルと計画名を問い合せます(出力例も示します)。

SET LINESIZE 120
COL SQL_HANDLE FORMAT a25
COL PLAN_NAME FORMAT a35

SELECT DISTINCT SQL_HANDLE,PLAN_NAME,ACCEPTED 
FROM   DBA_SQL_PLAN_BASELINES 
WHERE  PARSING_SCHEMA_NAME = 'HR';

SQL_HANDLE                PLAN_NAME                           ACC
------------------------- ----------------------------------- ---
SQL_3fa3b23c5ba1bf60      SQL_PLAN_3z8xk7jdu3gv0b7aa092a      YES

前のステップで取得したSQLハンドルと計画ベースライン名を指定して、計画を比較します。

VAR v_report CLOB

BEGIN
 :v_report := DBMS_XPLAN.COMPARE_PLANS(
   reference_plan    => plan_table_object('HR', 'PLAN_TABLE', 'STAFF'),
   compare_plan_list => plan_object_list (SPM_OBJECT('SQL_3fa3b23c5ba1bf60','SQL_PLAN_3z8xk7jdu3gv0b7aa092a')),
   type              => 'TEXT',
   level             => 'ALL',
   section           => 'ALL');
END;
/

計画の比較レポートを問い合せます(出力例も示します)。

SET LONG 1000000
SET PAGESIZE 50000
SET LINESIZE 200
SELECT :v_report rep FROM DUAL;

REP
------------------------------------------------------------------------

COMPARE PLANS REPORT
------------------------------------------------------------------------
  Current user           : SYS
  Total number of plans  : 2
  Number of findings     : 1
------------------------------------------------------------------------

COMPARISON DETAILS
------------------------------------------------------------------------
 Plan Number            : 1 (Reference Plan)
 Plan Found             : Yes
 Plan Source            : Plan Table
 Plan Table Owner       : HR
 Plan Table Name        : PLAN_TABLE
 Statement ID           : STAFF
 Plan ID                : 72
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "HR"
 SQL Text               : No SQL Text

Plan
-----------------------------
 Plan Hash Value  : 1766070819

--------------------------------------------------------------------
| Id | Operation            | Name  |Rows| Bytes | Cost | Time     |
--------------------------------------------------------------------
|   0| SELECT STATEMENT     |       |  1 |    13 |    2 | 00:00:01 |
|   1|   SORT AGGREGATE     |       |  1 |    13 |      |          |
| * 2|    TABLE ACCESS FULL | STAFF |  1 |    13 |    2 | 00:00:01 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("EMPLOYEE_ID"=20)

Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
- baseline_repro_fail = yes

--------------------------------------------------------------------
 Plan Number            : 2
 Plan Found             : Yes
 Plan Source            : SQL Plan Baseline
 SQL Handle             : SQL_3fa3b23c5ba1bf60
 Plan Name              : SQL_PLAN_3z8xk7jdu3gv0b7aa092a
 Plan Database Version  : 19.0.0.0
 Parsing Schema         : "HR"
 SQL Text               : SELECT COUNT(*) FROM staff WHERE employee_id = 20

Plan
-----------------------------

 Plan Hash Value  : 3081373994

------------------------------------------------------------------------
|Id| Operation            | Name            |Rows|Bytes |Cost |Time    |
------------------------------------------------------------------------
| 0| SELECT STATEMENT     |                   | 1 |  13 |   0 |00:00:01|
| 1|   SORT AGGREGATE     |                   | 1 |  13 |     |        |
|*2|    INDEX UNIQUE SCAN | STAFF_EMPLOYEE_ID | 1 |  13 |   0 |00:00:01|
------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("EMPLOYEE_ID"=20)

Comparison Results (1):
-----------------------------
 1. Query block SEL$1, Alias "STAFF"@"SEL$1": Some columns (OPERATION,
OPTIONS, OBJECT_NAME) do not match between the reference plan (id: 2)
and the current plan (id: 2)
------------------------------------------------------------------------