Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2885399
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2012-12-28 10:04:53

Beginning with Oracle10G, when statistics are gathered for a table, the old statistics are retained so should there be any problem with performance of queries dependent on those statistics, the old ones can be restored.


How long does Oracle retain the statistics for ?

The default period for which statistics are retained is 31 days but this can be altered with:-


execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)


- where xx is the number of days to retain them

NOTE: There is an overhead of storage in the SYSAUX tablespace with statistics so care should be taken not to cause the tablespace to fill with the statistics.


How do I know how many days the statistics are available for ?

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;



- will return the number of days stats are currently retained for.


select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;



- will return the date statistics have been purged up to (so only dates newer then this can possibly be restored to). Any request to restore stats from this date or older will fail with: "ORA-20006: Unable to restore statistics , statistics history not available"

How do I find the statistics history for a given table ?

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history

How do I restore the statistics ?

Having decided what date you know the statistics were good for, you can use:-

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)
ie.
execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');

阅读(1229) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~