217 DBMS_XPLAN
DBMS_XPLANパッケージによって、EXPLAIN PLANコマンドの出力を事前定義した複数の書式で表示する簡単な方法が提供されます。
また、DBMS_XPLANパッケージを使用して、自動ワークロード・リポジトリ(AWR)またはSQLチューニング・セットに格納されている文の計画を表示することもできます。さらに、V$SQL_PLAN固定ビューおよびV$SQL_PLAN_STATISTICS_ALL固定ビューに格納されている情報に基づいてキャッシュしたSQLカーソルについて、SQL実行計画およびSQL実行ランタイム統計を表示することもできます。最後に、SQLプラン・ベースラインから計画を表示します。
参照:
-
EXPLAINPLANコマンド、AWRおよびSQLチューニング・セットの詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。 -
V$SQL_PLAN固定ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。 -
V$SQL_PLAN_STATISTICS固定ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
この章のトピックは、次のとおりです:
217.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チューニング・セット内に格納されている文について、その実行計画内容を書式設定して表示します。
217.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の一部として自動的に付与されます。
217.3 DBMS_XPLANのデータ構造
DBMS_XPLANパッケージでは、TABLEタイプを定義します。
217.3.1 DBMS_XPLAN PLAN_OBJECT_LIST表タイプ
このタイプにより、COMPARE_PLANSファンクションへの入力として汎用オブジェクトのリストが使用できるようになります。
構文
TYPE plan_object_list IS TABLE OF generic_plan_object;汎用オブジェクトは、すべての計画ソースからの計画に共通する属性を抽象化します。すべての計画ソースは、plan_object_listスーパークラスのサブクラスです。次の表は、各種計画ソースのまとめです。オプションのパラメータがnullの場合は、複数のオブジェクトに対応できる点に注意してください。たとえば、cursor_cache_objectに子番号を指定していない場合は、指定したSQL IDのすべてのカーソル・キャッシュ文と一致します。
表217-1 PLAN_OBJECT_LISTの計画ソース
| 計画ソース | 指定 | 説明 |
|---|---|---|
|
PLAN TABLE |
|
このパラメータは次のとおりです。
|
|
カーソル・キャッシュ |
|
このパラメータは次のとおりです。
|
|
AWR |
|
このパラメータは次のとおりです。
|
|
SQLチューニング・セット |
|
このパラメータは次のとおりです。
|
|
SQL計画管理 |
|
このパラメータは次のとおりです。
|
|
SQLプロファイル |
|
|
|
アドバイザ |
|
このパラメータは次のとおりです。
|
217.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;
217.5 DBMS_XPLANサブプログラムの要約
この表は、DBMS_XPLANサブプログラムを示し、簡単に説明しています。
表217-2 DBMS_XPLANパッケージのサブプログラム
| サブプログラム | 説明 |
|---|---|
|
リスト内の各計画と参照プランを比較してレポートを返します。 |
|
|
計画を比較します。 |
|
|
PLAN TABLEの内容を表示します。 |
|
|
AWR内に格納されている実行計画の内容を表示します。 |
|
|
カーソル・キャッシュ内の任意のカーソルの実行計画を表示します。 |
|
|
|
|
|
SQLプラン・ベースラインの指定したSQLハンドルの1つ以上の実行計画を表示します。 |
|
|
SQLチューニング・セットに格納されている指定した文の実行計画を表示します。 |
217.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;
パラメータ
表217-3 COMPARE_PLANSファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
参照プラン。この計画は常に単一の計画として評価される必要があります。 |
|
|
参照プランと比較する計画のリスト。 |
|
|
レポートのタイプ。使用可能な値は次のとおりです。
|
|
|
レポートの形式。使用可能な値は次のとおりです。
|
|
|
レポート内の特定のセクション。使用可能な値は次のとおりです。
|
例217-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アドバイザに格納されているすべてのプラン。
217.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;
パラメータ
表217-4 DIFF_PLANファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQL文のテキスト |
|
|
ターゲット・プランの生成に使用されます。 |
|
|
解析スキーマ名は現在のユーザーにデフォルト設定されます。 |
217.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);パラメータ
表217-5 DISPLAYファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
計画が格納されている表の名前を指定します。このパラメータのデフォルトは |
|
|
表示する計画の |
|
|
計画の詳細レベルを制御します。次のいずれかの値を受け入れます。
表示出力をより細かく制御するために、前述の3つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ(
フォーマット・キーワードに 目的のPLAN TABLE( |
|
|
計画を格納する表から選択される行のセットを制限するSQLフィルタ述語。値が 計画を格納する表のすべての列を参照できます。また、副問合せ、ファンクション・コールなどのすべての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'));217.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);パラメータ
表217-6 DISPLAY_AWRテーブル・ファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQL文の |
|
|
SQL文の |
|
|
|
|
|
計画の詳細レベルを制御します。次の4つの値を指定できます。
表示出力をより細かく制御するために、前述の4つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ(
フォーマット・キーワードに |
使用上のノート
-
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%';217.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');パラメータ
表217-7 DISPLAY_CURSORファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
カーソル・キャッシュ内のSQL文の |
|
|
表示するカーソルの子番号。指定しない場合は、指定したsql_idパラメータに一致する |
|
|
計画の詳細レベルを制御します。次の5つの値を受け入れます。
表示出力をより細かく制御するために、前述のフォーマットのオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ( フォーマット・キーワードは、カンマまたはスペースで区切る必要があります。
次のフォーマットは非推奨になっていますが、下位互換性を維持する目的でサポートされています。
フォーマットのキーワードに' |
使用上のノート
-
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');217.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;パラメータ
表217-8 DISPLAY_PLANファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
計画が格納されている表の名前を指定します。このパラメータのデフォルトは |
|
|
表示する計画の |
|
|
計画を格納する表から選択される行のセットを制限するSQLフィルタ述語。値が 計画を格納する表のすべての列を参照できます。また、副問合せ、ファンクション・コールなどのすべてのSQL構文(「使用上のノート」の |
|
|
計画の詳細レベルを制御します。次の5つの値を受け入れます。
表示出力をより細かく制御するために、前述の3つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ(
フォーマット・キーワードに 目的のPLAN TABLE( |
|
|
出力タイプは次のいずれかです。 |
戻り値
要求されたレポートをCLOBとして戻します。
使用上のノート
アクティブ・レポートには、Enterprise Managerのインタフェースと類似の豊富な対話型ユーザー・インタフェースがあります(Enterprise Managerをインストールする必要はありません)。レポート・ファイルはHTML形式で作成されるため、最近のほとんどのブラウザで解釈可能です。アクティブ・レポートに機能を付与するコードは、最初に表示したときにWebブラウザにより透過的にダウンロードされるため、表示には外部接続が必要です。
警告:
filter_predsパラメータを公開することによって、アプリケーションがSQLインジェクションの影響を受ける可能性があるため、アプリケーション開発者は、十分に検討した後でのみこのパラメータをエンド・ユーザーに公開してください。実際、filter_predsを使用すると、すべての表の参照、またはテーブル・ファンクションを起動するデータベース・ユーザーが権限を持つすべてのサーバー・ファンクションの実行を行うことができます。
217.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;パラメータ
表217-9 DISPLAY_SQL_PLAN_BASELINEファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQL文のハンドル。計画を表示するSQL文を識別します。 |
|
|
計画名。特定の計画を識別します。デフォルト値のNULLは、識別されたSQL文に関連付けられたすべての計画が説明され、表示されることを意味します。 |
|
|
書式文字列によって、計画に格納されている情報のうちどの情報が表示されるかが決まります。指定可能なフォーマット値は、一般的なユースケースを表す |
戻り値
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;217.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;パラメータ
表217-10 DISPLAY_SQLSETファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQLチューニング・セットの名前。 |
|
|
SQLチューニング・セットに計画を格納しているSQL文のsql_id値を指定します。テーブル・ファンクション |
|
|
オプションのパラメータ。SQL文の特定の格納済実行計画を指定します。指定しない場合は、格納済のすべての実行計画が表示されます。 |
|
|
計画の詳細レベルを制御します。次の4つの値を指定できます。
表示出力をより細かく制御するために、前述の3つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ(
次の2つのフォーマットは非推奨となっていますが、下位互換性を維持する目的でサポートされています。
フォーマット・キーワードに |
|
|
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');217.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'
);パラメータ
表217-11 DISPLAY_WORKLOAD_REPOSITORY Tableファンクションのパラメータ
| パラメータ | 説明 |
|---|---|
|
|
SQL文の
|
|
|
SQL文の このパラメータはオプションです。省略すると、このテーブル・ファンクションは、指定の |
|
|
計画の詳細レベルを制御します。次の4つの値を指定できます。
表示出力をより細かく制御するために、前述の4つの標準的なformatオプションに次のキーワードを追加して、デフォルトの動作をカスタマイズできます。各キーワードは、PLAN TABLEの列の論理グループ(
フォーマット・キーワードに |
|
|
特定のデータベースの計画を識別します。 このパラメータを省略すると、その値は初期化パラメータ |
|
|
特定のコンテナの計画を識別します。 このパラメータを省略すると、その値は |
|
|
AWRリポジトリの場所を指定します。サポートされる値は、次のとおりです。
|
例217-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"