使用restore_table_stats还原统计信息

10G中可以使用dbms_stats.restore_table_stats恢复表老的统计信息，这功能还是挺不错的。因为统计信息的变化有时会引起某些语句执行计划变差，这时恢复原有统计信息，常常会有效果。当然分析前备份老的统计信息，始终是个好习惯。

查询统计信息保存时间，当然这个时间不一定能保证：

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

也可以通过execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45) 来修改这个保存时间。

查询统计信息能恢复到哪个时间点：

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------------------------
20-NOV-11 10.08.13.843271000 PM +08:00

小测试：
当前统计信息：

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               108046         100 HEIGHT BAL .000015395 C3020104        C30C512D        2011-12-22 14:53
                                    ANCED
 
NAME               7775         100 HEIGHT BAL .001328021 2F3130303065386 73756E2F7574696 2011-12-22 14:53
                                    ANCED                 4315F4C696E6B65 C2F427564646869
                                                          64486173684D617 737443616C656E6
                                                          056616C75654974 46172

恢复到分析之前：

SQL> select to_timestamp ('2011-12-22 14:52','yyyy-mm-dd hh24:mi') from dual;
 
TO_TIMESTAMP('2011-12-2214:52','YYYY-MM-DDHH24:MI')
---------------------------------------------------------------------------
22-DEC-11 02.52.00.000000000 PM
 
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  ownname => 'SYS',
  4  tabname => 'T1',
  5  as_of_timestamp => '22-DEC-11 02.52.00.000000000 PM'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               118045         100 HEIGHT BAL .000013915 C102            C30C512E        2011-12-22 14:45
                                    ANCED
 
NAME              11656         100 HEIGHT BAL  .00104712 2F3130343866633 73756E2F746F6F6 2011-12-22 14:45
                                    ANCED                 9355F5772617054 C732F747265652F
                                                          6F6B656E5772617 5768696C6553746
                                                          0546F6B656E496E 174656D656E74

再次恢复到2011-12-22 14:45之前的统计信息：

SQL> select to_timestamp ('2011-12-22 14:44','yyyy-mm-dd hh24:mi') from dual;
 
TO_TIMESTAMP('2011-12-2214:44','YYYY-MM-DDHH24:MI')
---------------------------------------------------------------------------
22-DEC-11 02.44.00.000000000 PM
 
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  ownname => 'SYS',
  4  tabname => 'T1',
  5  as_of_timestamp => '22-DEC-11 02.44.00.000000000 PM'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               117773           1 NONE       8.4909E-06 C11B            C30C5108        2011-12-21 16:15
NAME               2617           1 NONE       .000382117 2F3130303562643 73756E2F746F6F6 2011-12-21 16:15
                                                          3305F4C6E6B6443 C732F747265652F
                                                          6F6E7374616E74  4F7245787072657
                                                                          373696F6E

— The End —