SQLの実行計画を見るためによく使うDBMS_XPLAN.display。
ここに'OUTLINE'または'ADVANCED'オプションを渡すと、実際に付与されるヒント句が表示されるようになる。
これは'ALL'オプションでは表示されず、ドキュメントにも記載がないが、以下の本に詳細が書かれている。
他にもいろいろな情報が出せるようだが、とくにこのヒント一覧が便利なので紹介する。
- 作者: Karen Morton,Kerry Osborne,Robyn Sands,Riyaj Shamsudeen,Jared Still
- 出版社/メーカー: Apress
- 発売日: 2010/12/15
- メディア: Kindle版
- この商品を含むブログを見る
- 第二版も出てるっぽい
- 作者: Karen Morton,Kerry Osborne,Robyn Sands,Riyaj Shamsudeen,Jared Still
- 出版社/メーカー: Apress
- 発売日: 2013/10/30
- メディア: Kindle版
- この商品を含むブログを見る
以下、実際に実行してみたときのSQLと出力された実行計画を示す。
explain plan for select e.FIRST_NAME ,e.LAST_NAME ,d.DEPARTMENT_NAME ,j.JOB_TITLE from employees e, departments d, jobs j where e.DEPARTMENT_ID = d.DEPARTMENT_ID and e.JOB_ID = j.JOB_ID; select * from table(dbms_xplan.display(format=>'ADVANCED'));
このときの出力は、
Plan hash value: 2870237099
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 7420 | 10 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 7420 | 10 (20)| 00:00:01 |
| 2 | MERGE JOIN | | 107 | 5778 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 107 | 2889 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / J@SEL$1
4 - SEL$1 / J@SEL$1
6 - SEL$1 / E@SEL$1
7 - SEL$1 / D@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "D"@"SEL$1")
USE_MERGE(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "J"@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "J"@"SEL$1" ("JOBS"."JOB_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - access("E"."JOB_ID"="J"."JOB_ID")
filter("E"."JOB_ID"="J"."JOB_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "J"."JOB_TITLE"[VARCHAR2,35], "E"."FIRST_NAME"[VARCHAR2,20],
"E"."LAST_NAME"[VARCHAR2,25], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
2 - (#keys=0) "J"."JOB_TITLE"[VARCHAR2,35], "E"."FIRST_NAME"[VARCHAR2,20],
"E"."LAST_NAME"[VARCHAR2,25], "E"."DEPARTMENT_ID"[NUMBER,22]
3 - "J"."JOB_ID"[VARCHAR2,10], "J"."JOB_TITLE"[VARCHAR2,35]
4 - "J".ROWID[ROWID,10], "J"."JOB_ID"[VARCHAR2,10]
5 - (#keys=1) "E"."JOB_ID"[VARCHAR2,10], "E"."FIRST_NAME"[VARCHAR2,20],
"E"."LAST_NAME"[VARCHAR2,25], "E"."DEPARTMENT_ID"[NUMBER,22]
6 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
"E"."JOB_ID"[VARCHAR2,10], "E"."DEPARTMENT_ID"[NUMBER,22]
7 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]このOutline Dataのところに実際に付与されるヒント句がそのまま表示されている。
SEL1というクエリブロックが内部で使われているようだが、それはQuery Block Name / Object Aliasのところに一覧がある。
format=>'OUTLINE'だとOutline Dataだけが付与されるよう。
ヒント句が分かれば、例えば
LEADING(@"SEL$1" "J"@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
のところからテーブルの結合順がjobs, employees, departmentsであることが一目で分かる。
よくあるケースとして、leadingヒントを付与してテーブルの結合順を変えてみる。
explain plan for select /*+ leading(d e j) */ e.FIRST_NAME ,e.LAST_NAME ,d.DEPARTMENT_NAME ,j.JOB_TITLE from employees e, departments d, jobs j where e.DEPARTMENT_ID = d.DEPARTMENT_ID and e.JOB_ID = j.JOB_ID; select * from table(dbms_xplan.display(format=>'OUTLINE'));
実行計画は以下。
Plan hash value: 432207716
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 7420 | 10 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 106 | 7420 | 10 (20)| 00:00:01 |
| 2 | MERGE JOIN | | 106 | 4558 | 6 (17)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 107 | 2889 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | JOBS | 19 | 513 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "J"@"SEL$1")
USE_MERGE(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1" "J"@"SEL$1")
FULL(@"SEL$1" "J"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."JOB_ID"="J"."JOB_ID")
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")以下のように変わったことから、結合順がdepartments, employees, jobs と逆になったことが一目で分かる。
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1" "J"@"SEL$1")
付与したヒント句が本当に反映されたかどうかや、アクセスパス・結合順を見るには、この方式で見るのがおすすめ。
