DBMS_XPLAN.DISPLAY_CURSORの使い方とちょっとした落とし穴

先日のエントリ
[SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは（ほとんど）使わない](http://www.csus4.net/d/2011/02/24/whynotuse_explainplan_sqltrace/)
でお勧めしたDBMS_XPLAN.DISPLAY_CURSORについて、基本的な使い方を説明します。

### 基本的なDBMS_XPLAN.DISPLAY_CURSORの使い方

詳細はマニュアル(Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス)
のDBMS_XPLANパッケージの箇所をご確認いただきたいのですが、

* 10.2 : [DISPLAY_CURSORファンクション](http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-02/d_xplan.htm#i998364)
* 11.1 : [DISPLAY_CURSORファンクション](http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/appdev.111/E05686-02/d_xplan.htm#i998364)
* 11.2 : [DISPLAY_CURSORファンクション](http://download.oracle.com/docs/cd/E16338_01/appdev.112/b56262/d_xplan.htm#i998364)

DISPLAY_CURSORファンクションには以下の引数を指定します。

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

sql_id: SQLの識別子
child_number : 子カーソル番号
format: 出力フォーマット(後ほど簡単に説明)

このファンクションはテーブルファンクションであるため、TABLE演算子で変換すると
通常の表のように扱えます。典型的には引数sql_idに調査対象のSQLのsql_idを指定して、
以下のように使用します。

— 1. 調査対象のSQLを実行
SELECT … ;
— 2. 調査対象のSQLに対応するsql_idを確認
SELECT sql_id, sql_text FROM V$SQL WHERE sql_text LIKE ‘<調査対象のSQLにマッチするパターン>‘;
— 3. 調べたsql_idを指定して実行計画を取得
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘<上記のSQLで得たsql_id>‘));

また、sql_idの指定を省略すると、直前で実行したSQLの実行計画が確認できます。
EXPLAN PLANと同じような感覚でSQLの実行計画を確認できるため便利です。

set serveroutput off
<調査対象ののSQL>
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

select * from dual; を実行してから、実行計画を確認した例が以下の実行結果です。

SQL> set serveroutput on
SQL> select * from dual;

D
–
X

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
—————————————————————————–
SQL_ID a5ks9fhw2v9s1, child number 0
————————————-
select * from dual

Plan hash value: 272002086

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
————————————————————————–

13行が選択されました。

この方法でも、sql_idを確認することができます。

### DBMS_XPLAN.DISPLAY_CURSOR使用上の注意点いくつか

上記のsql_idの指定を省略してDBMS_XPLAN.DISPLAY_CURSORで直前のSQLの実行計画を確認する場合、
調査対象ののSQLを実行する前に、set serveroutput offを実行しておく必要が
あることに注意してください。
set serveroutput on となっていると、内部的に実行されているDBMS_OUTPUTパッケージ
のプロシージャの実行計画を取得しようとしてしまい、意図したSQLの実行計画が得られません。
この動作は若干不親切な気がしますが・・・、ま、しょうがないので、
set serveroutput offを実行するのを忘れないようにしましょう。

また、child_numberの指定を省略すると、sql_idに対応するSQLのすべての子カーソル
の実行計画が得られる。とマニュアルに記載がありますが・・・、実はBugのため、
1つの子カーソルの実行計画しか表示されません。
このため、すべての子カーソルの実行計画を確認したい場合は、以下のSQLを実行しましょう。

SELECT t.*
FROM v$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number)) t
WHERE s.sql_id = ‘＜sql_id＞’ ;

### formatの指定

format引数にはさまざまなパラメータが指定可能ですが、私は主に以下の2つのパターンを中心に使用します。

* ‘TYPICAL’ : format引数に何もしていない場合のデフォルトです。
実行計画のツリー構造を示すPLAN TABLEと、
フィルタ条件やアクセス方法を示すPredicate Informationが表示されます。
PLAN TABLEには主にCBOの見積統計が表示されます。
実行計画のみを取得する場合に使用します。
* ‘ALL ALLSTATS LAST’ : PLAN TABLEとPredicate Informationに加えて、いくつかの補足情報が表示されます。(ALLキーワード)
また、PLAN TABLEの列にはCBOの見積統計に加えて、実測統計が表示されます。(ALLSTATSキーワード)
統計は直近のSQL実行に関する情報となります。(LASTキーワード)
ただし、SQLの実行前にstatistics_levelパラメータをALLに設定するか、
SQLに /*+ gather_plan_statistics */ ヒントを指定する必要があります。
実行計画の実行時の統計を確認する必要がある場合に使用します。
主に、CBOが作成した実行計画の妥当性をチェックする場合に使用します。

format引数に’ALL ALLSTATS LAST’を指定して、select * from dual の実行計画を
取得した例を以下に示します。

SQL> set serveroutput off
SQL> alter session set statistics_level=all;

セッションが変更されました。

SQL> select * from dual;

D
–
X

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>’ALL ALLSTATS LAST’ ));

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID a5ks9fhw2v9s1, child number 1
————————————-
select * from dual

Plan hash value: 272002086

——————————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
——————————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 1 | 2 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
——————————————————————————————————————–

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

1 – SEL$1 / DUAL@SEL$1

Column Projection Information (identified by operation id):
———————————————————–

1 – “DUAL”.”DUMMY”[VARCHAR2,1]

23行が選択されました。

この’ALL ALLSTATS LAST’ のformat指定は、SQLチューニングやパフォーマンス問題の
トラブルシューティング極めて便利(!)なのですが、
便利さの説明については、次回更新entryで説明することにしますね。