SQLを実行すると、たまにインデックスを使ったほうが高速であるにもかかわらず全表スキャンが選択され、パフォーマンスが振るわないということがあります。
こういった現象の一因には、オプティマイザがキャッシュ効率を考慮せずに常にI/Oが発生するものとしてコスト計算を行っているということがあるようです。
（インデックスはバッファ・キャッシュに存在する可能性が高いため、実際には見積もりコストよりも高速にアクセスできるケースが多い）
このような現象が頻発する場合、OPTIMIZER_INDEX_CACHING、OPTIMIZER_INDEX_COST_ADJ という初期化パラメータの設定を調整すると、インデックス・スキャンのコストを低く計算させ、全表スキャンよりもインデックス・スキャンが選択される可能性を高めることができるようです。
- OPTIMIZER_INDEX_CACHING
- インデックス・ブロックが何％くらいバッファ・キャッシュに存在すると仮定するかを示します。0～100の範囲で指定し、デフォルトは 0 となっています。この値を高くすればするほど、インデックスのキャッシュヒット率が高いものとみなされ、インデックス・スキャンのコストが低く見積もられるようです。
- OPTIMIZER_INDEX_COST_ADJ
- インデックス・スキャンのコストを標準のコストの何％で計算するかを示します。0～100の範囲で指定し、デフォルトは100となっています。この値を小さくすればするほど、インデックス・スキャンのコストが低く見積もられるようです。
実際に、全表スキャンが発生するような SQL をサンプルに、これらのパラメータの設定を調整しながら実行計画の変化を見てみたところ、OPTIMIZER_INDEX_CACHING を高く、OPTIMIZER_INDEX_COST_ADJ を低く設定すると、インデックス・スキャンに変更され、全体のコストも低下することが確認できました。
チューニング方法の１つとして覚えておきたいパラメータです。