統計情報のリストア方法

OracleDB の本番環境にて定期的に統計情報取得していますが、あるタイミングで取得した統計情報から実行計画が変わり、SQL の性能が劣化する場合があります。

今回はそんな時に有効な統計情報のリストアについて説明します。

統計情報リストアを実施する場合の要件

統計情報のリストアを実施する場合、以下の要件を満たす必要があります。

リストアしたい統計情報が、統計情報のバックアップ保持期間内（デフォルト31日）である
統計情報取得に DBMS_STATS パッケージ、SET_*_STATS プロシージャを使用している
(「analyze」コマンドでの統計情報取得では、統計情報のリストアは使用できません。)

統計情報リストア手順

統計情報のリストアは、データベース単位、スキーマ単位、テーブル単位で実施できます。
今回は、テーブル単位でリストアする際の手順を説明します。

1. 統計情報保存期間の確認

統計情報保存期間を確認します。

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
　　　　　　　　　　　　 31

2. 統計情報取得履歴の確認

自動バックアップされた、リストア対象テーブルの統計情報取得履歴を確認します。

select owner, table_name, stats_update_time
from dba_tab_stats_history
where owner=’該当テーブルのオーナ名‘
and table_name=’該当テーブル名‘;

SQL> select owner,table_name,stats_update_time from dba_tab_stats_history
　2  where owner='TESTUSER' and table_name='TEST1';

OWNER　　TABLE_NAME STATS_UPDATE_TIME
-------- ---------- ------------------------------------
TESTUSER TEST1　　　18-01-31 22:04:19.941000 +09:00
TESTUSER TEST1　　　18-02-11 06:00:28.919000 +09:00

3. 統計情報のリストア

SQLの性能が劣化するよりも前に取得した統計情報をリストアします。

exec DBMS_STATS.RESTORE_TABLE_STATS
(ownname　　　　=>’該当テーブルのオーナ名‘,
tabname　　　　 =>’該当テーブル名‘,
as_of_timestamp =>’リストア時刻‘);

as_of_timestamp=>’リストア時刻’には、2.で取得したSTATS_UPDATE_TIMEの値を指定して下さい。
なお、強制的にリストアを行う場合は force オプション「force=>TRUE」を指定します。

SQL> exec DBMS_STATS.RESTORE_TABLE_STATS
　　　　　 (ownname=>'TESTUSER',
　　　　　　tabname=>'TEST1',
　　　　　　as_of_timestamp=>'18-01-31 22:04:19.941000 +09:00');

PL/SQLプロシージャが正常に完了しました。

4. 統計情報取得履歴の再確認

リストアした時間の取得履歴が表示されることを確認します。

SQL> select owner,table_name,stats_update_time from dba_tab_stats_history 　
　2  where owner='TESTUSER' and table_name='TEST1';

OWNER　　TABLE_NAME STATS_UPDATE_TIME
-------- ---------- ------------------------------------
TESTUSER TEST1　　　18-03-01 16:57:32.926000 +09:00　　　★
TESTUSER TEST1　　　18-01-31 22:04:19.941000 +09:00
TESTUSER TEST1　　　18-02-11 06:00:28.919000 +09:00

1行目に新しくリストアした時間の取得履歴が追加されています。

手順の詳細は、以下も参考にしてみてください。

[参考] 門外不出のOracle現場ワザ/Part3　オプティマイザ統計の管理
http://www.oracle.com/technetwork/jp/articles/chapter4-3-093504-ja.html#p01a

[参考] Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_stats.htm