Wednesday 1 February 2012

Differences between GATHER STALE and GATHER AUTO

Purpose

To give an explanation of the differences between GATHER STALE and GATHER AUTO in the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_DATABASE_STATS 
procedures.

Scope and Application

Intended for anyone collecting stats via the dbms_stats package

Differences between GATHER STALE and GATHER AUTO [ID 228186.1]


This is brief note to add some clarification in the area of the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_DATABASE_STATS procedures.  The 'options' parameter of these two procedures allows you to provide further
specifications on which objects to gather statistics on. Two of the values that this parameter can take are 'GATHER STALE' and 'GATHER AUTO'.

Summary:

  • 'GATHER AUTO': Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. Oracle makes up its own mind based on its own criteria.
  • 'GATHER STALE': Oracle gathers on objects considered to be STALE. By default 10% of the rows need to change to trigger this.

Detail:

Oracle will gather statistics on objects which have statistics considered to be STALE. This is done by looking at the *_tab_modifications views. To the end user, this means that if more than 10% of the rows change, then statistics will automatically be gathered.

Oracle will gather automatically statistics on objects which currently have NO statistics (even if they have NO MONITORING set) *plus* existing objects with STALE statistics. Prior to Oracle 10g GATHER AUTO (just like GATHER STALE) required monitoring to be turned on for the objects which already have statistics. If monitoring is was not turned on, there was no way for Oracle to know which objects become stale. In 10g and above this is handled automatically.

When GATHER AUTO is specified in dbms_stats.gather_stats syntax, Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. Thus, Gather AUTO option can be used to allow Oracle to decide how much statistics to gather.

When GATHER STALE is specified, any other parameters specified will also be taken into account.

See:
OracleƂ® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 13 Managing Optimizer Statistics
Section 13.3.1.8 Determining Stale Statistics


References

NOTE:295249.1 - Automated Object Statistics in 10gNOTE:114671.1 - Gathering Statistics for the Cost Based Optimizer (Pre 10g)NOTE:102334.1 - How to Automate Change Based Statistic Gathering - Monitoring Tables (Pre 10g)NOTE:237901.1 - Gathering Schema or Database Statistics Automatically in 8i and 9i - ExamplesNOTE:1073120.1 - DBMS_STATS 'gather auto' and 'gather stale' and column histogramsNOTE:457666.1 - How to list the objects having stale statistics (Use dbms_stats.gather_schema_stats options=>'LIST STALE')

2 comments:

Unknown said...

A stale article, if you dip it in a good, warm, sunny smile, will go off better than a fresh one that you've scowled upon. see the link below for more info.


#stale
www.ufgop.org

Unknown said...

Hello! I simply wish to give a huge thumbs up for the great data you have right here on this post. I will likely be coming back to your weblog for more soon. gsn casino games