10G，11G恢复表的统计信息

10G，11G恢复表的统计信息


适用的Oracle Version 10.1.0.2 to 11.2.0.3  


oracle 10G开始，当一个表生成新的统计信息的同时，该表的旧统计信息会保留，如果以后依赖该旧统计信息的查询出现性能问题，可以恢复旧的统计信息使该查询使用先前的统计信息来解决可能出现的性能问题oracle 10g,11g 中oracle 对象统计信息默认的保存时间是31天，统计信息在sysaux表空间有存储开销，应该注意sysaux不要被统计信息填满


sql> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

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


如果对统计信息的保存需要处理可以利用DBMS_STATS.ALTER_STATS_HISTORY_RETENTION包更改对象的统计信息保存时间

eg:更改对象统计信息保存10天

SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(10);

PL/SQL procedure successfully completed.

查询更改后的统计信息保留时间
sql> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         10
                         

查询数据库上次统计信息的废弃日期，由于默认是保存31天所以上次的废弃时间就是2012年10月16日 

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
16-OCT-12 08.29.06.665968000 AM +08:00


该DB的统计信息废弃时间是16-OCT-12，如果恢复的统计信息在16-OCT-12之前（例如 10-OCT-12）的将会报出ora-20006的错误，
换句话说只能恢复以后的统计信息


查询统计信息最后的更新时间可以使用以下SQL

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

 


如何恢复对象的统计信息


首先我们创建一个T1表来测试

SQL> create table t1 as select * from dba_source;

Table created.

SQL> commit;

Commit complete.

收集T1当前的统计信息

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.

现在查看下T1更新统计信息的时间是2012-11-16 4点24分25秒

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

TABLE_NAME           STATS_UPDATE_TIME
-------------------- --------------------------------------------------------
T1                   16-NOV-12 04.24.45.244868 PM +08:00


我们对表T1进行插入些新的值

SQL> insert into t1 select * from dba_source;

632812 rows created.

SQL> insert into t1 select * from dba_source;

632812 rows created.

SQL> commit;

Commit complete.

提交之后再次重新收集T1的统计信息 

SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.

查看T1更新统计信息的时间，2012-11-16 4点26分7秒
SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='T1';

TABLE_NAME           STATS_UPDATE_TIME
----------------   ----------------------------------------------
T1                   16-NOV-12 04.24.45.244868 PM +08:00
T1                   16-NOV-12 04.26.07.310670 PM +08:00




SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------
16-OCT-12 08.29.06.665968000 AM +08:00

恢复T1的统计信息至‘16-NOV-12 04.24.45.244868 PM +08:00’时间段

SQL> execute dbms_stats.restore_table_stats ('SYS','T1','16-NOV-12 04.24.45.244868 PM +08:00');

PL/SQL procedure successfully completed.


再次查询T1统计信息更新的时间

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

TABLE_NAME           STATS_UPDATE_TIME
----------------   -------------------------------------------
T1                   16-NOV-12 04.24.45.244868 PM +08:00
T1                   16-NOV-12 04.26.07.310670 PM +08:00
T1                   16-NOV-12 04.27.25.924217 PM +08:00

未完 待续