Automatic Stats gathering in 10

  • From: Fuad Arshad <fuadar@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 6 Jan 2009 12:40:01 -0800 (PST)

I'm trying to pin down why oracle is not automatically gatherign statisticso n 
some of my tables.
The problem that i'm seeing goes like this 

there are about 1000+ tables partitioned and non partitioned.

automatic stats gathering job is enabled with mostly default values.
Job has been running every day in weeknight & weekend maintainence windows . 
everything looks good.
It  does gather stats on some tables  but not all of the potiential 10% stale 
category tables.
The job completes successfully in about 30-40 minutes so it is within the 8 
hour window.
The tables that are not being analyzed do not have their statistics  locked.

example Table was analyzed in aug and the sample size was 15 millions rows .
now all_tab_modification shows 37099459 inserts and 9132018 updates which 
should be considered a candidate for  stats gathering.
dba_tab_statistics for this particular table shows that stattype_locked is null 
and stale_stats=YES

Just trying to see whati  should be looking for . This is a non RAC instance .


Other related posts: