oracle 统计信息过期判断和自动收集

Stale statistics

Statistics gathered by DBMS_STATS can become out-of-date. Typically, statistics are stale when 10% or more of the rows in the table have changed since the last time statistics were gathered.

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE orGATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.
=======
查询上次收集表的统计信息收集时间：

• Catalog view DBA_OPTSTAT_OPERATIONS contain history of statistics operations performed at schema and database level using DBMS_STATS.

• The views *_TAB_STATS_HISTORY views (ALL, DBA, or USER) contain a history of table statistics modifications.
  
  ==========
  oracle默认保留31天的统计信息：
  The database purges old statistics automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. You can configure retention using the ALTER_STATS_HISTORY_RETENTION procedure of DBMS_STATS. The default value is 31 days, which means that you would be able to restore the optimizer statistics to any time in last 31 days.

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;


一个表中被修改的行数超过stale_percent(缺省值10%)时就会认为这个表的统计数据过时了.oracle会监控所有表的DML活动并在SGA中进行记录.监控的信息会定时的刷新到磁盘且可以通过*_tab_modifications视图来查看.
也可以调用dbms_stats.flush_database_monitoring_info过程来手动刷新这些数据.如果想在查询时得到最新信息(在所有统计数据收集之前内部监控数据会被刷新).可以通过查询user_tab_statistics视图中的stale_stats列来查看哪个表的统计数据过时了.

表的stale_stats被设置为NO,统计数据是最新的.表的stale_stats被设置为YES,统计数据是过时的.表的stale_stats没有被设置说明丢失统计数据.

DBA_TAB_STATISTICS===>>>

STALE_STATS

VARCHAR2(3)

Indicates whether statistics for the object are stale (YES) or not (NO)

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

转载于:http://blog.itpub.net/20747382/viewspace-2130710/