Re: Automatic Stats gathering in 10
Date: Tue, 6 Jan 2009 18:13:09 -0800 (PST)
Message-ID: <335115.12080.qm_at_web82102.mail.mud.yahoo.com>
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
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 (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?
Rgds
Mark
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fuad Arshad
Sent: 07 January 2009 04:40
To: oracle-l_at_freelists.org
Subject: Automatic Stats gathering in 10
List,
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 .
--
http://www.freelists.org/webpage/oracle-l
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
--
http://www.freelists.org/webpage/oracle-l Received on Tue Jan 06 2009 - 20:13:09 CST