LoginSignup
6
1

More than 3 years have passed since last update.

@plusultra(Noriyoshi Shinoda)

ヒントレポート機能を試す（Oracle Database 19c）

Last updated at Posted at 2019-07-16

ヒント

ヒントはSQL文に付与する特別な構文のコメントです。「/*+ ヒント */」または、「-- ヒント」の形式で、SELECT / UPDATE / DELETE / INSERT句の直後に記述します。下記の例では table1テーブルの検索にindex1インデックスを使うように指定しています。

SQL> SELECT /*+ INDEX(table1 index1) */ * FROM table1 WHERE column1=10000;
   COLUMN1 COLUMN2
---------- ----------
       100 data1

例外はありますがヒントはオプティマイザに対する実行計画の決定に作用します。基本的に構文に間違いがなく、リソースが許せばヒントの記述が優先されます。
一方でヒント自体はあくまでコメントであるため、記述ミスがあってもSQL文の実行自体には影響を与えません（例外はありますが）。下記の例ではヒントの記述が間違っています（テーブル名が異なる）が、ヒントは無視されています。しかしオプティマイザは指定したインデックスを選択しています。

SQL> SELECT /*+ INDEX(large2 SYS_C007561) */ * FROM large1 WHERE c1=100;

        C1 C2
---------- ----------
       100 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  2871w0znhyb3t, child number 0
-------------------------------------
SELECT /*+ INDEX(large2 SYS_C007561) */ * FROM large1 WHERE c1=100

Plan hash value: 2248606561

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| LARGE1      |     1 |    11 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007561 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
...

ヒントレポート

従来のバージョンではヒントの記述により実行計画が決定しているのか、ヒントは間違っているがオプティマイザによりその実行計画になったのかを簡単に見極める方法がありませんでした。
Oracle Database 19cでは、DBMS_XPLANパッケージに含まれるDISPLAY*関数の出力でヒントの利用状況を確認できるようになりました。実行計画レポート下部に「Hint Report」として出力されます。ヒントレポートの出力制御にはFORMATパラメーターに以下の値を追加指定することができます。デフォルト値のTYPICALを指定すると、ヒントが使えない場合に限りレポートが出力されます。

設定値 説明
HINT_REPORT ヒントレポートを出力
HINT_REPORT_USED 使われたヒントレポートのみを出力
HINT_REPORT_UNUSED 使われなかったヒントのみ出力

構文エラー

ヒントの構文エラーはFORMATパラメータにどの値を指定しても出力されます。下記の例では、FULLヒントを指定していますが、テーブル名の指定がありません。ヒントレポートの先頭に「Total hints for statement: 1 (E - Syntax error (1))」と出力され、構文エラー（Syntax Error）になっていることがわかります。

SQL> SELECT /*+ FULL */ * FROM large1 WHERE c1=1000;

        C1 C2
---------- ----------
      1000 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  6xwpgg5rv2zu2, child number 0
-------------------------------------
SELECT /*+ FULL */ * FROM large1 WHERE c1=1000

Plan hash value: 2248606561

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| LARGE1      |     1 |    11 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007561 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("C1"=1000)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  FULL

ヒント名の出力部分に「E - FULL」と出力されているように、FULLヒントは構文エラーになっています。

使われないヒント

下記の例では、全件検索を行うFULLヒントと、インデックスを使用するINDEXヒントが矛盾しているためどちらも使われなかったことを示しています。
ヒントレポートの先頭に「Total hints for statement: 2 (U - Unused (2))」　と出力され、2つのヒントが使われなかった（Unused）ことを示しています。

SQL> SELECT /*+ FULL(large1) INDEX(large1 SYS_C007561) */ * FROM large1 WHERE c1=1000;

        C1 C2
---------- ----------
      1000 data1

SQL>  SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  1qjwpcgnnbuyy, child number 0
-------------------------------------
SELECT /*+ FULL(large1) INDEX(large1 SYS_C007561) */ * FROM large1
WHERE c1=1000

Plan hash value: 2248606561

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| LARGE1      |     1 |    11 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007561 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("C1"=1000)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))

   1 -  SEL$1 / LARGE1@SEL$1
         U -  FULL(large1) / hint conflicts with another in sibling query block
         U -  INDEX(large1 SYS_C007561) / hint conflicts with another in sibling query block

各ヒントの先頭に「U -」が出力され、ヒントが使われなかったことを示しています。

テーブル名が間違っている場合等も似た出力になります。下記の例ではFULLヒントに指定するテーブル名が間違っています。ヒントレポートの先頭に「Total hints for statement: 1 (U - Unresolved (1))」　と出力され、ヒントが解決できなかった（Unresolved）ことを示しています。

SQL> SELECT /*+ FULL(l1) */ * FROM large1 WHERE c1=1000;

        C1 C2
---------- ----------
      1000 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------

SQL_ID  97qj844hrqh9b, child number 0
-------------------------------------
SELECT /*+ FULL(l1) */ * FROM large1 WHERE c1=1000

Plan hash value: 2248606561

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| LARGE1      |     1 |    11 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007561 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("C1"=1000)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         N -  FULL(l1)

個別のヒントの先頭に「N -」が出力され、FULLヒントが使われなかったことを示しています。

使われたヒント

下記の例ではFULLヒントが有効に機能したことを示しています。

SQL> SELECT /*+ FULL(large1) */ * FROM large1 WHERE c1=1000;

        C1 C2
---------- ----------
      1000 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'HINT_REPORT_USED'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  44u1p8dv6btm1, child number 0
-------------------------------------
SELECT /*+ FULL(large1) */ * FROM large1 WHERE c1=1000

Plan hash value: 2439585055

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |   620 (100)|          |
|*  1 |  TABLE ACCESS FULL| LARGE1 |     1 |    11 |   620   (1)| 00:00:01 |

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

   1 - filter("C1"=1000)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   1 -  SEL$1 / LARGE1@SEL$1
           -  FULL(large1)

マニュアル

ヒントレポートについてはマニュアル「SQLチューニング・ガイド」に記述されていますが、マニュアル「PL/SQLパッケージおよびタイプ・リファレンス」にはFORMAT句に追加された構文に関する記述がありません。

6
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
1