機械翻訳について

242 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 SQLチューニング・ガイド』を参照してください。

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

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

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

• 概要

• セキュリティ・モデル

• 例

• DBMS_XPLANサブプログラムの要約

242.1 DBMS_XPLAN概要

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

これらのファンクションを次に示します。

• DISPLAY - PLAN TABLEの内容を書式設定して表示します。

• DISPLAY_AWR - AWR内に格納されているSQL文について、その実行計画内容を書式設定して表示します。

• DISPLAY_CURSOR - ロードした任意のカーソルについて、その実行計画内容を書式設定して表示します。

• DISPLAY_SQL_PLAN_BASELINE - SQLハンドルによって識別されるSQL文の1つ以上の実行計画を表示します。

• DISPLAY_SQLSET - SQLチューニング・セット内に格納されている文について、その実行計画内容を書式設定して表示します。

242.2 DBMS_XPLANのセキュリティ・モデル

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

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

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

DISPLAY_SQL_PLAN_BASELINEファンクションを使用する場合、ユーザーには、ユーザーが計画を取得しようと試みるSQL文を実行する権限に加えて、DBA_SQL_PLAN_BASELINESに対するSELECTまたはREAD権限が必要です。

前述の権限は、SELECT_CATALOG_ROLEの一部として自動的に付与されます。

242.3 DBMS_XPLANのデータ構造

DBMS_XPLANパッケージでは、TABLEタイプを定義します。

表タイプ

• DBMS_XPLAN PLAN_OBJECT_LIST表タイプ

242.3.1 DBMS_XPLAN PLAN_OBJECT_LIST表タイプ

このタイプにより、COMPARE_PLANSファンクションへの入力として汎用オブジェクトのリストが使用できるようになります。

構文

TYPE plan_object_list IS TABLE OF generic_plan_object;

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

表242-1 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 (オプション)

242.4 サンプル

次の例は、DBMS_XPLANの使用を示します。

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 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 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, 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 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;

242.5 DBMS_XPLANサブプログラムの要約

この表は、DBMS_XPLANサブプログラムを示し、簡単に説明しています。

表242-2 DBMS_XPLANパッケージ・サブプログラム

サブプログラム	説明
COMPARE_PLANSファンクション	リスト内の各計画と参照プランを比較してレポートを返します。
DIFF_PLANファンクション	計画を比較します。
DISPLAYファンクション	PLAN TABLEの内容を表示します。
DISPLAY_AWRファンクション	AWR内に格納されている実行計画の内容を表示します。
DISPLAY_CURSORファンクション	カーソル・キャッシュ内の任意のカーソルの実行計画を表示します。
DISPLAY_PLANファンクション	CLOB出力タイプの様々な形式でPLAN TABLEの内容を表示します。
DISPLAY_SQL_PLAN_BASELINEファンクション	SQLプラン・ベースラインの指定したSQLハンドルの1つ以上の実行計画を表示します。
DISPLAY_SQLSETファンクション	SQLチューニング・セットに格納されている指定した文の実行計画を表示します。

242.5.1 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;

パラメータ

表242-3 COMPARE_PLANSファンクションのパラメータ

パラメータ	説明
reference_plan	参照プラン。 この計画は常に単一の計画として評価される必要があります。
compare_plan_list	参照プランと比較する計画のリスト。 compare_plan_listは、generic_objectのリストです。それぞれのgeneric_objectは、1つ以上の計画に対応しています。
type	レポートのタイプ。 使用可能な値は次のとおりです。 TEXT HTML XML
level	レポートの形式。 使用可能な値は次のとおりです。 BASIC TYPICAL ALL
section	レポート内の特定のセクション。 使用可能な値は次のとおりです。 SUMMARY FINDINGS PLANS INFORMATION ERRORS

例242-1 サンプル

次の例は、COMPARE_PLANSファンクションの使用方法を示しています。

var report clob; 
exec :report := dbms_xplan.compare_plans(cursor_cache_object(‘8mkxm7ur07za0’, 2), 
                - plan_object_list (cursor_cache_object(‘8mkxm7ur07za0’, 4))); 

print report

前述の例では、SQL IDが'8mkxm7ur07za0'で子カーソル番号が2の計画と、同じSQL IDで子カーソル番号が4の計画を比較しています。 テキスト形式(デフォルト)でレポートを返します。

var report clob; 
exec :report := dbms_xplan.compare_plans(cursor_cache_object(‘8mkxm7ur07za0’, 2), 
                plan_object_list( spm_object(‘SQL_024d0f7d21351f5d’, ‘SQL_PLAN_sdfjkd’))); 

print report

前述の例では、SQL IDが'8mkxm7ur07za0'で子カーソル番号が2の計画と、SQLハンドルが'SQL_024d0f7d21351f5d'で計画名が'SQL_PLAN_sdfjkd'の問合せのSPMで取得される計画ベースラインの計画を比較しています。 テキスト形式(デフォルト)でレポートを返します。

var report clob; 
exec :report = dbms_xplan.compare_plans(cursor_cache_object(‘8mkxm7ur07za0’, 2), 
               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’, NULL, 38), sql_profile_object(‘pe3r3ejsfd’), a               dvisor_object(‘TASK_1228’, ‘EXEC_1928’, ‘8mkxm7ur07za0’)), type => ‘XML’,
               level => ‘ALL’, section => ‘SUMMARY’);

前述の例では、SQL IDが'8mkxm7ur07za0'で子カーソル番号が2の計画と、次のリストの各計画を比較しています。

• cursor_cache_object('8mkxm7ur07za0'): SQL ID '8mkxm7ur07za0'に対して生成されたカーソル・キャッシュ内のすべての計画。

• sqlset_object('SH','SQLT_WORKLOAD','6vfqvav0rgyad'): SQLチューニング・セットSHで生成されたすべての計画。 SQL ID '6vfqvav0rgyad'のSQLT_WORKLOAD。

• awr_object('6vfqvav0rgyad', 5): データベースID 5およびSQL ID '6vfqvav0rgyad'に対して取得されたAWR内のすべての計画。

• spm_object ('SQL_024d0f7d21351f5d, 'SQL_PLAN_sdfjkd'): 'SQL_PLAN_sdfjkd'という名前のSQLハンドル'SQL_024d0f7d21351f5d'の問合せに対する計画ベースライン。

• plan_table_object('SH','plan_table',NULL,38): plan_id=38で識別されるSH.plan_tableに格納された計画。

• sql_profile_object('pe3r3ejsfd'): SQLプロファイル名'pe3r3ejsfd'で識別される計画。

• advisor_object('TASK_1228','EXEC_1928','8mkxm7ur07za0'): タスク名'TASK_1228'、実行名'EXEC_1928'およびSQL ID'8mkxm7ur07za0'で識別されるSQLアドバイザに格納されているすべてのプラン。

242.5.2 DIFF_PLANファンクション

このファンクションは、2つのSQLプラン、参照プランおよびターゲット・プランを比較します。 このファンクションは、検索結果のレポートの取得に使用できるtask_idを返します。

構文

DBMS_XPLAN.DIFF_PLAN(
   sql_text    IN   CLOB,
   outline     IN   CLOB,
   user_name   IN   VARCHAR2 := 'NULL')
 RETURN VARCHAR2;

パラメータ

表242-4 DIFF_PLANファンクションのパラメータ

パラメータ	説明
sql_text	SQL文のテキスト
outline	ターゲット・プランの生成に使用されます。
user_name	解析スキーマ名は現在のユーザーにデフォルト設定されます。

242.5.3 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);

パラメータ

表242-5 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	計画の詳細レベルを制御します。 次のいずれかの値を受け入れます。 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パラメータのバリエーションの例を次に示します。

• projectionおよびnoteセクションを除くすべてを表示する場合は、'ALL -PROJECTION -NOTE'を使用します。

• TYPICALフォーマットに(TYPICALフォーマットでは通常除外されている)projectionセクションを追加して表示する場合は、'TYPICAL PROJECTION'を使用します。 TYPICALはデフォルトであるため、'PROJECTION'を使用するのみで同様に表示できます。

• TYPICALフォーマットを使用して、オプティマイザ・コスト、推定バイト数およびpredicateセクションを除外して表示する場合は、'-BYTES -COST -PREDICATE'を使用します。

• BASICレベルの情報にオプティマイザによる推定バイト数を追加して表示する場合は、'BASIC ROWS'を使用します。

  警告:

  filter_predsパラメータを公開することによって、アプリケーションがSQLインジェクションの影響を受ける可能性があるため、アプリケーション開発者は、十分に検討した後でのみこのパラメータをエンド・ユーザーに公開してください。 実際、filter_predsを使用すると、すべての表の参照、またはテーブル・ファンクションを起動するデータベース・ユーザーが権限を持つすべてのサーバー・ファンクションの実行を行うことができます。

例

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='sales_query')の計画を表示するには、次のようにします。

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

242.5.4 DISPLAY_AWRファンクション

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

ノート:

この関数は非推奨です。 かわりにDISPLAY_WORKLOAD_REPOSITORYを使用します。 DISPLAY_AWRはローカルDBIDのスナップショットでのみ動作しますが、DISPLAY_WORKLOAD_REPOSITORYは、リモートおよびインポートされたスナップショットを含む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);

パラメータ

表242-6 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に関する情報)が表示されます。 表示出力をより細かく制御するために、前述の4つの標準的な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'と指定すると、投影情報が除外されます。

使用上のノート

• DISPLAY_AWR機能を使用する場合、コール・ユーザーにはDBA_HIST_SQL_PLAN、AWR_ROOT_SQL_PLAN、AWR_PDB_SQL_PLAN、DBA_HIST_SQLTEXT、AWR_ROOT_SQLTEXT、AWR_PDB_SQLTEXTおよびV$DATABASEに対するSELECTまたはREAD権限が必要です。権限がない場合は、エラー・メッセージが表示されます。 デフォルトでは、これらのビューの選択権限はselect_catalogロールに付与されます。

• 次の例は、formatパラメータの様々な使用方法を示しています。

  • BASICレベルの情報にオプティマイザによる推定バイト数を追加して表示する場合は、'BASIC ROWS'を使用します。

  • projectionおよびnoteセクションを除くすべてを表示する場合は、'ALL -PROJECTION -NOTE'を使用します。

  • TYPICALフォーマットに(TYPICALフォーマットでは通常除外されている)projectionセクションを追加して表示する場合は、'TYPICAL PROJECTION'を使用します。 TYPICALはデフォルトであるため、'PROJECTION'を使用するのみで同様に表示できます。

  • TYPICALフォーマットを使用して、オプティマイザ・コスト、推定バイト数およびpredicateセクションを除外して表示する場合は、'-BYTES -COST -PREDICATE'を使用します。

例

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%';

242.5.5 DISPLAY_CURSORファンクション

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

構文

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

パラメータ

表242-7 DISPLAY_CURSORファンクションのパラメータ

パラメータ	説明
sql_id	カーソル・キャッシュ内のSQL文のSQL_IDを指定します。 V$SQLまたはV$SQLAREA内の列SQL_IDに問い合せることによって、適切な値を取り出せます。 また、V$SESSIONから特定セッションに対する列PREV_SQL_IDを選択することもできます。 このパラメータのデフォルトはNULLで、この場合、セッションで最後に実行されたカーソルが表示されます。
cursor_child_no	表示するカーソルの子番号。 指定しない場合は、指定したsql_idパラメータに一致するchild_number=0カーソルの実行計画が表示されます。 child_numberは、sql_idが指定されている場合にのみ指定できます。
format	計画の詳細レベルを制御します。 次の5つの値を受け入れます。 BASIC: 計画の最小限の情報として、操作ID、操作名および操作オプションを表示します。 TYPICAL: これがデフォルトです。 計画に関する最も一般的な情報(操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト)が表示されます。 プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。 PROJECTION、ALIASおよびREMOTE SQLの情報のみが除外されます(説明の後半を参照)。 SERIAL: TYPICALと同じですが、計画がパラレルで実行される場合でもパラレル情報は表示されません。 ALL: 最高のユーザー・レベルです。 TYPICALレベルで表示される情報に加えて、追加情報(PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報)が表示されます。 ADAPTIVE: 最終計画を表示します。実行が終了していない場合は、現在の計画を表示します。 この項では、ネステッド・ループ結合からハッシュ結合への切替えなどの計画に影響するランタイム最適化についてのノートをあげています。 計画の系統。 このセクションでは、自動再最適化のために以前に実行された計画を示しています。 また、動的計画のために計画が変更された場合には、デフォルト計画も示しています。 推奨された計画。 レポート作成モードでは、計画は表示された実行統計に基づいて選択されます。 自動再最適化の推奨計画を表示するには、子カーソルで収集されたオプティマイザ調整を使用して問合せを再コンパイルする必要があります。 動的計画に推奨計画を表示する場合、これは必要ありません。 動的計画。 これは、オプティマイザにより選択されたデフォルト計画とは異なる計画の部分を要約したものです。 表示出力をより細かく制御するために、前述のフォーマットのオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。 各キーワードは、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キーワードを指定すると、最後の実行に対する統計情報のみを表示できます。 次のフォーマットは非推奨になっていますが、下位互換性を維持する目的でサポートされています。 RUNSTATS_TOT - IOSTATSと同じです。指定したカーソルのすべての実行に対してIO統計情報を表示します。 RUNSTATS_LAST - IOSTATS LASTと同じです。カーソルの最後の実行に対するランタイム統計を表示します。 フォーマットのキーワードに'-'記号の接頭辞を付けると、指定した情報を除外できます。 たとえば、'-PROJECTION'と指定すると、投影情報が除外されます。

使用上のノート

• DISPLAY_CURSOR機能を使用する場合、コール・ユーザーには、固定ビューV$SQL_PLAN_STATISTICS_ALL、V$SQLおよびV$SQL_PLANに対するSELECTまたはREAD権限が必要です。権限がない場合は、エラー・メッセージが表示されます。

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

  • projectionおよびnoteセクションを除くすべてを表示する場合は、'ALL -PROJECTION -NOTE'を使用します。

  • TYPICALフォーマットに(TYPICALフォーマットでは通常除外されている)projectionセクションを追加して表示する場合は、'TYPICAL PROJECTION'を使用します。 TYPICALはデフォルトであるため、'PROJECTION'を使用するのみで同様に表示できます。

  • TYPICALフォーマットを使用して、オプティマイザ・コスト、推定バイト数およびpredicateセクションを除外して表示する場合は、'-BYTES -COST -PREDICATE'を使用します。

  • BASICレベルの情報にオプティマイザによる推定バイト数を追加して表示する場合は、'BASIC ROWS'を使用します。

例

現行セッションで最後に実行された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');

242.5.6 DISPLAY_PLANファンクション

このテーブル・ファンクションは、CLOB出力タイプの様々な形式でPLAN TABLEの内容を表示します。

構文

DBMS_XPLAN.DISPLAY_PLAN (
   table_name       IN    VARCHAR2   DEFAULT 'PLAN_TABLE',
   statement_id     IN    VARCHAR2   DEFAULT NULL,
   format           IN    VARCHAR2   DEFAULT 'TYPICAL',
   filter_preds     IN    VARCHAR2   DEFAULT NULL,
   type             IN    VARCHAR2   DEFAULT 'TEXT')
  RETURN CLOB;

パラメータ

表242-8 DISPLAY_PLANファンクションのパラメータ

パラメータ	説明
table_name	計画が格納されている表の名前を指定します。 このパラメータのデフォルトはPLAN_TABLE,で、これはEXPLAIN PLANコマンドに対するデフォルトのPLAN TABLEです。 NULLが指定された場合も、デフォルトのPLAN_TABLEになります。
statement_id	表示する計画のstatement_idを指定します。 デフォルトはNULL(EXPLAIN PLANコマンドがset statement_id句なしで実行された場合のデフォルト値)です。statement_idを指定しない場合は、説明された文のうちで直近のものについて、計画が表示されます。
filter_preds	計画を格納する表から選択される行のセットを制限するSQLフィルタ述語。 値がNULL(デフォルト値)の場合は、最後に実行されたEXPLAIN PLANに応じて計画が表示されます。 たとえば、filter_preds=>'plan_id = 10'などです。 計画を格納する表のすべての列を参照できます。また、副問合せ、ファンクション・コールなどのすべてのSQL構文(「使用上のノート」の「警告」を参照)を格納できます。
format	計画の詳細レベルを制御します。 次の5つの値を受け入れます。 BASIC: 計画の最小限の情報として、操作ID、操作名および操作オプションを表示します。 TYPICAL: これがデフォルトです。 計画に関する最も一般的な情報(操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト)が表示されます。 プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。 PROJECTION、ALIASおよびREMOTE SQLの情報のみが除外されます(説明の後半を参照)。 SERIAL: TYPICALと同じですが、計画がパラレルで実行される場合でもパラレル情報は表示されません。 ALL: 最高のユーザー・レベルです。 TYPICALレベルで表示される情報に加えて、追加情報(PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報)が表示されます。 ADAPTIVE: デフォルト計画を動的サブ計画ごとに(規定されている場合)表示します。 - 置換される可能性のある元の行ソース、およびそれを置換する行ソースを示したリスト - アウトライン表示がフォーマット引数で指定されている場合は、動的サブ計画のオプション別にヒントが表示されます。 表示出力をより細かく制御するために、前述の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ファンクション」を参照してください)。
type	出力タイプは次のいずれかです。'TEXT'、'ACTIVE'、'HTML'または'XML'(ACTIVEタイプに関する「使用上のノート」を参照) '

戻り値

要求されたレポートをCLOBとして戻します。

使用上のノート

アクティブ・レポートには、Enterprise Managerのインタフェースと類似の豊富な対話型ユーザー・インタフェースがあります(Enterprise Managerをインストールする必要はありません)。 レポート・ファイルはHTML形式で作成されるため、最近のほとんどのブラウザで解釈可能です。 アクティブ・レポートに機能を付与するコードは、最初に表示したときにWebブラウザにより透過的にダウンロードされるため、表示には外部接続が必要です。

警告:

filter_predsパラメータを公開することによって、アプリケーションがSQLインジェクションの影響を受ける可能性があるため、アプリケーション開発者は、十分に検討した後でのみこのパラメータをエンド・ユーザーに公開してください。 実際、filter_predsを使用すると、すべての表の参照、またはテーブル・ファンクションを起動するデータベース・ユーザーが権限を持つすべてのサーバー・ファンクションの実行を行うことができます。

242.5.7 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;

パラメータ

表242-9 DISPLAY_SQL_PLAN_BASELINEファンクションのパラメータ

パラメータ	説明
sql_handle	SQL文のハンドル。 計画を表示するSQL文を識別します。
plan_name	計画名。 特定の計画を識別します。 デフォルト値のNULLは、識別されたSQL文に関連付けられたすべての計画が説明され、表示されることを意味します。
format	書式文字列によって、計画に格納されている情報のうちどの情報が表示されるかが決まります。 指定可能なフォーマット値は、一般的なユースケースを表す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;

242.5.8 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;

パラメータ

表242-10 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 - SQL文の実行時に基本的な計画の統計情報が収集される(gather_plan_statisticsヒントを使用するか、STATISTICS_LEVELパラメータをALLに設定する)とすると、このフォーマットでは、カーソルの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パラメータのバリエーションの例を次に示します。

• projectionおよびnoteセクションを除くすべてを表示する場合は、'ALL -PROJECTION -NOTE'を使用します。

• TYPICALフォーマットに(TYPICALフォーマットでは通常除外されている)projectionセクションを追加して表示する場合は、'TYPICAL PROJECTION'を使用します。 TYPICALはデフォルトであるため、'PROJECTION'を使用するのみで同様に表示できます。

• TYPICALフォーマットを使用して、オプティマイザ・コスト、推定バイト数およびpredicateセクションを除外して表示する場合は、'-BYTES -COST -PREDICATE'を使用します。

• BASICレベルの情報にオプティマイザによる推定バイト数を追加して表示する場合は、'BASIC ROWS'を使用します。

例

'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');

242.5.9 DISPLAY_WORKLOAD_REPOSITORYファンクション

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

ノート:

このファンクションは、非推奨になったDISPLAY_AWRを代替するものです。

構文

DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY(
   sql_id            IN      VARCHAR2,
   plan_hash_value   IN      NUMBER   DEFAULT NULL,
   format            IN      VARCHAR2 DEFAULT 'TYPICAL'
   dbid              IN      NUMBER   DEFAULT NULL,
   con_dbid          IN      NUMBER   DEFAULT NULL,
   awr_location      IN      VARCHAR2 DEFAULT 'AWR_ROOT'
);

パラメータ

表242-11 DISPLAY_WORKLOAD_REPOSITORY表ファンクションのパラメータ

パラメータ	説明
sql_id	SQL文のSQL_IDを指定します。 DBA_HIST_SQLTEXT内の列SQL_IDを問い合せることによって、目的のSQL文に対する適切な値を取り出せます。
plan_hash_value	SQL文のPLAN_HASH_VALUEを指定します。 このパラメータはオプションです。 省略すると、このテーブル・ファンクションは、指定のSQL_IDに対する格納済のすべての実行計画を戻します。
format	計画の詳細レベルを制御します。 次の4つの値を指定できます。 BASIC: 計画の最小限の情報として、操作ID、操作名および操作オプションを表示します。 TYPICAL: これがデフォルトです。 計画に関する最も一般的な情報(操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト)が表示されます。 プルーニング情報、パラレル情報および述語情報は、適用可能な場合にのみ表示されます。 PROJECTION、ALIASおよびREMOTE SQLの情報のみが除外されます(説明の後半を参照)。 SERIAL: TYPICALと同じですが、計画がパラレルで実行される場合でもパラレル情報は表示されません。 ALL: 最高のユーザー・レベルです。 TYPICALレベルで表示される情報に加えて、追加情報(PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報)が表示されます。 表示出力をより細かく制御するために、前述の4つの標準的な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'と指定すると、投影情報が除外されます。
dbid	特定のデータベースの計画を識別します。 このパラメータを省略すると、その値は初期化パラメータAWR_LOCATIONで示されるAWRリポジトリのDBIDにデフォルト設定されます。 CDBの場合、AWR_LOCATIONがAWR_ROOTに設定されていると、その値はCDBルートのDBIDに設定されます。 AWR_PDBに設定されている場合、その値はコンテナのDBIDに設定されます。
con_dbid	特定のコンテナの計画を識別します。 このパラメータを省略すると、その値はSYS_CONTEXT('userenv', 'con_id')にデフォルト設定されます。
awr_location	AWRリポジトリの場所を指定します。 サポートされる値は、次のとおりです。 アクセスするAWRがルート・コンテナにある場合は、AWR_ROOT。 これはデフォルトです。 アクセスするAWRがローカル・コンテナ内にある場合は、'AWR_PDB'。

例242-2 AWR計画の問合せ

管理者としてログインして、次の問合せを発行するとします。

select count(*) from sh.sAleS

次のようにして、AWRスナップショットを作成します。

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

次のように、ファンクションの出力へのDBA_HIST_SQLTESTの結合を問い合せます。

SET LINESIZE 150
SET PAGESIZE 5000

SELECT t.*
FROM   DBA_HIST_SQLTEXT ht, 
       TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY
         (ht.sql_id, null, '-PREDICATE +ALIAS',null,null,'AWR_ROOT')) t
WHERE  ht.SQL_TEXT LIKE '%sAleS%';

SQL_ID 2f4cx9qjnqd70
--------------------
select count(*) from sh.sAleS

Plan hash value: 1123225294

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |       |    27 (100)|          |       |       |
|   1 |  SORT AGGREGATE                |                 |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL          |                 |   918K|    27   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT     |                 |   918K|    27   (0)| 00:00:01 |       |       |
|   4 |     BITMAP INDEX FAST FULL SCAN| SALES_PROMO_BIX |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   3 - SEL$1 / "SALES"@"SEL$1"