事前設定が不要なSQL実行計画の取得方法

01.DBMS_XPLAN.DISPLAY_CURSORプロシージャ

V$SQL_PLANを直接参照すべき状況は一般的に少ないため、DBMS_XPLAN.DISPLAY_CURSORから先に説明します。
DBMS_XPLAN.DISPLAY_CURSORは共有プールにキャッシュされた共有カーソルから実行計画を取得・表示するプロシージャであり、10.1以降で導入されました。事前設定不要で実行計画を取得でき、”Predicate Information”などのSQLトレースで表示されない情報を得られるという利点があります。

SQL_ID=’6377g9s3af8u6’のSQLの実行計画をDBMS_XPLAN.DISPLAY_CURSORで取得した例

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 6377g9s3af8u6, child number 0
-------------------------------------
select ch, cname, pa.pa, pname from ch, pa where ch.pa = pa.pa and
pa.pa = 1

Plan hash value: 3732797803

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS | | 1 | 1113 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PA | 1 | 85 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | IDX_PA | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CH | 1 | 1028 | 0 (0)| |
|* 5 | INDEX RANGE SCAN | IDX_CHPA | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------

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

3 - access("PA"."PA"=1)
5 - access("CH"."PA"=1)


24行が選択されました。

02.V$SQL_PLAN

DBMS_XPLAN.DISPLAY_CURSORプロシージャは非常に有用であり、可能であればこのプロシージャを使用すべきですが、Oracle Database 9.2, 9.0.1では、DBMS_XPLAN.DISPLAY_CURSORが導入されていません。このため、Oracle Database 9.2, 9.0.1では、V$SQL_PLANを問い合わせるカスタムSQLを実行して実行計画を確認します。SQL*Plusの変数 hash、addrには実行計画を確認したいSQLのハッシュ値とADDRESS値を指定してください。

define hash=SQLハッシュ値
define addr=SQL ADDRESS値
column id format 999 newline
column operation format a20
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc
select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan
where hash_value = &hash
and address = '&addr'
start with id = 0
connect by
(prior id = parent_id
and prior hash_value = hash_value
and prior child_number = child_number
)
order siblings by id, position;