概要
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'));
個人的希望
個人的には以下のサイトのQ&Aにもある通り、「-SQLTEXT」「-QUERY」のようなオプションを設けてPLANから確認が始められる(SQLテキスト部分を除外できる)ようにして欲しい。
https://sqlmaria.com/2017/08/08/using-dbms_xplan-display_cursor-to-examine-execution-plans/
参考資料
Expert Oracle SQL
Troubleshooting Oracle Performance, Second Edition
https://docs.oracle.com/cd/E82638_01/TGSQL/reading-execution-plans.htm#GUID-725CADF8-3803-416F-96EB-BF942F742663
Oracle® Database SQLチューニング・ガイド 12cリリース2 (12.2)
https://sqlmaria.com/2017/08/08/using-dbms_xplan-display_cursor-to-examine-execution-plans/
