恢复历史统计信息

SQL> select dbms_stats.get_stats_history_retention from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31
 
SQL> create table test as select * from tabs;
 
Table created
 
SQL> select * from dba_tab_stats_history where table_name='TEST';
 
OWNER                          TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
 
SQL> exec dbms_stats.gather_table_stats('SYS',tabname => 'test');
 
PL/SQL procedure successfully completed
 
SQL> select * from dba_tab_stats_history where table_name='TEST';
 
OWNER                          TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            TEST                                                                                         03-8月 -10 04.07.50.140000 下午 +08:00
 
SQL> select blocks from user_tables where table_name='TEST';
 
    BLOCKS
----------
        21
 
SQL> insert into test select * from test;
 
710 rows inserted
 
SQL> exec dbms_stats.gather_table_stats('SYS',tabname => 'test');
 
PL/SQL procedure successfully completed
 
SQL> select blocks from user_tables where table_name='TEST';
 
    BLOCKS
----------
        44
 
SQL> select * from dba_tab_stats_history where table_name='TEST';
 
OWNER                          TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            TEST                                                                                         03-8月 -10 04.07.50.140000 下午 +08:00
SYS                            TEST                                                                                         03-8月 -10 04.09.12.718000 下午 +08:00
 
SQL> exec dbms_stats.restore_table_stats(ownname => 'sys',tabname => 'test',as_of_timestamp => '03-8月 -10 04.07.50.140000 下午 +08:00');
 
PL/SQL procedure successfully completed
 
SQL> select blocks from user_tables where table_name='TEST';
 
    BLOCKS
----------
        21