Re: Automatic Stats gathering in 10
Date: Tue, 6 Jan 2009 18:13:09 -0800 (PST)
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" <mark.teehan_at_credit-suisse.com> To: fuadar_at_yahoo.com; oracle-l_at_freelists.org Sent: Tuesday, January 6, 2009 7:55:38 PM Subject: RE: Automatic Stats gathering in 10
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 (10.2.0.4). 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?
[mailto:oracle-l-bounce_at_freelists.org] 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
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:
http://www.freelists.org/webpage/oracle-l Received on Tue Jan 06 2009 - 20:13:09 CST