GAGA LIFE.

インフラエンジニアブログ

スポンサーリンク

Oracleのヒント句の有効性を確認する Hint Report(DBMS_XPLAN)

概要

Oracle 18cまではヒント句の使用有無や記述の正誤を簡単に判断する方法はありませんでした。
これまでは、イベントの10053(CBOトレース)を取得し、出力されたトレース内の「Dumping Hints」というセクションで確認していました。
ただし、それも実際にヒントが有効になったかどうかの判断としては使えないケースがありました。
※従来バージョンでは、ヒントが有効であるため実行計画が決定されているのか、ヒントの指定は誤っているがCBOによりその実行計画になったのかを簡単に判別できなかった。

リリース19.3以降のDBMS_XPLANのDISPLAYやDISPLAY_CURSORでは、ヒントが有効にならなかった場合に、
「Hint Report」というセクションが表示され、この出力内容からヒントの利用有無が確認できるようになりました。

ヒント使用状況レポートは19cの新機能であり、実行計画の出力からオプティマイザーによって使用され、オプティマイザーによって使用されないすべてのヒントを判別するのに役立ちます。

ヒント句が無視される理由

  • 構文エラー:記述ミスや無効な引数を指定した場合
  • 未解決ヒント:構文エラー以外の理由。索引指定誤りなど
  • 競合するヒント:競合するヒントの同時指定。FULLヒントとINDEXヒントを同時に指定するなど
  • 問合せ変換の影響を受けるヒント:CBOによって内部で問合せ変換が発生した場合、ヒントが無効になる時がある

レポート出力可能なファンクション

  • DISPLAY(PLAN TABLE)
  • DISPLAY_CURSOR(カーソル・キャッシュ)
  • DISPLAY_WORKLOAD_REPOSITORY(AWR)
  • DISPLAY_SQL_PLAN_BASELINE(SQL計画ベースライン)
  • DISPLAY_SQLSET(SQLチューニング・セット)

ヒントレポートの出力制御(FORMATパラメータ)

Value Description
HINT_REPORT ヒントレポートを出力
HINT_REPORT_USED 使われたヒントレポートのみを出力
HINT_REPORT_UNUSED 使われなかったヒントのみ出力
TYPICAL(デフォルト) 使われなかったヒントのみ出力
ALL 使用/未使用の両方を出力

パラメータ指定例

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'TYPICAL'));
SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'BASIC +HINT_REPORT'));

レポート出力内容

Hint Reportでは、以下の出力がなされます。
- SQL文に対するヒント数(合計ヒント数と未使用ヒント数)を出力
- その後に、それぞれのヒントに対応する実行計画の行番号/問合せブロック名/オブジェクト別名を出力
- 同一オブジェクトでは、未使用のヒントを最初に表示し、その後に使用されたヒントを表示
- 未使用のヒント・テキストが出力されたときに、理由として以下のいずれかの注釈が表示される
(非使用ヒント数に、それぞれの注釈の数も出力されます)
- 正しくないヒントや最終計画に存在しない問合せブロックに対するヒントは、行番号が0

Value Description
E 構文エラー
N 未解決ヒント
U 未使用

実行例

SQL> select
    /*+ 
    INDEX(e emp_idx) 
    PUSH_PRED(dept) 
    FULL(@sel$2 l) 
    PARALLEL(2) 
    OPT_PARAM('_simple_view_merging','false')
    */
    e.first_name,
    e.last_name,
    dept_locs_v.street_address,
    dept_locs_v.postal_code
from
    employees e,
    (
        select
            /*+ FULL(l) FUL(d) */
            d.department_id,
            d.department_name,
            l.street_address,
            l.postal_code
        from
            depa  2  rtments d,
            locations l
        where
            d.location_id = l.location_id
    ) dept_locs_v
where
    dept_locs_v.department_id = e.department_id
and e.last_name = 'XXXXX'
;
  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31  
レコードが選択されませんでした。

SQL> select * from TABLE(dbms_xplan.display_cursor(format=>'ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3ghnkvfjbt5kz, child number 2
-------------------------------------
select     /*+     INDEX(e emp_idx)     PUSH_PRED(dept)     FULL(@sel$2
l)     PARALLEL(2)     OPT_PARAM('_simple_view_merging','false')     */
    e.first_name,     e.last_name,     dept_locs_v.street_address,
dept_locs_v.postal_code from     employees e,     (         select
       /*+ FULL(l) FUL(d) */             d.department_id,
d.department_name,             l.street_address,
l.postal_code         from             departments d,
locations l         where             d.location_id = l.location_id
) dept_locs_v where     dept_locs_v.department_id = e.department_id and
e.last_name = 'XXXXX'

Plan hash value: 1795193073

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       |       |     6 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000    |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |             |     1 |    61 |     6   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     JOIN FILTER CREATE  | :BF0000     |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  5 |      TABLE ACCESS FULL  | EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     VIEW                |             |    27 |  1161 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      HASH JOIN          |             |    27 |  1026 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       PX BLOCK ITERATOR |             |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  9 |        TABLE ACCESS FULL| LOCATIONS   |    23 |   713 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       JOIN FILTER USE   | :BF0000     |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 11 |        TABLE ACCESS FULL| DEPARTMENTS |    27 |   189 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   5 - SEL$1 / E@SEL$1
   6 - SEL$2 / DEPT_LOCS_V@SEL$1
   7 - SEL$2
   9 - SEL$2 / L@SEL$2
  11 - SEL$2 / D@SEL$2

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

   3 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - filter("E"."LAST_NAME"='XXXXX')
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
   9 - access(:Z>=:Z AND :Z<=:Z)
  11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"D"."DEPARTMENT_ID"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   2 - (#keys=0) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   3 - (#keys=1; rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40]
   4 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   5 - (rowset=256) "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
       "E"."DEPARTMENT_ID"[NUMBER,22]
   6 - (rowset=256) "DEPT_LOCS_V"."DEPARTMENT_ID"[NUMBER,22], "DEPT_LOCS_V"."STREET_ADDRESS"[VARCHAR2,40],
       "DEPT_LOCS_V"."POSTAL_CODE"[VARCHAR2,12]
   7 - (#keys=1; rowset=256) "L"."POSTAL_CODE"[VARCHAR2,12], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "D"."DEPARTMENT_ID"[NUMBER,22]
   8 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
   9 - (rowset=256) "L"."LOCATION_ID"[NUMBER,22], "L"."STREET_ADDRESS"[VARCHAR2,40],
       "L"."POSTAL_CODE"[VARCHAR2,12]
  10 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]
  11 - (rowset=256) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."LOCATION_ID"[NUMBER,22]

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 7 (U - Unused (2), N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------------------------

   0 -  STATEMENT
           -  OPT_PARAM('_simple_view_merging','false')
           -  PARALLEL(2)

   1 -  SEL$1
         N -  PUSH_PRED(dept)

   5 -  SEL$1 / E@SEL$1
         U -  INDEX(e emp_idx) / index specified in the hint doesn't exist

   7 -  SEL$2
         E -  FUL

   9 -  SEL$2 / L@SEL$2
         U -  FULL(l) / hint overridden by another in parent query block
           -  FULL(@sel$2 l)

Note
-----
   - Degree of Parallelism is 2 because of hint

上記のHint Reportでは、オプティマイザーは以下のように判断しています。
- PARALLEL(2)、OPT_PARAM('_ simple_view_merging'、'false')およびFULL(@sel$2 l)ヒントのみを考慮
- U:指定されたインデックスが存在しないため、ヒントINDEX(e emp_idx)は使用しない
- U:同様のヒントが親クエリブロックで指定され、サブクエリブロックのクエリが上書きされるため、ヒントFULL(l)は使用しない
- E:FULヒントに構文エラーがあるため、考慮されない(※FULではなくFULL)
- N:ヒントPUSH_PRED(dept)は未解決(※「dept」という名前のビューはない)

参考資料

津島博士のパフォーマンス講座 第72回 SQLパッチとヒント使用状況レポートについて
https://blogs.oracle.com/otnjp/tsushima-hakushi-72

スポンサーリンク