How restore CBO statistics

從Oracle10g開始，當對一個表收集統計資訊時，舊的統計資訊會被保留了，因此當出現新收集的統計資訊引起效能問題時，可以還原舊統計資訊。預設情況下統計資訊會被保留31天，但可以執行execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (days)來進行修改，days為你所指定的天數。

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31
SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);
 
PL/SQL procedure successfully completed
 
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         60

在修改統計資訊保留天數時要確保sysaux表空間有足夠的表空間大小來儲存統計資訊，防止統計資訊寫滿sysaux表空間。

可以執行以下語句來查詢統計資訊的保留天數：

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

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

執行以下語句可以查詢被保留時間最久的統計資訊，早於這個時間點的統計資訊已經被刪除。任何要求還原這個時間點及之前的統計資訊都會報錯"ORA-20006: Unable to restore statistics , statistics history not available"

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
10-APR-16 10.50.36.929152000 PM +08:00

SQL> execute dbms_stats.restore_table_stats ('SCOTT','EMP','10-APR-16 10.50.36.929152000 PM +08:00');

begin dbms_stats.restore_table_stats ('SCOTT','EMP','10-APR-16 10.50.36.929152000 PM +08:00'); end;

ORA-20006: Unable to restore statistics , statistics history not available
ORA-06512: at "SYS.DBMS_STATS", line 17063
ORA-06512: at "SYS.DBMS_STATS", line 17080
ORA-06512: at line 2

要想找到表的統計資訊收集歷史資訊可以執行以下查詢

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ --------------------------------------------------------------------------------
PM_BILL                        12-APR-16 10.06.00.454894 PM +08:00
PM_BILL                        29-APR-16 10.11.24.030930 PM +08:00
PM_BILL                        01-MAY-16 04.44.42.324357 AM +08:00
PM_BILL                        02-MAY-16 07.03.38.762862 AM +08:00
PM_BILL                        03-MAY-16 09.33.53.942353 AM +08:00

還原統計資訊可以根據需要選擇以下幾種方式:
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)

下面介紹一個還原指定表統計資訊的操作:
1.查詢表pm_bill的統計資訊，顯示有232277行記錄。

SQL> select table_name,num_rows,blocks,empty_blocks,sample_size from user_tables where table_name='PM_BILL';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS SAMPLE_SIZE
------------------------------ ---------- ---------- ------------ -----------
PM_BILL                            232277       5164            0       69683

2.查詢表pm_bill收集的統計資訊的次數

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ --------------------------------------------------------------------------------
PM_BILL                        12-APR-16 10.06.00.454894 PM +08:00
PM_BILL                        29-APR-16 10.11.24.030930 PM +08:00
PM_BILL                        01-MAY-16 04.44.42.324357 AM +08:00
PM_BILL                        02-MAY-16 07.03.38.762862 AM +08:00
PM_BILL                        03-MAY-16 09.33.53.942353 AM +08:00

3.查詢表pm_bill中真實的記錄數為235032與統計資訊中記錄的232277有差異

SQL> select count(*) from pm_bill;

  COUNT(*)
----------
    235032

4.查詢統計資訊保留的最早時間

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
10-APR-16 10.50.36.929152000 PM +08:00

5.對錶pm_bill收集統計資訊，取樣比例使用100%

SQL> exec dbms_stats.gather_table_stats(ownname => 'insur_changde',tabname => 'pm_bill',estimate_percent => 100,method_opt => 'for all columns size repeat');

PL/SQL procedure successfully completed

6.查詢表pm_bill的統計資訊，可以看到重新收集統計資訊後表的記錄數與真實記數一樣為235032

SQL> select table_name,num_rows,blocks,empty_blocks,sample_size from user_tables where table_name='PM_BILL';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS SAMPLE_SIZE
------------------------------ ---------- ---------- ------------ -----------
PM_BILL                            235032       5290            0      235032

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ --------------------------------------------------------------------------------
PM_BILL                        12-APR-16 10.06.00.454894 PM +08:00
PM_BILL                        29-APR-16 10.11.24.030930 PM +08:00
PM_BILL                        01-MAY-16 04.44.42.324357 AM +08:00
PM_BILL                        02-MAY-16 07.03.38.762862 AM +08:00
PM_BILL                        03-MAY-16 09.33.53.942353 AM +08:00
PM_BILL                        12-MAY-16 03.06.43.688976 PM +08:00

7.將表pm_bill的統計資訊還原到03-MAY-16 09.33.53.942353 AM +08:00這個時間點

SQL> exec dbms_stats.restore_table_stats ('insur_changde','pm_bill','03-MAY-16 09.33.53.942353 AM +08:00');

PL/SQL procedure successfully completed

SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ --------------------------------------------------------------------------------
PM_BILL                        12-APR-16 10.06.00.454894 PM +08:00
PM_BILL                        29-APR-16 10.11.24.030930 PM +08:00
PM_BILL                        01-MAY-16 04.44.42.324357 AM +08:00
PM_BILL                        02-MAY-16 07.03.38.762862 AM +08:00
PM_BILL                        03-MAY-16 09.33.53.942353 AM +08:00
PM_BILL                        12-MAY-16 03.06.43.688976 PM +08:00
PM_BILL                        12-MAY-16 03.12.55.388126 PM +08:00

7 rows selected

從上面的查詢結果可以看到，表pm_bill的統計資訊收集的歷史資訊多了一條時間為12-MAY-16 03.12.55.388126 PM +08:00
的記錄，這是我們還原統計資訊所產生的。

查詢表pm_bill的統計資訊，可以看到統計資訊中記錄的表的記錄數恢復成了232277行

SQL> select table_name,num_rows,blocks,empty_blocks,sample_size from user_tables where table_name='PM_BILL';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS SAMPLE_SIZE
------------------------------ ---------- ---------- ------------ -----------
PM_BILL                            232277       5164            0       69683

這個功能當新收集統計資訊後，如果引起了效能問題可以做為一個臨時手段還解決效能問題。