Oracleの統計情報と実行計画の固定化

統計情報と実行計画についていつもごちゃごちゃになるのでメモ

統計情報とは

データベースの情報（テーブル、表領域、インデックス、NULLの有無、行数など・・・）

データベースの情報をもとに生成されるもの。（データベースの情報とイコールではない）

Oracleのデフォルトでは自動オプティマイザ統計収集という機能で、自動的に統計情報を収集するようになっている。

SQLを実行するときの方法。全行取得するのか、インデックスを見るのか　などの最適化結果。通常統計情報をもとにCBO(コストベースオプティマイザ）が自動的に決定する。

ここでよく問題になるのが、エンタープライズなどでの大規模DBで深夜バッチなどがある場合。

昼間によく実行されるSQLと夜間に実行されるSQLの性質が大きく異なることがある。

こういった場合に、深夜バッチの統計情報をもとに実行計画を作ってしまうと、昼間のSQLに最適化されておらず、パフォーマンス劣化を引き起こすことがある。

なので、このようなケースでは実行計画の固定を考える。

基本的には実行計画は統計情報をもとに作られるので、統計情報を固定化すればよいということになる。

DBMS_STATS.LOCK_TABLE_STATSプロシージャを使用してロックを行う。

しかし、これだけでは実際には実行計画が予期せず変更されてしまう場合がある。例えば以下のような隠しパラメータを考慮する必要がある。

_OPTIMIZER_USE_FEEDBACK

11gR2より実装されたCardinality Feedback機能の有効・無効を切り替えるもので、デフォルトはtrue

カーディナリティとはテーブル内のカラムに格納されているデータの種類がどのくらいあるのかを示す値。

例えば都道府県であれば47種類しかないし、曜日であれば7種類しかない。

曜日は都道府県よりもカーディナリティが低い　と表現する。

_optimizer_use_feedbackがtrueになっていると、実行計画を生成する際に統計情報に加えてカーディナリティの情報も加えて最適化する。

なので、統計情報をロックしていたとしても実行計画が変更されることがありうる。

_OPTIM_PEEK_USER_BINDS

9iから追加されたバインドピーク機能の有効・無効を設定する隠しパラメータで、デフォルトはtrue

バインドピーク機能とは、実際にバインド変数にセットされた値をもとに実行計画を決める機能。

バインド変数のデフォルト値ではなく実際のバインド変数の値を見る(peek)ことで、実行計画を最適化するための機能だが、毎回実行計画の最適化を行うわけではなく、ハードパースが実行されたタイミングのバインド変数の値がセットされる。

このため例外的な値をセットしてしまうと、そのあとの実行計画がパフォーマンスの悪いものとなってしまう。

参考: