ヘッダーをスキップ
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス
11g リリース1（11.1）
E05686-02
  目次
目次		 索引
索引
戻る
戻る 		 次へ
次へ		  

164 DBMS_XPLAN

DBMS_XPLANパッケージによって、EXPLAIN PLANコマンドの出力を事前定義した複数の書式で表示する簡単な方法が提供されます。 また、DBMS_XPLANパッケージを使用して、自動ワークロード・リポジトリ（AWR）またはSQLチューニング・セットに格納されている文のプランを表示することもできます。 さらに、V$SQL_PLAN固定ビューおよびV$SQL_PLAN_STATISTICS_ALL固定ビューに格納されている情報に基づいてキャッシュしたSQLカーソルについて、SQL実行計画およびSQL実行ランタイム統計を表示することもできます。最後に、SQL計画のベースラインから計画を表示します。


関連項目:

  • EXPLAIN PLANコマンド、AWRおよびSQLチューニング・セットの詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

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

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

DBMS_XPLANの使用方法

概要

DBMS_XPLANパッケージでは、次の5つのテーブル・ファンクションを提供しています。

セキュリティ・モデル

このパッケージは、パッケージ所有者（SYS）ではなく、コール・ユーザーの権限で実行されます。 テーブル・ファンクションDISPLAY_CURSORを使用するには、V$SQL_PLAN、V$SESSIONおよびV$SQL_PLAN_STATISTICS_ALL.の各固定ビューに対するSELECT権限が必要です。

DISPLAY_AWRファンクションを使用する場合、ユーザーにはDBA_HIST_SQL_PLAN、DBA_HIST_SQLTEXTおよびV$DATABASEに対するSELECT権限が必要です。

DISPLAY_SQLSETファンクションを使用する場合、ユーザーにはALL_SQLSET_STATEMENTSおよびALL_SQLSET_PLANSに対するSELECT権限が必要です。

DISPLAY_SQL_PLAN_BASELINEファンクションを使用する場合、ユーザーにはDBA_SQL_PLAN_BASELINESに対するSELECT権限が必要です。

これらの権限は、SELECT_CATALOGロールの一部として自動的に付与されます。

例

DBMS_XPLAN.DISPLAYを使用したPLAN TABLEの表示

SELECT文でEXPLAIN PLANコマンドを実行します。

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';

DBMS_XPLAN.DISPLAYテーブル・ファンクションを使用して、プランを表示します。

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

この問合せによって次の出力が作成されます。

Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."ENAME"='benoit')

15 rows selected.

DBMS_XPLAN.DISPLAY_CURSORを使用したカーソル実行計画の表示

デフォルトでは、テーブル・ファンクションDISPLAY_CURSORによって、セッションで最後に実行されたSQL文の実行計画が書式設定されます。次に例を示します。

SELECT ename  FROM  emp e, dept d
   WHERE   e.deptno = d.deptno
   AND   e.empno=7369;

ENAME
----------
SMITH

セッションで最後に実行された文の実行計画を表示するには、次のように入力します。

SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

この問合せによって次の出力が作成されます。

Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0
------------------------------------------------------------------
SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno=7369

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |            |          |
|*  1 |  HASH JOIN         |      |     1 |    16 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."EMPNO"=7369)

21 rows selected.

テーブル・ファンクションDISPLAY_CURSORを使用して、カーソル・キャッシュに格納されている任意のロード済カーソルの実行計画を表示することもできます。その場合、子カーソルへの参照をテーブル・ファンクションに指定する必要があります。また、この指定には、文のSQL IDと子番号（オプション）も含まれます。

次のように、簡単に判別できるコメントを付けて問合せを実行します。

SELECT /* TOTO */ ename, dname
FROM dept d join emp e USING (deptno);

前の文のsql_idとchild_numberを取得します。

SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%TOTO%';

SQL_ID         CHILD_NUMBER
----------     -----------------------------
gwp663cqh5qbf   0

カーソルの実行計画を表示します。

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ ename, dname
FROM dept d JOIN emp e USING (deptno);

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |
|   1 |  SORT GROUP BY      |      |     4 |    64 |     7  (43)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   224 |     6  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    44 |     3  (34)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    70 |     3  (34)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")

sql_idとchild_numberのペアを取得する問合せおよびプランを表示するための問合せの2つの問合せを発行するかわりに、1つの問合せにまとめることもできます。

文字列'TOTO'に一致するすべてのカーソルの実行計画を表示します。

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';

パラレル情報を含むPLAN TABLEの表示

デフォルトでは、DISPLAYおよびDISPLAY_CURSORテーブル・ファンクションでレポートされるのは関連情報のみです。 「DBMS_XPLAN.DISPLAYを使用したPLAN TABLEの表示」で示した問合せは、パラレルで実行されていません。したがって、プランのパラレル化に関連する情報はレポートされません。次の例のように、問合せがパラレルで実行された場合のみ、パラレル情報がレポートされます。

ALTER TABLE emp PARALLEL;
EXPLAIN PLAN for
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename    ='hermann'
   ORDER BY e.empno;

DBMS_XPLAN.DISPLAYテーブル・ファンクションを使用して、プランを表示します。

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3693697345

-------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Bytes | Cost (%CPU)| Time     | TQ    |INOUT |PQ Distrib   |
-------------------------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |         | 1    | 117   | 6 (50)     | 00:00:01 |       |      |             |
| 1  | PX COORDINATOR     |         |      |       |            |          |       |      |             |
| 2  | PX SEND QC (ORDER) |:TQ10003 | 1    | 117   | 6 (50)     | 00:00:01 | Q1,03 | P->S | QC (ORDER)  |
| 3  | SORT ORDER BY      |         | 1    | 117   | 6 (50)     | 00:00:01 | Q1,03 | PCWP |             |
| 4  | PX RECEIVE         |         | 1    | 117   | 5 (40)     | 00:00:01 | Q1,03 | PCWP |             |
| 5  | PX SEND RANGE      |:TQ10002 | 1    | 117   | 5 (40)     | 00:00:01 | Q1,02 | P->P | RANGE       |
|* 6 | HASH JOIN          |         | 1    | 117   | 5 (40)     | 00:00:01 | Q1,02 | PCWP |             |
| 7  | PX RECEIVE         |         | 1    | 87    | 2 (50)     | 00:00:01 | Q1,02 | PCWP |             |
| 8  | PX SEND HASH       |:TQ10001 | 1    | 87    | 2 (50)     | 00:00:01 | Q1,01 | P->P | HASH        |
| 9  | PX BLOCK ITERATOR  |         | 1    | 87    | 2 (50)     | 00:00:01 | Q1,01 | PCWC |             |
|* 10| TABLE ACCESS FULL  | EMP     | 1    | 87    | 2 (50)     | 00:00:01 | Q1,01 | PCWP |             |
| 11 | BUFFER SORT        |         |      |       |            |          | Q1,02 | PCWC |             |
| 12 | PX RECEIVE         |         | 4    | 120   | 3 (34)     | 00:00:01 | Q1,02 | PCWP |             |
| 13 | PX SEND HASH       |:TQ10000 | 4    | 120   | 3 (34)     | 00:00:01 |       | S->P | HASH        |
| 14 | TABLE ACCESS FULL  | DEPT    | 4    | 120   | 3 (34)     | 00:00:01 |       |      |             |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."DEPTNO"="D"."DEPTNO")
10 - filter("E"."ENAME"='hermann')
---------------------------------------------------

問合せがパラレルの場合、並列性に関連する情報として、テーブル・キュー番号（TQ列）、テーブル・キュー・タイプ（INOUT）およびテーブル・キュー配布方式（PQ Distrib）がレポートされます。

デフォルトでは、PLAN TABLE内の複数のプランが、DISPLAYテーブル・ファンクションに渡されたstatement_idパラメータ（デフォルト値はNULL）と一致した場合に表示されるのは、最新のEXPLAIN PLANコマンドに対応するプランのみです。したがって、各EXPLAIN PLANの後で、PLAN TABLEをパージする必要はありません。ただし、DISPLAYテーブル・ファンクションの最適な実行パフォーマンスを確保するため、PLAN TABLEを定期的にパージする必要があります。PLAN TABLEが作成されていない場合は、グローバル一時表を使用して個々のユーザーの計画情報が格納され、その内容がセッションの存続期間中保持されます。グローバル一時表の内容は切り捨てることができないことに注意してください。

使用しやすくするため、DISPLAYテーブル・ファンクション以外にビューを定義し、そのビューを使用して、EXPLAIN PLANコマンドの出力を次のように表示できます。

最新のEXPLAIN PLANを表示するためのビューの使用方法

# define plan view
CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

# display the output of the last explain plan command
SELECT * FROM PLAN;

DBMS_XPLANサブプログラムの要約

表164-1 DBMS_XPLANパッケージのサブプログラム

サブプログラム 説明

DISPLAYファンクション

PLAN TABLEの内容を表示します。

DISPLAY_AWRファンクション

AWR内に格納されている実行計画の内容を表示します。

DISPLAY_CURSORファンクション

カーソル・キャッシュ内の任意のカーソルの実行計画を表示します。

DISPLAY_SQL_PLAN_BASELINEファンクション

SQL計画ベースラインの指定したSQLハンドルの1つ以上の実行計画を表示します。

DISPLAY_SQLSETファンクション

SQLチューニング・セットに格納されている指定した文の実行計画を表示します。

DISPLAYファンクション

このテーブル・ファンクションは、PLAN TABLEの内容を表示します。

また、このテーブル・ファンクションを使用して、表に格納されているプランを（統計情報を含めて、または統計情報を含めずに）表示できます。ただし、そのためには、この表の列の名前がPLAN TABLE（統計情報が含まれている場合はV$SQL_PLAN_STATISTICS_ALL）の列の名前と同じである必要があります。指定した表に述語を適用して、表示するプランの行を選択できます。

構文

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL,
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);

パラメータ

表164-2 DISPLAYファンクションのパラメータ

パラメータ 説明

table_name

プランが格納されている表の名前を指定します。 このパラメータのデフォルトはPLAN_TABLE,です。これはEXPLAIN PLANコマンドに対するデフォルトのPLAN TABLEです。 NULLが指定された場合も、デフォルトのPLAN_TABLEになります。

statement_id

表示するプランのstatement_idを指定します。 デフォルトはNULL（EXPLAIN PLANコマンドがset statement_id句なしで実行された場合のデフォルト値）です。statement_idを指定しない場合は、説明された文のうちで直近のものについて、プランが表示されます。

format

プランの詳細レベルを制御します。次の4つの値を指定できます。

  • BASIC: プランの最小限の情報として、操作ID、操作名および操作オプションを表示します。

  • TYPICAL: これがデフォルトです。プランに関する最も一般的な情報（操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト）が表示されます。プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。PROJECTION、ALIASおよびREMOTE SQLの情報のみが除外されます（説明の後半を参照）。

  • SERIAL: TYPICALと同じですが、プランがパラレルで実行される場合でもパラレル情報は表示されません。

  • ALL: 最高のユーザー・レベルです。TYPICALレベルで表示される情報に加えて、追加情報（PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報）が表示されます。

表示出力をより細かく制御するために、前述の3つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ（PARTITIONなど）または基となるPLAN TABLEの出力への論理的追加（PREDICATEなど）を表します。フォーマット・キーワードは、カンマまたはスペースで区切る必要があります。

  • ROWS - 関連がある場合は、オプティマイザで推定された行数を表示します。

  • BYTES - 関連がある場合は、オプティマイザで推定されたバイト数を表示します。

  • COST - 関連がある場合は、オプティマイザ・コスト情報を表示します。

  • PARTITION - 関連がある場合は、パーティション・プルーニング情報を表示します。

  • PARALLEL - 関連がある場合は、PX情報（配布方式およびテーブル・キュー情報）を表示します。

  • PREDICATE - 関連がある場合は、predicateセクションを表示します。

  • PROJECTION - 関連がある場合は、projectionセクションを表示します。

  • ALIAS - 関連がある場合は、Query Block Name/Object Aliasセクションを表示します。

  • REMOTE - 関連がある場合は、分散問合せ（シリアル分散およびリモートSQLを使用したリモート問合せなど）に関する情報を表示します。

  • NOTE - 関連がある場合は、EXPLAIN PLANのnoteセクションを表示します。

フォーマット・キーワードに-という接頭辞を付けて、指定した情報を除外することができます。たとえば、'-PROJECTION'と指定すると、投影情報が除外されます。

目的のPLAN TABLE（table_nameパラメータを参照）にプランの統計情報の列も格納する場合（PLAN TABLEがV$SQL_PLAN_STATISTICS_ALL固定ビューの内容を取得するために使用される表である場合など）は、追加のフォーマット・キーワードを使用して、DISPLAYファンクションの使用時に表示される統計情報のクラスを指定できます。 追加可能なフォーマット・キーワードは、IOSTATS、MEMSTATS、ALLSTATSおよびLASTです（これら4つのキーワードの詳細は、「DISPLAY_CURSORファンクション」または「DISPLAY_SQLSETファンクション」を参照してください）。

filter_preds

プランを格納する表から選択される行のセットを制限するSQLフィルタ述語。値がNULL（デフォルト値）の場合は、最後に実行されたEXPLAIN PLANに応じてプランが表示されます。 たとえば、filter_preds=>'plan_id = 10'などです。

プランを格納する表のすべての列を参照できます。また、副問合せ、ファンクション・コールなどのすべてのSQL構文（「使用上の注意」の「警告」を参照）を格納できます。

使用上の注意

formatパラメータのバリエーションの例を次に示します。

例

PLAN TABLEに格納されている最新のEXPLAIN PLANコマンドの結果を表示するには、次のように入力します。

SELECT * FROM table (DBMS_XPLAN.DISPLAY);

デフォルトのPLAN TABLE以外のPLAN TABLE（例: "my_plan_table"）から表示するには、次のように入力します。

SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));

最小限のプラン情報を表示するには、次のように入力します。

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));

fooで識別される文（statement_id='foo'など）のプランを表示するには、次のように入力します。

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));

DISPLAY_AWRファンクション

このテーブル・ファンクションは、AWR内に格納されている実行計画の内容を表示します。

構文

DBMS_XPLAN.DISPLAY_AWR(
   sql_id            IN      VARCHAR2,
   plan_hash_value   IN      NUMBER DEFAULT NULL,
   db_id             IN      NUMBER DEFAULT NULL,
   format            IN      VARCHAR2 DEFAULT TYPICAL);

パラメータ

表164-3 DISPLAY_AWRテーブル・ファンクションのパラメータ

パラメータ 説明

sql_id

SQL文のSQL_IDを指定します。 DBA_HIST_SQLTEXT内の列SQL_IDを問い合せることによって、目的のSQL文に対する適切な値を取り出せます。

plan_hash_value

SQL文のPLAN_HASH_VALUEを指定します。このパラメータはオプションです。 省略すると、このテーブル・ファンクションは、指定のSQL_IDに対する格納済のすべての実行計画を戻します。

db_id

SQL_IDで識別されるSQL文の計画を表示する対象となるdatabase_idを指定します。 指定しない場合は、V$DATABASEに示されるようなローカル・データベースのdatabase_idが使用されます。

format

プランの詳細レベルを制御します。次の4つの値を指定できます。

  • BASIC: プランの最小限の情報として、操作ID、操作名および操作オプションを表示します。

  • TYPICAL: これがデフォルトです。プランに関する最も一般的な情報（操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト）が表示されます。プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。PROJECTION、ALIASおよびREMOTE SQLの情報のみが除外されます（説明の後半を参照）。

  • SERIAL: TYPICALと同じですが、プランがパラレルで実行される場合でもパラレル情報は表示されません。

  • ALL: 最高のユーザー・レベルです。TYPICALレベルで表示される情報に加えて、追加情報（PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報）が表示されます。

表示出力をより細かく制御するために、前述の3つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ（PARTITIONなど）または基となるPLAN TABLEの出力への論理的追加（PREDICATEなど）を表します。フォーマット・キーワードは、カンマまたはスペースで区切る必要があります。

  • ROWS - 関連がある場合は、オプティマイザで推定された行数を表示します。

  • BYTES - 関連がある場合は、オプティマイザで推定されたバイト数を表示します。

  • COST - 関連がある場合は、オプティマイザ・コスト情報を表示します。

  • PARTITION - 関連がある場合は、パーティション・プルーニング情報を表示します。

  • PARALLEL - 関連がある場合は、PX情報（配布方式およびテーブル・キュー情報）を表示します。

  • PREDICATE - 関連がある場合は、predicateセクションを表示します。

  • PROJECTION - 関連がある場合は、projectionセクションを表示します。

  • ALIAS - 関連がある場合は、Query Block Name/Object Aliasセクションを表示します。

  • REMOTE - 関連がある場合は、分散問合せ（シリアル分散およびリモートSQLを使用したリモート問合せなど）に関する情報を表示します。

  • NOTE - 関連がある場合は、EXPLAIN PLANのnoteセクションを表示します。

フォーマット・キーワードに-という接頭辞を付けて、指定した情報を除外することができます。たとえば、'-PROJECTION'と指定すると、投影情報が除外されます。

使用上の注意

例

SQL ID 'atfwcg8anrykp'に関連付けられた別の実行計画を表示するには、次のように入力します。

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

文字列'TOTO'を含む、格納されているすべてのSQL文の実行計画をすべて表示するには、次のように入力します。

SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table
    (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf
 WHERE ht.sql_text like '%TOTO%';

DISPLAY_CURSORファンクション

このテーブル・ファンクションは、カーソル・キャッシュにロードされている任意のカーソルの実行計画を表示します。 実行計画に加え、V$SQL_PLAN_STATISTICS_ALL VIEWSに基づいて様々なプランの統計情報（I/O、メモリー、タイミングなど）をレポートできます。

構文

DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL,
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

パラメータ

表164-4 DISPLAY_CURSORファンクションのパラメータ

パラメータ 説明

sql_id

カーソル・キャッシュ内のSQL文のSQL_IDを指定します。 V$SQLまたはV$SQLAREA内の列SQL_IDに問い合せることによって、適切な値を取り出せます。 また、V$SESSIONから特定セッションに対する列PREV_SQL_IDを選択することもできます。このパラメータのデフォルトはNULLです。その場合、セッションで最後に実行されたカーソルが表示されます。

child_number

表示するカーソルの子番号。 指定しない場合は、指定のsql_idパラメータに一致するすべてのカーソルの実行計画が表示されます。 child_numberは、sql_idが指定されている場合にのみ指定できます。

format

プランの詳細レベルを制御します。次の4つの値を指定できます。

  • BASIC: プランの最小限の情報として、操作ID、操作名および操作オプションを表示します。

  • TYPICAL: これがデフォルトです。プランに関する最も一般的な情報（操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト）が表示されます。プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。PROJECTION、ALIASおよびREMOTE SQLの情報のみが除外されます（説明の後半を参照）。

  • SERIAL: TYPICALと同じですが、プランがパラレルで実行される場合でもパラレル情報は表示されません。

  • ALL: 最高のユーザー・レベルです。TYPICALレベルで表示される情報に加えて、追加情報（PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報）が表示されます。

表示出力をより細かく制御するために、前述の3つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ（PARTITIONなど）または基となるPLAN TABLEの出力への論理的追加（PREDICATEなど）を表します。

フォーマット・キーワードは、カンマまたはスペースで区切る必要があります。

  • ROWS - 関連がある場合は、オプティマイザで推定された行数を表示します。

  • BYTES - 関連がある場合は、オプティマイザで推定されたバイト数を表示します。

  • COST - 関連がある場合は、オプティマイザ・コスト情報を表示します。

  • PARTITION - 関連がある場合は、パーティション・プルーニング情報を表示します。

  • PARALLEL - 関連がある場合は、PX情報（配布方式およびテーブル・キュー情報）を表示します。

  • PREDICATE - 関連がある場合は、predicateセクションを表示します。

  • PROJECTION - 関連がある場合は、projectionセクションを表示します。

  • ALIAS - 関連がある場合は、Query Block Name/Object Aliasセクションを表示します。

  • REMOTE - 関連がある場合は、分散問合せ（シリアル分散およびリモートSQLを使用したリモート問合せなど）に関する情報を表示します。

  • NOTE - 関連がある場合は、EXPLAIN PLANのnoteセクションを表示します。

  • IOSTATS - （gather_plan_statisticsヒントを使用するか、またはstatistics_levelパラメータをALLに設定することによって）SQL文の実行時に基本的なプランの統計情報が収集されるとすると、このフォーマットによって、カーソルのALLの実行に対する（または後述するLASTの実行のみに対する）IO統計情報が表示されます。

  • MEMSTATS - PGAメモリー管理が有効化されている（pga_aggregate_targetパラメータが0（ゼロ）以外の値に設定されている）とすると、このフォーマットによって、メモリー管理の統計情報（演算子の実行モード、メモリー使用量、ディスクに書き出されるバイト数など）を表示できます。これらの統計情報は、メモリーが集中的に使用される操作（ハッシュ結合、ソート、一部のビットマップ演算子など）にのみ適用されます。

  • ALLSTATS - 'IOSTATS MEMSTATS'のショートカット。

  • LAST - デフォルトでは、カーソルのすべての実行に対してプランの統計情報が表示されます。LASTキーワードを指定すると、最後の実行に対する統計情報のみを表示できます。

次の2つのフォーマットは、使用しないことをお薦めしますが、下位互換性を維持する目的でサポートされています。

  • RUNSTATS_TOT - IOSTATSと同じです。指定したカーソルのすべての実行に対してIO統計情報を表示します。

  • RUNSTATS_LAST - IOSTATS LASTと同じです。カーソルの最後の実行に対するランタイム統計を表示します。

フォーマット・キーワードに-という接頭辞を付けて、指定した情報を除外することができます。たとえば、'-PROJECTION'と指定すると、投影情報が除外されます。

使用上の注意

例

現行セッションで最後に実行されたSQL文の実行計画を表示するには、次のように入力します。

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_CURSOR);

SQL ID 'atfwcg8anrykp'に関連付けられたすべての子の実行計画を表示するには、次のように入力します。

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));

前の文中に指定されているカーソルに対するランタイム統計を表示するには、次のように入力します。

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS LAST');

DISPLAY_SQL_PLAN_BASELINEファンクション

このテーブル・ファンクションは、SQL計画ベースラインの指定したSQLハンドルの1つ以上の実行計画を表示します。

構文

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
   sql_handle      IN VARCHAR2 := NULL,
   plan_name       IN VARCHAR2 := NULL,
   format          IN VARCHAR2 := 'TYPICAL')
 RETURN dbms_xplan_type_table;

パラメータ

表164-5 DISPLAY_SQL_PLAN_BASELINEファンクションのパラメータ

パラメータ 説明

sql_handle

SQL文のハンドル。 計画を表示するSQL文を識別します。

plan_name

計画名。特定の計画を識別します。デフォルト値のNULLは、識別されたSQL文に関連付けられたすべての計画が説明され、表示されることを意味します。

format

書式文字列によって、計画に格納されている情報のうちどの情報が表示されるかが決まります。それぞれ一般的な使用例を表す3つの書式値（'BASIC'、'TYPICAL'、'ALL'）のいずれかを使用できます。

戻り値

PL/SQLタイプの表

使用上の注意

このプロシージャは計画のベースラインに格納された計画情報を使用して、計画を説明および表示します。SQL管理ベースに格納されたplan_idは、生成された計画のplan_idと一致しない可能性があります。 格納されたplan_idと生成されたplan_idとの不一致は、生成された計画が再生成不可能な計画であることを意味します。このような計画は無効とみなされ、SQLコンパイル時にオプティマイザによってバイパスされます。

例

TYPICAL形式を使用して、SQLハンドル'SYS_SQL_b1d49f6074ab95af'によって識別されるSQL文のすべての計画を表示します。

SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
  FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
                 'SYS_SQL_b1d49f6074ab95af')) t;

BASIC形式を使用して、文字列'HR2'を含む1つ以上のSQL文のすべての計画を表示します。

SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
   FROM (SELECT DISTINCT sql_handle FROM dba_sql_plan_baselines
         WHERE sql_text like '%HR2%') pb,
        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(pb.sql_handle, NULL,
                                                   'BASIC')) t;

DISPLAY_SQLSETファンクション

このテーブル・ファンクションは、SQLチューニング・セットに格納されている指定した文の実行計画を表示します。

構文

DBMS_XPLAN.DISPLAY_SQLSET(
   sqlset_name      IN  VARCHAR2,
   sql_id           IN  VARCHAR2,
   plan_hash_value  IN NUMBER := NULL,
   format           IN  VARCHAR2  := 'TYPICAL',
   sqlset_owner     IN  VARCHAR2  := NULL)
  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

パラメータ

表164-6 DISPLAY_SQLSETファンクションのパラメータ

パラメータ 説明

sqlset_name

SQLチューニング・セットの名前。

sql_id

SQLチューニング・セットにプランを格納しているSQL文のsql_id値を指定します。 テーブル・ファンクションDBMS_SQLTUNE.SELECT_SQLSETを問い合せると、格納済のすべてのSQL文を検出できます。

plan_hash_value

オプション・パラメータ。SQL文の特定の格納済実行計画を指定します。指定しない場合は、格納済のすべての実行計画が表示されます。

format

プランの詳細レベルを制御します。次の4つの値を指定できます。

  • BASIC: プランの最小限の情報として、操作ID、操作名および操作オプションを表示します。

  • TYPICAL: これがデフォルトです。プランに関する最も一般的な情報（操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト）が表示されます。プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。PROJECTION、ALIASおよびREMOTE SQLの情報のみが除外されます（説明の後半を参照）。

  • SERIAL: TYPICALと同じですが、プランがパラレルで実行される場合でもパラレル情報は表示されません。

  • ALL: 最高のユーザー・レベルです。TYPICALレベルで表示される情報に加えて、追加情報（PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報）が表示されます。

表示出力をより細かく制御するために、前述の3つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ（PARTITIONなど）または基となるPLAN TABLEの出力への論理的追加（PREDICATEなど）を表します。フォーマット・キーワードは、カンマまたはスペースで区切る必要があります。

  • ROWS - 関連がある場合は、オプティマイザで推定された行数を表示します。

  • BYTES - 関連がある場合は、オプティマイザで推定されたバイト数を表示します。

  • COST - 関連がある場合は、オプティマイザ・コスト情報を表示します。

  • PARTITION - 関連がある場合は、パーティション・プルーニング情報を表示します。

  • PARALLEL - 関連がある場合は、PX情報（配布方式およびテーブル・キュー情報）を表示します。

  • PREDICATE - 関連がある場合は、predicateセクションを表示します。

  • PROJECTION - 関連がある場合は、projectionセクションを表示します。

  • ALIAS - 関連がある場合は、Query Block Name/Object Aliasセクションを表示します。

  • REMOTE - 関連がある場合は、分散問合せ（シリアル分散およびリモートSQLを使用したリモート問合せなど）に関する情報を表示します。

  • NOTE - 関連がある場合は、EXPLAIN PLANのnoteセクションを表示します。

  • IOSTATS - （gather_plan_statisticsヒントを使用するか、またはstatistics_levelパラメータをALLに設定することによって）SQL文の実行時に基本的なプランの統計情報が収集されるとすると、このフォーマットによって、カーソルのALLの実行に対する（または後述するLASTの実行のみに対する）IO統計情報が表示されます。

  • MEMSTATS - PGAメモリー管理が有効化されている（pga_aggregate_targetパラメータが0（ゼロ）以外の値に設定されている）とすると、このフォーマットによって、メモリー管理の統計情報（演算子の実行モード、メモリー使用量、ディスクに書き出されるバイト数など）を表示できます。これらの統計情報は、メモリーが集中的に使用される操作（ハッシュ結合、ソート、一部のビットマップ演算子など）にのみ適用されます。

  • ALLSTATS - 'IOSTATS MEMSTATS'のショートカット。

  • LAST - デフォルトでは、カーソルのすべての実行に対してプランの統計情報が表示されます。LASTキーワードを指定すると、最後の実行に対する統計情報のみを表示できます。

次の2つのフォーマットは、使用しないことをお薦めしますが、下位互換性を維持する目的でサポートされています。

  • RUNSTATS_TOT - IOSTATSと同じです。指定したカーソルのすべての実行に対してIO統計情報を表示します。

  • RUNSTATS_LAST - IOSTATS LASTと同じです。カーソルの最後の実行に対するランタイム統計を表示します。

フォーマット・キーワードに-という接頭辞を付けて、指定した情報を除外することができます。たとえば、'-PROJECTION'と指定すると、投影情報が除外されます。

sqlset_owner

SQLチューニング・セットの所有者。デフォルトは現行のユーザーです。

使用上の注意

formatパラメータのバリエーションの例を次に示します。

例

'OLTP_optimization_0405'という名前のSQLチューニング・セットのSQL ID 'gwp663cqh5qbf'およびPLAN HASH 3693697075に関連付けられたSQL文の実行計画を表示するには、次のように入力します。

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));

SQLチューニング・セットに格納されているSQL ID 'atfwcg8anrykp'のすべての実行計画を表示するには、次のように入力します。

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLTP_optimization_0405','gwp663cqh5qbf'));

前の文中に指定されているSQL文に対するランタイム統計を表示するには、次のように入力します。

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLTP_optimization_0405', 'gwp663cqh5qbf', NULL, 'ALLSTATS LAST');
  戻る
戻る 		 次へ
次へ
  目次
目次		 索引
索引