Oracle SQL実行計画でヒントの一覧を表示するためのDBMS_XPLAN.display(format=>'ADVANCED')オプションが便利

SQLの実行計画を見るためによく使うDBMS_XPLAN.display。
ここに'OUTLINE'または'ADVANCED'オプションを渡すと、実際に付与されるヒント句が表示されるようになる。
これは'ALL'オプションでは表示されず、ドキュメントにも記載がないが、以下の本に詳細が書かれている。
他にもいろいろな情報が出せるようだが、とくにこのヒント一覧が便利なので紹介する。

Pro Oracle SQL (Expert's Voice in Oracle)

作者: Karen Morton,Kerry Osborne,Robyn Sands,Riyaj Shamsudeen,Jared Still
出版社/メーカー: Apress
発売日: 2010/12/15
メディア: Kindle版
この商品を含むブログを見る

第二版も出てるっぽい

Pro Oracle SQL

作者: 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")

付与したヒント句が本当に反映されたかどうかや、アクセスパス・結合順を見るには、この方式で見るのがおすすめ。

カタカタブログ

SIerで働くITエンジニアがカタカタした記録を残す技術ブログ。Java, Oracle Database, Linuxが中心です。たまに数学やデータ分析なども。

Oracle SQL実行計画でヒントの一覧を表示するためのDBMS_XPLAN.display(format=>'ADVANCED')オプションが便利