【ASK_ORACLE】Oracle如何恢復舊的統計資訊

前言

有時候統計資訊的改變不一定會提升SQL執行效率，相反可能會大幅降低執行效率。如果我們不想再次收集新的統計資訊，Oracle可以恢復到某一時刻的舊統計資訊，下面我們來看看如何恢復吧！

一些關於統計資訊的SQL語句參考：

1.【TUNE_ORACLE】檢查統計資訊是否過期SQL參考： http://blog.itpub.net/69992972/viewspace-2785127/

2.【TUNE_ORACLE】定製化收集統計資訊SQL參考： http://blog.itpub.net/69992972/viewspace-2784605/

3.【TUNE_ORACLE】檢視錶，列和索引的統計資訊SQL參考： http://blog.itpub.net/69992972/viewspace-2784114/

實驗環境

搭建平臺：VMware Workstation

OS：OL 6.10

DB：Oracle 11.2.0.4

恢復方法

1. 設定統計資訊保留時間

和AWR、備份一樣，統計資訊也有自己的保留時間，我們可以根據實際需要來設定（預設31天）：

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (天數);

比如我們保留60天：

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (60);

 

我們來檢視下當前的保留天數，和最早能恢復到的時間：

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

---------------------------------------------------

60              ##已經修改成保留60天了

 

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY

------------------------------------------------------------------------------

19-NOV-22 04.52.41.089721000 PM +8:00      ##這裡由於之前是31天保留時間，所以就算更新到60天，也不會找到比這個時間更老的資料了，因為已經覆蓋了。

 

2. 恢復統計資訊

有幾種恢復統計資訊的方式：

（1）恢復表的統計資訊 （最常用！）

SQL> exec DBMS_STATS.RESTORE_ TABLE_STATS ('owner','table',date)

舉例：

SQL> exec dbms_stats.restore_table_stats ('TEST','TBS','19-NOV-22 04.52.41.089721000 PM +8:00');

 

（2）恢復使用者的統計資訊（較常用）

SQL> exec DBMS_STATS.RESTORE_ SCHEMA_STATS('owner',date)

 

（3）恢復資料庫的統計資訊

SQL> exec DBMS_STATS.RESTORE_ DATABASE_STATS(date)

 

（4）恢復資料字典的統計資訊

SQL> exec DBMS_STATS.RESTORE_ DICTIONARY_STATS(date)

（5）恢復基表（X$）的統計資訊

SQL> exec DBMS_STATS.RESTORE_ FIXED_OBJECTS_STATS(date)

 

（6）恢復system的統計資訊

SQL> exec DBMS_STATS.RESTORE_ SYSTEM_STATS(date)