Oracleの統計情報のリストア方法

技術

統計情報のリストアをこの前行ったのでメモしておく。

統計情報のリストアってなに？

ある時点のあるDBの統計情報を戻す機能で例えば次のような時に役に立つ

  • 本番環境で定期的に統計情報取得していたが、突然ある時の統計取得から実行計画が変わり遅くなった。実行計画を元に戻すために統計情報を戻したい。

とか

  • 本番環境でレスポンスが遅いけど、開発環境では早いというのはよくある話。そこで本番と同じ実行計画を開発環境に適用したい。

まずは前者のある時点に統計情報を戻す方法

次のコマンドで統計情報のリストアが可能。

exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'SCOTT', tabname=>'EMP', as_of_timestamp=>'2009/03/01 18:00');

そのままだけど、SCOTTユーザのEMPテーブルを'2009/03/01 18:00'時点に戻すというコマンド。
注意点としてAnalyzeで取得した統計情報はリストアができない。
DBMS_STATSで統計を取得した場合のみ統計情報のリストアが可能になる。
analyzeは下位互換性用のコマンドみたいだから下記DBMS_STATSで行ったほうが良い。

exec DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'SCOTT', INDNAME=>'PK_EMP');
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'EMP',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);

あと、リストア用のSQLを生成するSQLをのせておく。

SELECT
  OWNER,
  TABLE_NAME,
  STATS_UPDATE_TIME,
  'exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>' || chr(39) || OWNER || chr(39) || ', tabname=>' || chr(39) || TABLE_NAME || chr(39) || ',as_of_timestamp=>' || chr(39) || TO_CHAR(STATS_UPDATE_TIME,'yy-mm-ddhh24:mi:ss.ff3') || chr(39) || ')' AS RESTORE_SQL
FROM
  DBA_TAB_STATS_HISTORY
WHERE
    OWNER = 'SCOTT'
AND TABLE_NAME = 'EMP'
ORDER BY OWNER, TABLE_NAME, STATS_UPDATE_TIME DESC
/

次は本番環境から開発環境に統計情報を移動する方法

全体のおおまかな流れは以下の通り。（本番環境と開発環境でユーザ名が異なっても大丈夫です。）
1.DBMS_STATS.CREATE_STAT_TABLEプロシージャを使用し、統計保存用テーブルを作成
2.作成した統計保存用テーブルに統計情報をコピー
3.エクスポートにて統計情報をエクスポート
4.インポートにて統計情報をインポート
5.統計保存用テーブルから統計情報を戻す
（1-3は本番環境で実施、4-5は開発環境で実施）

1.DBMS_STATS.CREATE_STAT_TABLEプロシージャを使用し、統計保存用テーブルを作成
exec DBMS_STATS.CREATE_STAT_TABLE('SCOTT', 'SAVE_STATS_SCOTT');

SCOTTユーザにSAVE_STATS_SCOTTというテーブルが作成される。

2.作成した統計保存テーブル'SAVE_STATS_SCOTT'にテーブルとインデックスの統計を保存する
-- TABLE
exec DBMS_STATS.EXPORT_TABLE_STATS('SCOTT','EMP', stattab =>'SAVE_STATS_SCOTT');
-- INDEX
exec DBMS_STATS.EXPORT_INDEX_STATS('SCOTT','PK_EMP', stattab =>'SAVE_STATS_SCOTT');
3.エクスポートにて統計情報をエクスポート

SAVE_STATS_SCOTTテーブルをexpする
（略）

4.インポートにて統計情報をインポート

SAVE_STATS_SCOTTテーブルをimpする
（略）

5.統計保存用テーブルから統計情報を戻す
-- TABLE
exec DBMS_STATS.IMPORT_TABLE_STATS('SCOTT','EMP', stattab =>'SAVE_STATS_SCOTT',statown =>'SCOTT');
-- INDEX
exec DBMS_STATS.IMPORT_INDEX_STATS('SCOTT','PK_EMP', stattab =>'SAVE_STATS_SCOTT',statown =>'SCOTT');

statownはSAVE_STATS_SCOTTテーブルを保持しているユーザ名です。

詳細はこちらで
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-02/d_stats.htm