This forum is currently locked. You can't register or post questions at this time. (read more)

Stale Table in Oracle 11gR2

All posts relating to Oracle database administration.

Moderator: Tim...

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Stale Table in Oracle 11gR2

Postby peterx » Tue Jan 08, 2013 1:39 am

hi Tim,

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Stale Table in Oracle 11gR2

Postby Tim... » Tue Jan 08, 2013 8:56 am

Hi.

By default, stale means more than 10% of the rows have been modified (insert, update or delete). If a table is small, a change in a couple of rows will get it marked as stale.

Many of the system tables are constantly being changed, so it is not unlikely they will be stale on a regular basis. You can check the number of changes in the dba_tab_modifications view. This shows all the stats for the changes. A table is considered stale based on these stats.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Stale Table in Oracle 11gR2

Postby peterx » Tue Jan 08, 2013 9:09 am

hi Tim,

very thanks. Got it...., and learn it...
Seems, i can also take "the number of changes in the dba_tab_modifications" for reference.

thanks a lot.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Stale Table in Oracle 11gR2

Postby Tim... » Tue Jan 08, 2013 3:06 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 7 guests