オプティマイザ・ヒントを無視する設定を試す（Oracle Database 18c）

オンプレミス版の Oracle Database 18c が提供されるようになったため新機能を検証します。まずはオプティマイザ・ヒントを無視する設定の検証です。

# ヒントを無視する
チューニングの一貫として SQL 文の実行計画を一部変更するためにヒントを埋め込むことがあります。ヒントの記述は構文が正しく、リソースが許せば基本的に記述通りに実行計画を変更することができます。
しかし、何らかの事情で既に記述されたヒントを無視したい場合には、初期化パラメータ optimizer_ignore_hints を TRUE に設定します。この初期化パラメーターは、旧バージョンでは隠しパラメーターでしたが、Oracle Database 18c では標準パラメーターになりました。

単純な例で検証してみます。まずヒントを指定しない場合の実行計画を確認すると主キーを使って検索（INDEX UNIQUE SCAN）しています。

```
SQL> SELECT * FROM scott.data1 WHERE c1=100;

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

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  7xw9uvjxbzxxg, child number 0
-------------------------------------
SELECT * FROM scott.data1 WHERE c1=100

Plan hash value: 3576731475

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

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

   2 - access("C1"=100)


19 rows selected.
```

次に FULL ヒントを指定して同じ SQL 文を実行します。実行計画がテーブル全件検索（TABLE ACCESS FULL）になっていることがわかります。

```
SQL> SELECT /*+ FULL(data1) */ * FROM scott.data1 WHERE c1=100;

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

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  amxwr9u49tq4k, child number 0
-------------------------------------
SELECT /*+ FULL(data1) */ * FROM scott.data1 WHERE c1=100

Plan hash value: 2673507129

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

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

   1 - filter("C1"=100)


18 rows selected.
```

初期化パラメーター optimizer_ignore_hints を TRUE に設定すると、FULL ヒントが無視されて主キー検索に戻ることがわかります。

```
SQL> ALTER SESSION SET optimizer_ignore_hints = TRUE;

Session altered.

SQL> SELECT /*+ FULL(data1) */ * FROM scott.data1 WHERE c1=100;

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

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  amxwr9u49tq4k, child number 1
-------------------------------------
SELECT /*+ FULL(data1) */ * FROM scott.data1 WHERE c1=100

Plan hash value: 3576731475

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

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

   2 - access("C1"=100)


19 rows selected.

```

マニュアルの記載は以下のようになっています。

>　このパラメータをTRUEに設定すると、オプティマイザでは埋込みヒントが無視されます。 

これだけ読むと、すべてのヒントが無視されるように見えますが、実際にはこの初期化パラメーターを TRUE に設定しても一部のヒントは無視されません。

## パラレル・ヒント
パラレル・クエリーに関するヒントを無視する場合には、別の初期化パラメーター optimizer_ignore_parallel_hints を TRUE に設定します。
下記の例では、PARALLEL ヒント付の SELECT 文を実行することで、パラレル・クエリーが動作していることがわかります。

```
SQL>  SELECT /*+ PARALLEL(data1, 2) */ COUNT(*) FROM scott.data1;

  COUNT(*)
----------
   1000000

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  2cut69wsr078t, child number 0
-------------------------------------
 SELECT /*+ PARALLEL(data1, 2) */ COUNT(*) FROM scott.data1

Plan hash value: 1728043381

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |   343 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000K|   343   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| DATA1    |  1000K|   343   (1)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

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

   6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 2 because of table property


27 rows selected.
```

しかし、初期化パラメーター optimizer_ignore_parallel_hints を TRUE に設定すると実行計画がシリアルになります。

```
SQL> ALTER SESSION SET optimizer_ignore_parallel_hints = TRUE;

Session altered.

SQL> SELECT /*+ PARALLEL(data1, 2) */ COUNT(*) FROM scott.data1;

  COUNT(*)
----------
   1000000

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8xr9fard0tfda, child number 1
-------------------------------------
SELECT /*+ PARALLEL(data1, 2) */ COUNT(*) FROM scott.data1

Plan hash value: 4109052708

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |       |   570 (100)|          |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_DATA1 |  1000K|   570   (1)| 00:00:01 |
--------------------------------------------------------------------------


14 rows selected.

```

## その他のヒント
これら２つの初期化パラメーターを TRUE に設定しても実際には動作するヒントがほかにもあります。下記のように IGNORE_ROW_ON_DUPKEY_INDEX ヒント等は動作を止めることができません。

```
SQL> ALTER SESSION SET optimizer_ignore_hints = TRUE;

Session altered.

SQL> ALTER SESSION SET optimizer_ignore_parallel_hints = TRUE;

Session altered.

SQL> INSERT INTO scott.data1 VALUES (100, 'duplicate');
INSERT INTO scott.data1 VALUES (100, 'duplicate')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DATA1) violated


SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(data1(c1)) */ INTO scott.data1 VALUES (100, 'duplicate');

0 rows created.
```