統計情報の履歴やロックなど

●統計情報履歴

まずは、統計情報の履歴です。これは便利です。
10g 以降において、SQLの実行計画が悪化した場合、まずは過去の統計情報を戻すことを考えてみてください。Oracleが自動的に過去の統計情報を約1ヶ月 保持しているはずです。今日の朝からSQLの性能が悪化したのであれば、昨日の統計に戻してみると改善するかもしれません。

例：
SELECT * FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='TEST_FOR_STATS';

SYSTEM TEST_FOR_STATS

08-10-09 22:55:38.859000 +09:00

SYSTEM TEST_FOR_STATS

08-10-09 22:57:48.906000 +09:00

このように統計情報がバージョン管理されています。
注：analyzeでは過去統計情報が残らないため注意してください。

統計を過去に戻す方法ですが、次のように行います。

例：
exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'SYSTEM',tabname=>'TEST_FOR_STATS',as_of_timestamp=>'08-10-09 22:56:38.859000');

なお、DBMS_STATS.RESTORE_DATABASE_STATS で、データベース全体が、
DBMS_STATS.RESTORE_SCHEMA_STATS で、スキーマ全体が戻ります。

ここら辺のプロシージャの詳細は、PL/SQL パッケージ・プロシージャマニュアルをご覧ください。日本オラクルのOTNからマニュアルは無料でダウンロードできます。

なお、9i以前のOracleの場合、DBMS_STATSで手動でバックアップをとることができます。

●統計情報のロック

次は、統計情報のロックです。性能を変えたくない場合、ロックすることで統計が変わらないようにできます。これにより実行計画はほぼ固定となります。

DBMS_STATSのLOCK_SCHEMA_STATS や LOCK_TABLE_STATS、
UNLOCK_SCHEMA_STATS や UNLOCK_TABLE_STATSを用います。

例：
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('SYSTEM', 'TEST_FOR_STATS');

注意点は、データが少ないときに固定してしまうと、Oracleはフルスキャンを選びやすいという点があげられます。つまり、データが多くなってもフルスキャンのままになって性能がどんどん劣化する場合もありえます。

●ヒストグラム(分布に関する情報)

10g以降において、統計情報の収集では、ヒストグラムも収集されえるようになりました(デフォルト値)。ヒストグラムも、実行計画が変わる要素です。9iまでと同様に取らないというのも手です。

DBMS_STATSの METHOD_OPT というパラメータがあり、これがデフォルトで'FOR ALL COLUMNS SIZE AUTO'となっています。この場合、ヒストグラムをとってしまうことがあります。

ただし、DWHなどでは、ヒストグラムがあるからこそ速くなるケースもあるため、きちんと検討した上で止めてください。OLTP系ではヒストグラムはあまり必要ないかもしれません。

● バインドピークの停止

実 は、Oracleは9i以降、バインド変数の中をピーク(peek：のぞく)して、実行計画を立てる参考にしています。これが実行計画を変動させることが あります。この機能は目的があって導入されているので、いちがいに止める必要はありませんが、実行計画を変動させたくない場合には、これの停止も検討して よいでしょう。

個人的に止め方を書くことはできないので、Oracleのサポートやインターネットで検索して止め方を調べてみてください。「バインドピーク 無効」といったキーワードで出てくるはずです。

●そのほか

なお、実行計画はキャッシュされることもあるため、alter system flush shared_pool などでメモリ上のキャッシュをクリアすることも、必要に応じて実行してください。
また、統計を取得していない場合、Oracleはメモリ上に一時的な統計情報を置く(ダイナミックサンプリング)ことがあります。これも実行計画を変動させます。alter system flush shared_pool でこれも消すことができます。

今回紹介したノウハウを使いこなせば、実行計画はだいたい安定するのではないでしょうか？　まだまだ統計情報のノウハウは続きます。