Re: Automatic Stats gathering in 10

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Wed, 7 Jan 2009 22:11:39 +0100
Message-Id: <D87CF830-8D60-4E0D-8B33-F9A624525ED5_at_gmail.com>

please check:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm 14.2.1 GATHER_STATS_JOB Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:

• Missing statistics
• Stale statistics

=> do you have valid and good statistics on most of your tables?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41884 14.3.1.5 Determining Stale Statistics

Statistics must be regularly gathered on database objects as those database objects are modified over time. In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is set to GATHER STALE or GATHER AUTO. If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.

You can check DBA_TABLES. NUM_ROWS against DBA_TAB_MODIFICATIONS to get an estimation if a table hits the 10% boundary. Only if it's beyond it's worth to check why the automatic stats gathering is not working as you expect.

hth
  Martin

Am 06.01.2009 um 21:40 schrieb Fuad Arshad:

> 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
>
>

--
http://www.freelists.org/webpage/oracle-l




application/pkcs7-signature attachment: smime.p7s

Received on Wed Jan 07 2009 - 15:11:39 CST