Re: Automatic Stats gathering in 10
- From: Fuad Arshad <fuadar@xxxxxxxxx>
- To: "Teehan, Mark" <mark.teehan@xxxxxxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- 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@xxxxxxxxxxxxxxxxx>
To: fuadar@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Fuad Arshad
Sent: 07 January 2009 04:40
To: oracle-l@xxxxxxxxxxxxx
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 .
--
//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
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
