Re: Automatic Stats gathering in 10

From: Fuad Arshad <>
Date: Tue, 6 Jan 2009 18:13:09 -0800 (PST)
Message-ID: <>

maintainence window is there and there are approx 400+ tables affected but about 20-30 tables are analyzed with the  autostats procedure . The stats for august are when we perofrmed an 10.2 upgrade from 9.2 and were gathered after the upgrade
  • Original Message ---- From: "Teehan, Mark" <> To:; Sent: Tuesday, January 6, 2009 7:55:38 PM Subject: RE: Automatic Stats gathering in 10

Hi Fuad,
Do you have a maintenance window defined : is it possible that the table can no longer be gathered inside the maintenance window? If this is the case, then the stats job will reinstate the stats that existed before the job began ( It also needs to complete the gather for all dependant indexes before stats gathering is marked complete.

Another possibility: interrupting a stats gather can cause subsequent automatic gather problems- a manual gather using gather_table_stats should fix this. Is it possible that a stats gather for this table returned an error in Aug?


-----Original Message-----

[] On Behalf Of Fuad Arshad Sent: 07 January 2009 04:40
Subject: Automatic Stats gathering in 10

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 .


Please access the attached hyperlink for an important electronic communications disclaimer:

-- Received on Tue Jan 06 2009 - 20:13:09 CST

Original text of this message