Automated Optimizer Statistics

[Jeff Bisch]

Hello,

 

  I am running 11.1.0.7.0 on Windows 2008 64 bit, and I have a question concerning the Automated Maintenance Tasks.  More specifically, my question is about the Optimizer Statistics Gathering job.

 

  The job is set to run at 10 PM each evening, and I have left the configuration intact, meaning that the job is set to run with the defaults as originally set by Oracle.  The job executes each night as designed.  I have noticed that when reviewing tables for any of the schemas in the database, that the "Last Analyzed" column is only updated for a few tables.  My suspicions tell me that the job is only gathering stats on tables that have had their content changed.  I have not found any literature to support this theory, thus my question.

 

  Does the Automated Maintenance Tasks - Optimizer Statistics Gathering job, only gather stats on tables where the data has changed?

 

Thanks in advance for your help,

 

  Jeff

[ddf]

There are two 'standard' windows for this job, one is a weekday window
which starts at 10pm and closes at 6am with the condition that the job
stops at the time the window closes. There is also a weekend window,
starting at 6AM on Saturday and running, essentially, until 6am on
Monday morning. The job is set to GATHER AUTO, which gathers stale
and empty statistics. The behaviour you're seeing is due to the
window closing at 6am on weekdays; the job finishes the statistics
that are running but starts no more objects, leaving the remainder to
be worked on at the next window. The weekend window allows the stats
job to 'catch up', if you will, by opening a 48 hour window during the
usual 'off hours'.

You can use the following query to return objects queued for
processing by the automatic statistics job:

set serveroutput on size 1000000 verify off
--
-- Auto stats candidates
--
declare
m_object_list dbms_stats.objecttab;
begin

dbms_stats.gather_schema_stats(
ownname => upper('&schema'),
options => 'LIST AUTO',
objlist => m_object_list
);

dbms_output.put_line(chr(10)||'GATHER AUTO will analyze '||
m_object_list.count||' objects'||chr(10));
dbms_output.put_line('OWNER TYPE OBJECT
NAME
PARTITION
SUBPARTITION CONF');
dbms_output.put_line('========= =====
=======================================
========================================
====================================== ====');
for i in 1..m_object_list.count loop
dbms_output.put_line(
rpad(m_object_list(i).ownname,10) ||
rpad(m_object_list(i).objtype, 6) ||
rpad(m_object_list(i).objname,40) ||
rpad(nvl(m_object_list(i).partname, ' '),40) ||
rpad(nvl(m_object_list(i).subpartname,' '),40) ||
lpad(m_object_list(i).confidence,4)
);
end loop;
end;
/

This will run in 10gR2 and earlier releases; for 11g the confidence
'column' no longer exists so that can either be removed from the query
or replaced with an optimiztic '100' to indicate the likelihood of
having statistics gathered on the object.


David Fitzjarrell

[jgar the jorrible]

On Oct 5, 3:14 am, Jeff Bisch <jbis...@yahoo.com> wrote:

It only gathers on those that have no statistics or are stale, where
stale is defined as 10% changed:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats.htm#i41282
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats.htm#i41884

So this leaves you open to sudden plan changes as statistics edge
conditions are reached for particular queries. The changes can be
good or bad, depending on how accurately optimizer usage of the
statistics reflect reality. People tend to only notice the bad. You
might want to google how to use baselines to stabilize a system that
is running well.
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf
is pretty informative.

jg
--
@home.com is bogus.
http://www.theinquirer.net/inquirer/news/2114845/oracle-closes-conference-doors-salesforce-cloud-talk

[ddf]

On Oct 5, 8:59 am, jgar the jorrible <joel-ga...@home.com> wrote:
> On Oct 5, 3:14 am, Jeff Bisch <jbis...@yahoo.com> wrote:
>
> > Hello,
> >  
> >   I am running 11.1.0.7.0 on Windows 2008 64 bit, and I have a question concerning the Automated Maintenance Tasks.  More specifically, my question is about the Optimizer Statistics Gathering job.
> >  
> >   The job is set to run at 10 PM each evening, and I have left the configuration intact, meaning that the job is set to run with the defaults as originally set by Oracle.  The job executes each night as designed.  I have noticed that when reviewing tables for any of the schemas in the database, that the "Last Analyzed" column is only updated for a few tables.  My suspicions tell me that the job is only gathering stats on tables that have had their content changed.  I have not found any literature to support this theory, thus my question.
> >  
> >   Does the Automated Maintenance Tasks - Optimizer Statistics Gathering job, only gather stats on tables where the data has changed?
> >  
> > Thanks in advance for your help,
> >  
> >   Jeff
>
> It only gathers on those that have no statistics or are stale, where

> stale is defined as 10% changed:http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats....http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats....

>
> So this leaves you open to sudden plan changes as statistics edge
> conditions are reached for particular queries.  The changes can be
> good or bad, depending on how accurately optimizer usage of the
> statistics reflect reality.  People tend to only notice the bad.  You
> might want to google how to use baselines to stabilize a system that

> is running well.http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehou...

> is pretty informative.
>
> jg
> --

> @home.com is bogus.http://www.theinquirer.net/inquirer/news/2114845/oracle-closes-confer...

Up until 11.2 histogram data reflects the sample size, not the
'extrapolated' numbers views like DBA_TABLES and DBA_INDEXES report.
In 11.2 this was fixed. Another change in 11.2 is to default
extimate_percent to NULL to gather stats for the entire table/
partition; Oracle modified the stats gathering methodology to
eliminate a time-consuming sort operation which speeds the process
considerably over prior releases. I have noticed that plan changes of
the 'adverse' variety are fewer in 11.2 when compared to the same data
in 10.2 (partly due, I expect, to optimizer changes in 11.2 which
increase the number of options available for query execution and
partly due to the histogram fix). I can't speak for 11.1 as we jumped
from 10.2 straight to 11.2.


David Fitzjarrell