oracle 統計資訊過期判斷和自動收集

xychong123發表於2016-12-16
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部落格 ” ，連結：http://blog.itpub.net/20747382/viewspace-2130822/，如需轉載，請註明出處，否則將追究法律責任。

相關文章