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> 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> 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> 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)