OPT_PARAM ヒントを試す（Oracle Database 21c)

Oracle Database のヒントの中で OPT_PARAM ヒントを試します。このヒントを指定すると、ヒントを記述した SQL 文の実行中のみ、初期化パラメーターの値を変更することができます。ALTER SYSTEM 文や ALTER SESSION 文による初期化パラメーター操作をせずに、ヒントだけで一時的な初期化パラメーターの変更を可能にします。

# 構文と使用できる初期化パラメーター

OPT_PARAMヒントには変更する初期化パラメーターと値を指定します。大文字／小文字の違いは無視されます。表記方法は以下の通りです。

```
/*+ OPT_PARAM('初期化パラメーター名', '値') */ 
```
値が数値の場合はシングルクオーテーションで囲まずに値を直接記述します。このヒントで指定できる初期化パラメーターは以下の通りです。

|初期化パラメーター名|説明|
|:---|:---|
|APPROX_FOR_AGGREGATION|集計問合せおよび分析問合せに近似問合せを行うか|
|APPROX_FOR_COUNT_DISTINCT|COUNT (DISTINCT)を近似問い合わせに変換するか|
|APPROX_FOR_PERCENTILE|近似パーセンタイル関数に変換するか|
|OPTIMIZER_DYNAMIC_SAMPLING|動的統計の収集を制御|
|OPTIMIZER_INDEX_CACHING|ネステッド・ループ結合およびINリスト・イテレータを制御|
|OPTIMIZER_INDEX_COST_ADJ|索引を使用する傾向の強さを調整|
|OPTIMIZER_SECURE_VIEW_MERGING|ビューのマージ時のセキュリティ・チェックを制御|
|STAR_TRANSFORMATION_ENABLED|スター・クエリーの有効化|

# 実行例

ヒントを指定しないSQL文を実行し、実行計画を確認します。集約処理には「SORT AGGREGATE」が使われていることがわかります。

```SQL:
SQL> SELECT COUNT(DISTINCT c1) FROM data1;

COUNT(DISTINCTC1)
-----------------
          1000000

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d3cncur6b9r1v, child number 2
-------------------------------------
SELECT COUNT(DISTINCT c1) FROM data1

Plan hash value: 534340379

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18行が選択されました。
```

初期化パラメーター approx_for_count_distinct を true に設定して同じ SQL 文を実行します。実行計画に「SORT AGGREGATE APPROX」が出力され、近似値集計が行われたことがわかります。実行計画の出力に SQL レポートを追加しており、ヒントが有効になっていることがわかります。

```SQL:
SQL> SELECT /*+ OPT_PARAM('approx_for_count_distinct', 'true') */ COUNT(DISTINCT c1) FROM data1;

COUNT(DISTINCTC1)
-----------------
           971092

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fkh1ab4vrdk26, child number 1
-------------------------------------
SELECT /*+ OPT_PARAM('approx_for_count_distinct', 'true') */ COUNT(DISTINCT c1) FROM data1

Plan hash value: 534340379

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |       |       |   511 (100)|          |
|   1 |  SORT AGGREGATE APPROX|             |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C008794 |   970K|    12M|   511   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   0 -  STATEMENT
           -  OPT_PARAM('approx_for_count_distinct', 'true')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

26行が選択されました。
```

　変更できない初期化パラメーターとして optimizer_mode を指定して同じ SELECT 文を実行しています。ヒント・レポートには「Syntax error」が出力されおり、ヒントが無効であることがわかります。

```SQL:
SQL> SELECT /*+ OPT_PARAM('optimizer_mode', 'first_rows') */ COUNT(DISTINCT c1) FROM data1;

COUNT(DISTINCTC1)
-----------------
          1000000

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4q8hm4s1tjfzp, child number 1
-------------------------------------
SELECT /*+ OPT_PARAM('optimizer_mode', 'first_rows') */ COUNT(DISTINCTc1) FROM data1

Plan hash value: 534340379

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

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

   0 -  SEL$1
         E -  OPT_PARAM

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
```