How to restore table statistics

收集统计信息的目的是为了使执行计划准确，有时统计信息收集后执行计划发生了改变，我们期待回归之前的执行计划。ORACLE提供了相应的过程可以回退到之前的统计信息，进而执行计划也会回退。[@more@]

1、查一下某张表统计信息的历史
SQL> SELECT OWNER,
2 TABLE_NAME,
3 TO_CHAR(STATS_UPDATE_TIME, 'YYYYMMDD HH24:MI:SS')
4 FROM DBA_TAB_STATS_HISTORY
5 WHERE OWNER = 'UCR_ACT4'
6 AND TABLE_NAME = 'TI_O_CREDIT_WORK'
7 ORDER BY 3
8 /

OWNER TABLE_NAME TO_CHAR(STATS_UPDATE_TIME,'YYY
------------------------------ ------------------------------ ------------------------------
UCR_ACT4 TI_O_CREDIT_WORK 20130305 15:36:25
UCR_ACT4 TI_O_CREDIT_WORK 20130306 10:31:01

2 rows selected

SQL>

2、执行回退
SQL> EXEC DBMS_STATS.RESTORE_TABLE_STATS (OWNNAME=>'UCR_ACT4', TABNAME=>'TI_O_CREDIT_WORK', AS_OF_TIMESTAMP=>TO_DATE('20130305 15:36:25', 'YYYYMMDD HH24:MI:SS'));

PL/SQL procedure successfully completed

SQL>

3、查一下
SQL> SELECT OWNER, TABLE_NAME, LAST_ANALYZED
2 FROM DBA_TAB_STATISTICS
3 WHERE OWNER = 'UCR_ACT4'
4 AND TABLE_NAME = 'TI_OH_CREDIT_WORK'
5 /

OWNER TABLE_NAME LAST_ANALYZED
------------------------------ ------------------------------ -------------
UCR_ACT4 TI_OH_CREDIT_WORK 2013-3-5 9:41

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/717880/viewspace-1060264/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/717880/viewspace-1060264/