i found that if I using "dba_tab_statistics" to get all the table where stale_stats = 'YES'
I will always have:
- Code: Select all
OWNER TABLE_NAME OBJECT_TYPE NUM_OF_ROW LAST_ANALYZED
SYS AQ$_ALERT_QT_L TABLE 0 07-Jan-2013 22:00:35
SYS MON_MODS$ TABLE 0 07-Jan-2013 22:00:36
SYS DAM_LAST_ARCH_TS$ TABLE 1 07-Jan-2013 16:22:38
SYS KET$_AUTOTASK_STATUS TABLE 1 07-Jan-2013 22:00:22
SYS WRH$_TEMPFILE TABLE 1 06-Jan-2013 06:00:08
SYS WRM$_WR_CONTROL TABLE 1 07-Jan-2013 22:00:23
SYS WRI$_DBU_CPU_USAGE_SAMPLE TABLE 2 07-Jan-2013 16:24:00
SYS WRM$_DATABASE_INSTANCE TABLE 2 07-Jan-2013 22:00:25
SYS JOB$ TABLE 3 07-Jan-2013 22:00:21
SYS KET$_CLIENT_TASKS TABLE 3 07-Jan-2013 22:00:23
SYS WRI$_ALERT_OUTSTANDING TABLE 3 07-Jan-2013 22:00:20
SYS WRH$_SERVICE_NAME TABLE 5 06-Jan-2013 22:08:54
SYS SCHEDULER$_WINDOW TABLE 9 07-Jan-2013 22:00:32
SYS SCHEDULER$_JOB TABLE 15 07-Jan-2013 22:00:21
SYS WRH$_TABLESPACE TABLE 15 06-Jan-2013 14:08:02
SYSMAN MGMT_TARGETS TABLE 16 07-Jan-2013 22:00:24
SYS WRH$_DATAFILE TABLE 25 06-Jan-2013 14:08:00
SYSMAN MGMT_COLLECTION_TASKS TABLE 31 07-Jan-2013 22:00:24
SYS WRI$_SQLTEXT_REFCOUNT TABLE 50 07-Jan-2013 16:24:00
SYS DBMS_LOCK_ALLOCATED TABLE 55 07-Jan-2013 22:00:29
SYSMAN MGMT_PURGE_POLICY_TARGET_STATE TABLE 91 07-Jan-2013 16:22:25
SYS WRH$_OPTIMIZER_ENV TABLE 187 07-Jan-2013 18:39:04
SYS SEQ$ TABLE 225 07-Jan-2013 16:22:42
SYSMAN MGMT_POLICY_ASSOC_EVAL_SUMM TABLE 376 07-Jan-2013 22:00:28
SYS WRM$_SNAPSHOT TABLE 400 07-Jan-2013 16:24:01
SYSMAN MGMT_TARGET_ROLLUP_TIMES TABLE 416 07-Jan-2013 22:00:30
SYS STATS_TARGET$ TABLE 655 06-Jan-2013 22:08:54
SYS WRH$_SQLTEXT TABLE 718 07-Jan-2013 16:23:38
SYSMAN MGMT_POLICY_ASSOC_EVAL_DETAILS TABLE 939 07-Jan-2013 22:00:29
SYS WRI$_ADV_OBJECTS TABLE 982 06-Jan-2013 14:08:09
SYS WRH$_SEG_STAT_OBJ TABLE 1045 07-Jan-2013 22:00:31
SYS WRI$_ADV_SQLT_RTN_PLAN TABLE 1479 01-Jan-2013 22:01:58
SYS WRI$_ADV_RATIONALE TABLE 1725 06-Jan-2013 06:00:39
SYSMAN MGMT_SYSTEM_PERFORMANCE_LOG TABLE 1959 07-Jan-2013 22:00:28
SYS SMON_SCN_TIME TABLE 2057 07-Jan-2013 16:22:42
SYSMAN MGMT_CURRENT_METRICS TABLE 2065 07-Jan-2013 22:00:29
SYS WRI$_ADV_TASKS TABLE 2185 07-Jan-2013 16:23:58
SYS WRI$_ADV_ADDM_FDG TABLE 3153 06-Jan-2013 10:07:58
SYS WRI$_ADV_SQLT_PLANS TABLE 3293 04-Jan-2013 18:10:34
SYS COL_USAGE$ TABLE 4028 07-Jan-2013 22:00:30
SYSMAN MGMT_METRICS_1HOUR TABLE 15840 07-Jan-2013 16:22:22
SYS WRH$_SQL_PLAN TABLE 20490 07-Jan-2013 22:00:35
SYS AUD$ TABLE 50706 06-Jan-2013 06:01:00
SYSMAN MGMT_METRICS_RAW TABLE 63425 07-Jan-2013 16:22:24
SYS HISTGRM$ TABLE 93700 07-Jan-2013 16:24:02
SYS WRI$_ADV_PARAMETERS TABLE 142892 06-Jan-2013 10:08:53
Then, i use
- Code: Select all
SQL_GATHER_TABLE
EXEC DBMS_STATS.gather_table_stats('SYS', 'AQ$_ALERT_QT_L', estimate_percent => DBMS_STATS.auto_sample_size);
EXEC DBMS_STATS.gather_table_stats('SYS', 'MON_MODS$', estimate_percent => DBMS_STATS.auto_sample_size);
...
...
...
EXEC DBMS_STATS.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', estimate_percent => DBMS_STATS.auto_sample_size);
EXEC DBMS_STATS.gather_table_stats('SYS', 'HISTGRM$', estimate_percent => DBMS_STATS.auto_sample_size);
EXEC DBMS_STATS.gather_table_stats('SYS', 'WRI$_ADV_PARAMETERS', estimate_percent => DBMS_STATS.auto_sample_size);
to gather all the table stats. But, after one day. The table will becomes "stale" again !
I just want to ask if some of the sys or system table will always stale ?
thanks
