はじめに
Oracleで実行計画を取得する方法は、以下のように多く存在します。（他にもありますが）
- DBMS_XPLAN.DISPLAY_CURSOR
- SQLトレース
- EXPLAIN PLAN
- AUTOTRACE(SQL*Plus)
が、DBMS_XPLAN.DISPLAY_CURSORが使えれば十分なケースが多いと思います。
DBMS_XPLAN.DISPLAY_CURSORは、v$sql_planから実行計画を取得することができるファンクションです。ただし、共有プールから実行計画が削除されている場合は取得することができません。
以降、Oracle 12c R2にてDBMS_XPLAN.DISPLAY_CURSORを使用してみます。
DBMS_XPLAN.DISPLAY_CURSORの実行
直前に実行したSQLの実行計画を取得
直前に実行したSQLの実行計画を取得する方法です。
SQL> set serveroutput off
→ 正しく取得できないので、「set serveroutput off」は必ず実行。
SQL> set linesize 1000
SQL> set pagesize 0
→ 折り返しやページ変更が発生しないように設定。
SQL> select emp.first_name, emp.last_name, j.job_title from employees emp, jobs j where emp.job_id = j.job_id and emp.salary = 8300.00;
→ 実行計画を取得するSQLを実行。
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());
→ 実行計画取得。DBMS_XPLAN.DISPLAYを引数なしで呼ぶ。引数なしだと直前に実行したSQLの実行計画を取得できる。
取得した実行計画は以下のとおり。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1536616604
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 4 (0)|
00:00:01 |
| 1 | NESTED LOOPS | | 1 | 55 | 4 (0)|
00:00:01 |
| 2 | NESTED LOOPS | | 1 | 55 | 4 (0)|
00:00:01 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 28 | 3 (0)|
00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)|
00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 27 | 1 (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."SALARY"=8300.00)
4 - access("EMP"."JOB_ID"="J"."JOB_ID")
Note
-----
- this is an adaptive plan
22行が選択されました。
SQL IDを指定して実行計画を取得
SQL IDを指定して実行計画を取得します。通常はこちらのやり方を利用することが多いです。
select sql_id, child_number, sql_text from v$sql where sql_text like 'select emp.first_name%';
→ v$sqlから実行計画を取得したSQLを探して、SQL_IDを取得します。
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定'));
取得した実行計画は以下のとおり。
PLAN_TABLE_OUTPUT
SQL_ID cp5qum1t9gdun, child number 0
-------------------------------------
select emp.first_name, emp.last_name, j.job_title, j.min_salary from
employees emp, jobs j where emp.job_id = j.job_id and emp.salary =
8300.00
Plan hash value: 1536616604
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | 1 | 59 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 59 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 28 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 31 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."SALARY"=8300)
4 - access("EMP"."JOB_ID"="J"."JOB_ID")
Note
-----
- this is an adaptive plan
下のように表示されている行ですが、「child number 0」と表示されています。
SQL_ID cp5qum1t9gdun, child number 0
今回は同じSQL（SQL_ID）に対して作成された実行計画が一つだったため、「child number 0」になっています。
実行計画が2つあった場合は、「child number 1」と連番を持ちます。
実行計画が異なるので子カーソルが複数あると言ったりするらしいです。
実行計画が複数ある場合に実行計画を取得するとどうなるかというと、
取得に失敗します。
どうすれば取得できるかというと、「child number 1」であれば、DBMS_XPLAN.DISPLAY_CURSORプロシージャの引数に"1"を指定するだけです。
引数を指定しないと全部表示してくれれば良いのですが、そうはなっていないようです。
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', 1));
child numberの取得方法は色々ありますが、v$sqlでSQL_IDを調べたのであれば、CHILD_NUMBERカラムの値を見るのが一番簡単です。
format指定によって取得できる情報の違い
DBMS_XPLAN.DISPLAY_CURSORの引数formatにより、出力する情報を設定することができます。
formatに指定できる情報はたくさんあるのですが、よく利用する指定方法だけ以降に記載します。
まずは、"ALL"指定。これで実行計画＋実行統計を取得します。
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', format=>'ALL'));
format指定なしと比較して、以下の情報が追加で出力されます。
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / EMP@SEL$1
4 - SEL$1 / J@SEL$1
5 - SEL$1 / J@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"EMP"."JOB_ID"[VARCHAR2,10], "J"."JOB_TITLE"[VARCHAR2,35],
"J"."MIN_SALARY"[NUMBER,22]
2 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"EMP"."JOB_ID"[VARCHAR2,10], "J".ROWID[ROWID,10]
3 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"EMP"."JOB_ID"[VARCHAR2,10]
4 - "J".ROWID[ROWID,10]
5 - "J"."JOB_TITLE"[VARCHAR2,35], "J"."MIN_SALARY"[NUMBER,22]
次に、"ALL ALLSTATS LAST"を指定した場合です。
statistics_levelがALLでないと取得できません。そのため、sessionレベルでstatistics_levelを以下のように設定します。
statistics_levelをシステム全体で変更することもできますが、オーバーヘッドが大きいので、通常指定することはありません。
alter session set statistics_level=all;
show parameter statistics_level;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', format=>'ALL ALLSTATS LAST'));
※LASTを指定しない場合、平均値や合計値（Buffers）が表示されてしまう。LASTを指定すると直近の実行の統計が表示されます。ALLSTATSとLASTを一緒に指定することが多いです。
"ALL"を指定した場合と比較して、以下の情報が追加で出力されます。
E-Rows、E-Bytes、E-Time、A-Rows、A-Time、Buffers が追加されています。
"E-"はOracleによって推定されたデータで、"A-"は実際の実行結果になります。BuffersはバッファからGetしたデータブロック数です。
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 1 | 59 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 9 |
| 2 | NESTED LOOPS | | 1 | 1 | 59 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 1 | 28 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 4 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 1 | 31 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------------------------------
終わったら、statistics_levelは元に戻しておきましょう。
alter session set statistics_level=typical;
statistics_levelを使用せずに、GATHER_PLAN_STATISTICSヒントをつけても同様の結果を表示できます。
select /*+ GATHER_PLAN_STATISTICS */ * from hr.DEPARTMENTS
d, hr.EMPLOYEES e where d.MANAGER_ID = e.EMPLOYEE_ID;
次に、「format=>'ADVANCED'」を試してみます。
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', format=>'ADVANCED'));
format=>'ALL'よりもOutline Dataが増える。
付与したヒント句が本当に反映されたかどうかを確認できます。
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" "EMP"@"SEL$1")
INDEX(@"SEL$1" "J"@"SEL$1" ("JOBS"."JOB_ID"))
LEADING(@"SEL$1" "EMP"@"SEL$1" "J"@"SEL$1")
USE_NL(@"SEL$1" "J"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "J"@"SEL$1")
END_OUTLINE_DATA
*/
開発環境（性能試験以外）なら、statistics_levelをALLにして、formatを"ALLSTATS LAST ADVANCED"しておけば良いかと思います。
ヒント句を使わないなら、"ALL ALLSTATS LAST"
おまけ：statistics_levelをALLにできない場合は、gather_plan_statisticsヒントをつけておけば同じ情報を得られます。
select /*+ gather_plan_statistics */ emp.first_name, emp.last_name, j.job_title, j.min_salary from employees emp, jobs j where emp.job_id = j.job_id and emp.salary = 8300.00;
バインド変数の値を表示する
フォーマットに"PEEKED_BINDS"を指定するとハードパース時のバインド変数の値を表示することができます。
set serveroutput off
set linesize 1000
set pagesize 0
variable salary number;
execute :salary := 8300;
select emp.first_name, emp.last_name, j.job_title from employees emp, jobs j where emp.job_id = j.job_id and emp.salary = :salary;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST PEEKED_BINDS'));
実行すると以下のように実行計画が表示されます（一部省略）。
「Peeked Binds」の部分でバインド変数の値を確認できます。
共有プールに残っていない場合は表示されないはずです。
また、"LAST"をつけて実行計画を取得しましたが、バインド変数の値は毎回保存されるわけではないため、前回の情報が表示されないことに注意が必要です。
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | 2 | 110 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 110 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 2 | 56 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 27 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / EMP@SEL$1
4 - SEL$1 / J@SEL$1
5 - SEL$1 / J@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :SALARY (NUMBER): 8300
v$sql_bind_captureビューでも同様にバインド変数の値を取得することができます。
select name, value_string from v$sql_bind_capture where sql_id='d8jx3zv3h0qn8';
適応計画(Adaptive Plan)の適用状況を表示する
formatに"ADAPTIVE"を指定することで、12cから追加された適応計画(Adaptive Plan)の適用状況を表示することができます。
最初の実行計画の"Note"部に"this is an adaptive plan"と表示されていました。
これは適応計画のサブプランが適用されたことを意味していますが、実際にどのように適用されたかは実行計画から読み取れません。formatに"ADAPTIVE"を指定することで、どのように適用されたかを確認することができるようになります。
Note
-----
- this is an adaptive plan
formatに"ADAPTIVE"を指定した実行例は以下のようになります。
SQL> select emp.first_name, emp.last_name, j.job_title from employees emp, jobs j where emp.job_id = j.job_id and emp.salary = 8300.00;
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL ADAPTIVE'));
SQL_ID 0afr8hr8f6gz7, child number 0
-------------------------------------
select emp.first_name, emp.last_name, j.job_title from employees emp,
jobs j where emp.job_id = j.job_id and emp.salary = 8300.00
Plan hash value: 1536616604
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|- * 1 | HASH JOIN | | 1 | 55 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| * 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 28 | 3 (0)| 00:00:01 |
| * 6 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| |
| 7 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 27 | 1 (0)| 00:00:01 |
|- 8 | TABLE ACCESS FULL | JOBS | 1 | 27 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1
5 - SEL$1 / EMP@SEL$1
6 - SEL$1 / J@SEL$1
7 - SEL$1 / J@SEL$1
8 - SEL$1 / J@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."JOB_ID"="J"."JOB_ID")
5 - filter("EMP"."SALARY"=8300)
6 - access("EMP"."JOB_ID"="J"."JOB_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"J"."JOB_TITLE"[VARCHAR2,35], "J"."JOB_TITLE"[VARCHAR2,35]
2 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"EMP"."JOB_ID"[VARCHAR2,10], "J"."JOB_TITLE"[VARCHAR2,35]
3 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"EMP"."JOB_ID"[VARCHAR2,10], "J".ROWID[ROWID,10]
4 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"EMP"."JOB_ID"[VARCHAR2,10]
5 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25],
"EMP"."JOB_ID"[VARCHAR2,10]
6 - "J".ROWID[ROWID,10]
7 - "J"."JOB_TITLE"[VARCHAR2,35]
8 - "J"."JOB_ID"[VARCHAR2,10], "J"."JOB_TITLE"[VARCHAR2,35]
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
今回は"this is an adaptive plan (rows marked '-' are inactive)"と表示されています。
これは実行計画の"-"が採用されなかった計画となります。
以下に実行計画部分だけを抜粋しましたが、左側に"-"となっているのが採用されなかった計画になります。
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|- * 1 | HASH JOIN | | 1 | 55 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| * 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 28 | 3 (0)| 00:00:01 |
| * 6 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| |
| 7 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 27 | 1 (0)| 00:00:01 |
|- 8 | TABLE ACCESS FULL | JOBS | 1 | 27 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
適応計画を使用したくない場合はOPTIMIZER_ADAPTIVE_PLANSパラメータで制御することができます。
ここでは、パラメータではなくヒント句を使用して適応計画を無効化してみます。
ヒント句の場合は以下のように" OPT_PARAM('_optimizer_adaptive_plans','false')"と指定します。
参考 12c Adaptive Optimization – Part 2 – Hints
SQL> select /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */ emp.first_name, emp.last_name, j.job_title from employees emp, jobs j where emp.job_id = j.job_id and emp.salary = 8300.00;
このときの実行計画（抜粋）は以下のようになります。Hint Reportにヒントが適用されていることが表示されています。結果的に同じ実行計画に今回はなりましたが。
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL ADAPTIVE'));
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 28 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 27 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('_optimizer_adaptive_plans','false')
- OPT_PARAM('_optimizer_adaptive_plans','false')
- OPT_PARAM('_optimizer_adaptive_plans','false')
ヒントレポート（HINT REPORT）
ヒントはコメントであるため記述に誤りがあってもエラーにはなりません。そのため、逆にヒントが正しく書けて適用されているかどうかも確認するのが難しいです。
Oracle19cはヒントレポートを出力できるようになり、ヒントが実行計画で使用されているかどうか、エラーならその理由を確認することができます。
ヒントレポートは実行計画にて出力することができます。
set serveroutput off
select /*+ LEADING (e d) USE_NL(d) */
d.*
, e.FIRST_NAME
, e.LAST_NAME
, e.EMAIL
from
hr.DEPARTMENTS d
inner join hr.EMPLOYEES e
on d.MANAGER_ID = e.EMPLOYEE_ID
order by d.DEPARTMENT_ID;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALL LAST'));
以下のようにHint Reportの項目が出力されます。
LEADINGとUSE_NLが使用されていることが確認できます。
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 151 (100)| |
| 1 | SORT ORDER BY | | 11 | 528 | 151 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 11 | 528 | 150 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
1 - SEL$58A6D7F6
- LEADING (e d)
4 - SEL$58A6D7F6 / D@SEL$1
- USE_NL(d)
DBMS_XPLAN.DISPLAY_CURSORのformatに"ALL", "HINT_REPORT", "HINT_REPORT_USED"を指定した場合に表示されます。
指定しなくても、使用されないヒントがある場合は自動で表示されます。
ヒントが使われないとき、構文にエラーがある場合は以下のような表示になります。
ヒントが使われないとき
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$58A6D7F6 / D@SEL$1
U - USE_NL(d)
構文やヒントの指定にエラーがある場合
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - SEL$2
E - USE_NL1
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------
1 - SEL$58A6D7F6
N - USE_NL(d1)