Oracle 実行計画の確認方法(DBMS_XPLAN.DISPLAY_CURSOR)

概要

SQLパフォーマンス調査で使用するDBMS_XPLANについてのまとめを記載します。

DBMS_XPLAN.DISPLAY_CURSORについて：
過去にDatabaseで実行されたSQLの実行計画を確認することが可能です。

また、この機能を使用することでSQL文の実行計画とそのSQLを実行した際の実行統計の情報を併記して表示することが可能です。
そのため、この機能によって、CBO(コストベース・オプティマイザ)の見積もりミスの有無やメモリ不足等の事象で作業効率が悪くなっていないかを確認したりすることが可能になります。

特徴

過去に実行されたSQL文の共有カーソルの情報がライブラリ・キャッシュに存在している場合に該当SQLの実行計画を確認することが可能です。
→ ライブラリ・キャッシュからage-outされていた場合は確認することが出来ません。
既に実行されキャッシュされている情報を検索するため、実行計画を確認するためにSQL文を再度解析する必要がなく、低コストで実行できます。
実行計画と併せて実行統計も表示することが可能なため、Estimate(見積)とActual(実測)の乖離の確認が容易です。

使用の前提条件

DISPLAY_CURSOR機能を使用する場合、ユーザーに下記ビューに対するSELECTまたはREAD権限が必要です。
権限がない場合は、エラー・メッセージが表示されます。
※DBMS_XPLANは内部的にこれらのビューに対する検索を実行し、情報を表示しています。

V$SQL_PLAN_STATISTICS_ALL
V$SQL
V$SQL_PLAN

パラメータ

DISPLAY_CURSORファンクションのパラメータは下記です。
   sql_id:カーソル・キャッシュ内のSQL文のSQL_IDを指定します。
              NULLの場合、セッションで最後に実行されたカーソルが表示されます。
   cursor_child_no:表示するカーソルの子番号を指定します
                              ※NULLの場合、指定のsql_idパラメータに一致するすべてのカーソルの実行計画を表示
   format:計画の詳細レベルを制御します。

format句の指定

BASIC：計画の最小限の情報として、操作ID、操作名および操作オプションを表示します。
TYPICAL：これがデフォルトです。計画に関する最も一般的な情報
(操作ID、操作名、操作オプション、行数、バイト数およびオプティマイザ・コスト)が表示されます。
SERIAL：TYPICALと同じですが、計画がパラレルで実行される場合でもパラレル情報は表示されません。
ALL：TYPICALレベルで表示される情報に加えて、
追加情報(PROJECTION、ALIAS、および分散処理が行われる場合はREMOTE SQLに関する情報)が表示されます。

ADVANCEDパラメータについて

マニュアルには記載がありませんが、上記以外にformat句に「ADVANCED」を指定することが可能です。
このオプションによって、OUTLINE(ヒント句)の情報などの追加情報が表示できます。

formatパラメータで指定可能な修飾子

Value	Description
adaptive	サブプランの表示を制御します。この修飾子は、バージョン12.1以降でのみ使用できます。
alias	クエリブロック名とオブジェクトエイリアスを含むセクションの表示を制御します。
bytes	実行計画テーブルのバイト列の表示を制御します。
cost	実行計画テーブルのCost列の表示を制御します。
note	ノートを含むセクションの表示を制御します。
outline	アウトラインを含むセクションの表示を制御します。
parallel	並列処理情報、具体的には実行計画表のTQ、IN-OUT、およびPQ Distrib列の表示を制御します。
partition	パーティション化情報、特に実行計画テーブルのPstart列とPstop列の表示を制御します。
peeked_binds	表示されたバインド変数を含むセクションの表示を制御します。
predicate	フィルター、アクセス、およびストレージの述部を含むセクションの表示を制御します。
projection	列投影情報を含むセクションの表示を制御します。
remote	リモートで実行されるSQL文の表示を制御します。
report	レポートモードのアクティブ化を制御します。有効にすると、適応および再最適化された実行計画に関する追加情報が表示されます。この修飾子は、バージョン12.1以降でのみ使用できます。
rows	実行計画テーブルの行列の表示を制御します。

format句に指定可能なパラメータの詳細については以下のマニュアルを参考にしてください。

https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_xplan.htm#sthref14602

Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 11g リリース2(11.2)
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_xplan.htm#i998364

https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_xplan.htm#i998364
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1)

https://docs.oracle.com/cd/E82638_01/ARPLS/DBMS_XPLAN.htm#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース2 (12.2)

詳細については「$ORACLE_HOME/rdbms/admin/dbmsxpln.sql」でも確認できます。

また、すべてのレベルは、データを削除するマイナス記号または追加するオプションのプラス記号とともに、詳細な制御を使用してカスタマイズできます。
例) 「ADVANCED -COST」や「BASIC +PEEKED_BINDS」

実行統計を併記する方法

実行統計の収集するには、SQL文の実行時に実行統計を取得する必要がありますが、SQL実行統計を収集する場合は、以下の3つの条件のいずれかを満たしている必要があります。

STATISTICS_LEVELパラメータをALLの状態でSQL文を実行する
SQL文にGATHER_PLAN_STATISTICSヒントを指定して実行する
SQLトレースを有効にしてSQL文を実行する

上記にて統計情報が収集されている場合には、DBMS_XPLAN.DISPLAY_CURSORにてformat句に以下のキーワードを指定します。

IOSTATS : I/Oに関する実行統計を表示
MEMSTATS : メモリに関する実行統計を表示
ALLSTATS : IOSTATSとMEMSTATSの両方を指定
LAST : このオプションが指定されていると特定のSQL文を最後に実行した際の1回分の統計を表示。
指定しない場合は同一SQL文の全実行を集計表示。
(注:パラレルクエリの場合にLASTを指定する場合は注意してください。Doc ID 1757657.1参照)

format句の書式レベルと制御

パラメータ	alias	bytes	cost	note	predicate	parallel	partition	projection	outlines	peeked_binds	remote	rows
Column/Section	S	C	C	S	S	C	C	S	S	S	S	C
BASIC
null		✔	✔	✔	✔							✔
TYPICAL		✔	✔	✔	✔	✔	✔				✔	✔
SERIAL		✔	✔	✔	✔		✔					✔
ALL	✔	✔	✔	✔	✔	✔	✔	✔	✔		✔	✔
ADVANCED	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔	✔

現時点で、DBMS_XPLAN.DISPLAY_CURSORから最大の表示を得るには、formatパラメータに 'ADVANCED ALLSTATS LAST ADAPTIVE'を指定する必要があります。

表示内容

表示される実行計画の各ステップには、次のような項目が併記されます。
※SQLの実行内容とformat句で指定したパラメータに応じて下記項目から必要な情報が表示されます。
なお、この情報は、V$SQL_PLAN_STATISTICS_ALLから取得しています。

▽実行計画を含むテーブルの列

Column	Description
Basics(Always Available)
id	実行計画内の各操作（行）の識別子。番号の前にアスタリスクが付いている場合は、その行の述部情報が後で使用できることを意味します。
Operation	実行される操作。これは、行ソース操作とも呼ばれます。
Name	操作が実行されるオブジェクト。
Query Optimizer Estimations
Rows and E-Rows	操作によって戻された推定行数。
Bytes and E-Bytes	操作によって戻されたデータの見積もり量。
TempSpc and E-Temp	操作に必要な一時スペースの推定量（バイト単位）。
Cost (%CPU)	操作の推定コスト。 CPUコストの割合はカッコで示しています。この値は実行計画によって累積されます。つまり、親オペレーションのコストには、子オペレーションのコストが含まれています。
Time and E-Time	操作を実行するのに必要な推定時間（HH：MM：SS）。
Partitioning
Pstart	アクセスされる最初のパーティションの番号。その番号が解析時にわからない場合は、KEYまたはINVALIDのいずれかです。 KEYは、実行フェーズ中に最初のパーティションが決定されたときに使用されます。
Pstop	アクセスされる最後のパーティションの番号。その番号が解析時にわからない場合は、KEYまたはINVALIDのいずれかです。 KEYは、実行フェーズ中に最後のパーティションが決定されたときに使用されます。
Parallel and Distributed Processing
Inst	分散処理の場合、操作で使用されるデータベース・リンクの名前。
TQ	並列処理の場合、スレーブプロセス間の通信に使用されるテーブルキュー。
IN-OUT	並列または分散操作の関係。
PQ Distrib	並列処理の場合、プロデューサがデータをコンシューマに送信するために使用されるディストリビューション。
Runtime Statistics*
Starts	特定の操作が実行された回数。特別な場合には、この統計情報は、特定のメモリ構造にアクセスした回数を示します
A-Rows	操作によって戻される実際の行数。
A-Time	操作の実行に費やされた実際の時間（HH：MM：SS.FF）。
I/O Statistics*
Buffers	実行中に実行された論理読み取りの数。
Reads	実行中に実行された物理読み込みの数。
Writes	実行中に実行された物理書き込みの数。
Memory Utilization Statistics
Omem	最適な実行に必要なメモリーの推定量（バイト単位）。
1Mem	1パス実行に必要なメモリーの推定量（バイト単位）。
O/1/M(*2)	実行が最適/ワンパス/マルチパスモードで実行された回数。
Used-Mem(*1)	最後の実行中に操作で使用されたメモリー量（バイト単位）。
Used-Tmp(*1)	最後の実行中に操作で使用された一時スペースの量（キロバイト単位）。他のメモリー使用率列と一致するように、この値に1,024を掛けなければなりません（たとえば、32Kは32MBを意味します）。
Max-Tmp(*2)	操作で使用される一時スペースの最大量（キロバイト単位）。この値は、他のメモリ使用率列と一致するように、1,024を掛けなければなりません（たとえば、32Kは32MBを意味します）。

*実行統計が有効になっている場合に出力される項目

赤字：Estimate 青字：Actual(実行統計)

(*1)format句にLAST指定をした場合に表示される項目
(*2)format句にLAST指定をしなかった場合に表示される項目

(補足)
Optimal:そのステップ(例えばSORT)のすべての作業をメモリ内で実行すること
1-pass:中間データを一段階ディスクに退避してマージ作業を行うこと
Multi-pass:中間データを二段階以上ディスクに退避してマージ作業を行うこと

▽セクションの表示内容(実行計画以外の部分)

1.
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T2@SEL$1
4 - SEL$1 / T2@SEL$1
5 - SEL$1 / T1@SEL$1

このセクションでは、クエリブロック名とオブジェクトエイリアスを示します。
実行計画の各操作では、関連するクエリブロックと、オプションで実行されるオブジェクトが表示されます。
この情報は、SQL文が同じ表を複数回参照する場合には不可欠です。

2.
Outline Data
-------------
/*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
     DB_VERSION('11.2.0.4')
     ALL_ROWS
     OUTLINE_LEAF(@"SEL$1")
     INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
     FULL(@"SEL$1" "T1"@"SEL$1")
     LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
     USE_HASH(@"SEL$1" "T1"@"SEL$1")
     END_OUTLINE_DATA
*/

その特定の実行計画を再現するのに十分な、アウトラインと呼ばれる一連のヒントを示します。
アウトラインには必ずしもすべての必要なヒントが含まれているとは限りません。

3.
Peeked Binds (identified by position):
--------------------------------------
   1 - :T1_ID (NUMBER): 6
   2 - :T2_ID_MIN (NUMBER): 6
   3 - :T2_ID_MAX (NUMBER): 19

問合せオプティマイザがバインド変数を利用する場合にのみ表示されます。
上記の通り、各バインド変数のデータ型と値が表示されます。

4.
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:T2_ID_MIN<=:T2_ID_MAX)
   2 - access("T1"."N"="T2"."N")
   4 - access("T2"."ID">=:T2_ID_MIN AND "T2"."ID"<=:T2_ID_MAX)
   5 - filter("T1"."ID">:T1_ID)

どの述部が適用されるかを示します。
それぞれについて、（行）と方法（アクセス、フィルタまたはストレージ）が適用される場所が示されます。
アクセス述語は、効率的なアクセス構造を利用して行を特定するために使用されますが、フィルタ述語は行をすでに格納している構造体から抽出されています。

5.
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
   2 - (#keys=1) "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22],"T2"."PAD"[VARCHAR2,1000]
   3 - "T2"."ID"[NUMBER,22], "T2"."N"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
   4 - "T2".ROWID[ROWID,10], "T2"."ID"[NUMBER,22]
   5 - "T1"."N"[NUMBER,22]

各操作が実行されるときにどの列が出力として返されるかを示します。

6.
Note
-----
- dynamic sampling used for this statement (level=2)

最後に、最適化フェーズ、環境、またはSQLステートメント自体に関する注釈と警告を提供するセクションがあります。
上記例では、問合せオプティマイザがオブジェクト統計を収集するために動的サンプリングを使用したことが通知されます。

実行例

SQL> set linesize 200
SQL> col sql_id for a15
SQL> select sql_id,sql_text from v$sqltext where sql_text like 'select * from emp%';

SQL_ID SQL_TEXT
--------------- ----------------------------------------------------------------
8wqqr7cg11y7t select * from emp e, dept d where e.deptno = d.deptno

SQL> set line 2000
SQL> set pages 2000
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

Session altered.

SQL> select * from table(dbms_xplan.display_cursor('&SQLID',null,'advanced'));
Enter value for sqlid: 8wqqr7cg11y7t
old 1: select * from table(dbms_xplan.display_cursor('&SQLID',null,'advanced'))
new 1: select * from table(dbms_xplan.display_cursor('8wqqr7cg11y7t',null,'advanced'))

SQL_ID 8wqqr7cg11y7t, child number 1
-------------------------------------
select * from emp e, dept d where e.deptno = d.deptno

Plan hash value: 615168685

---------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time         |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |      |      | |     6 (100)|          |
|* 1 | HASH JOIN        |      |   14 | 1638 | 6 (0)|     00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT |    4 |   120 |     3   (0)|     00:00:01 |
| 3 | TABLE ACCESS FULL| EMP |   14 | 1218 |     3   (0)|     00:00:01 |
---------------------------------------------------------------------------

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

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

Outline Data
-------------

/*+
       BEGIN_OUTLINE_DATA
       IGNORE_OPTIM_EMBEDDED_HINTS
       OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
       DB_VERSION('12.2.0.1')
       ALL_ROWS
       OUTLINE_LEAF(@"SEL$1")
       FULL(@"SEL$1" "D"@"SEL$1")
       FULL(@"SEL$1" "E"@"SEL$1")
       LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
       USE_HASH(@"SEL$1" "E"@"SEL$1")
       END_OUTLINE_DATA
   */

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

1 - access("E"."DEPTNO"="D"."DEPTNO")

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

1 - (#keys=1) "D"."DEPTNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],

       "D"."LOC"[VARCHAR2,13], "D"."DNAME"[VARCHAR2,14],
       "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22]
   2 - (rowset=256) "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14],
       "D"."LOC"[VARCHAR2,13]
   3 - (rowset=256) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],
       "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],
       "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

124 rows selected.

※12cR2からtable句の省略可

(これまで) select * from table(dbms_xplan.display_cursor('&SQLID',null,'allstats last'));

(12.2以降) select * from (dbms_xplan.display_cursor('&SQLID',null,'allstats last'));