RE: silly dbms_stats question
Date: Fri, 24 Apr 2009 13:20:28 -0400
I believe the default setting is for STALE so the table needs to have at least 10% of its rows changed before it will analyze it, or stats must be older than 31 days.
From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Friday, April 24, 2009 1:18 PM
To: JSweetser_at_icat.com; jkstill_at_gmail.com; Crisler, Jon; oracle-l-freelists
Subject: Re: silly dbms_stats question
And also that this job doesn't collect stats on all tables, but only those that have changed significantly. So if you expect last_analysed to show when the stats job ran you'll be disappointed if your table wasn't analysed in that run.
On 4/24/09, Sweetser, Joe <JSweetser_at_icat.com> wrote:
> Indirectly related, don't forget that Oracle automatically gathers
> by default in 10g.
> Go here:
> To see (among other things) this:
> This job is created automatically at database creation time and is
> managed by the Scheduler. The Scheduler runs this job when the
> maintenance window is opened. By default, the maintenance window opens
> every night from 10 P.M. to 6 A.M. and all day on weekends.
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still
> Sent: Friday, April 24, 2009 10:35 AM
> To: Crisler, Jon
> Cc: oracle-l-freelists
> Subject: Re: silly dbms_stats question
> On Fri, Apr 24, 2009 at 9:29 AM, Crisler, Jon <Jon.Crisler_at_usi.com>
> My information was that dbms_stats does not update the
> last_analyzed column of dba_tables / user_tables, and this affected
> versions of 10g, but was fixed in 11g. However your test seems to
> contradict my information, but I have tested this many times and found
> it to be true, even on 10.2.0.3. I wonder if there is a patch that
> changed this behavior ?
> Is there a database you can duplicate it on?
> Possibly platform dependent?
> Our databases are all on Windows or Linux.
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
-- Sent from Google Mail for mobile | mobile.google.com Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 24 2009 - 12:20:28 CDT