Oracleチューニング　オプティマイザ・チューニング　OPTIMIZER_INDEX_COST_ADJ　OPTIMIZER_INDEX_CACHING

SQL> show parameter OPTIMIZER_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>



●OPTIMIZER_INDEX_CACHING
索引ブロックが何％くらいバッファ・キャッシュに存在すると仮定するかを示す。
0～100の範囲で指定し、デフォルトは 0 。
この値を高いほど、インデックスのキャッシュヒット率が高いものと判断され、
ネステッド・ループ結合を使用したインデックス・スキャンのコストが低く見積もられる。
●OPTIMIZER_INDEX_COST_ADJ
インデックス・スキャンのコストを標準のコストの何％で計算するかを示す。
0～100の範囲で指定し、デフォルトは100。
この値が小さいほど、インデックス・スキャンのコストが低く見積もられる。

初期設定値としては以下を目安

OPTIMIZER_INDEX_COST_ADJ = 25
OPTIMIZER_INDEX_CACHING = 90

実際に、全表スキャンが発生するような SQL をサンプルに、これらのパラメータの設定を調整しながら実行計画の変化を見てみたところ、OPTIMIZER_INDEX_CACHING を高く、OPTIMIZER_INDEX_COST_ADJ を低く設定すると、インデックス・スキャンに変更され、
全体のコストも低下することを確認。 

フルスキャンのほうがコストが低いと判断され、インデックスが使用されない場合に調整するパラメータ
ヒント句でも使用可能

OPTIMIZER_INDEX_COST_ADJ	>	0		0～100
OPTIMIZER_INDEX_CACHING		<	100		0～100


--ヒント句
/*+ OPT_PARAM('OPTIMIZER_INDEX_COST_ADJ',10) */
/*+ OPT_PARAM('OPTIMIZER_INDEX_CACHING',10) */
--合わせ技
/*+ OPT_PARAM('OPTIMIZER_INDEX_COST_ADJ',10) OPT_PARAM('OPTIMIZER_INDEX_CACHING',100) */

--バッファキャッシュクリア
alter system flush buffer_cache ;
--共有プールクリア
alter system flush shared_pool ;

--システム全体の変更（再起動が必要）
ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 25;
ALTER SYSTEM SET OPTIMIZER_INDEX_CACHING = 90;

--セッション単位
--◆初期化
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 25;
ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90;