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.
DBA_TAB_STATISTICS===>>>
| STALE_STATS | VARCHAR2(3) | Indicates whether statistics for the object are stale (YES) or not (NO) |
查看之前的统计信息：
select * from DBA_OPTSTAT_OPERATIONS where TARGET like '%DWE_TASKS' order by 3;
select * from dba_TAB_STATS_HISTORY where TABLE_NAME='DWE_TASKS';
select object_id,OBJECT_NAME,LAST_DDL_TIME from DBA_OBJECTS where OBJECT_NAME ='DWE';
select OBJ# ,ROWCNT,BLKCNT,AVGRLN,SAMPLESIZE,ANALYZETIME,SAVTIME from sys.WRI$_OPTSTAT_TAB_HISTORY where obj#=3459727;
select * from table(dbms_stats.diff_table_stats_in_history('TKCSOWNER','DWE',systimestamp,to_timestamp(to_date('26-12-16 10.00.00','dd-mm-yy hh24:mi:ss'))));
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20747382/viewspace-2130822/ ，如需转载，请注明出处，否则将追究法律责任。
