Differences between GATHER STALE and GATHER AUTO [ID 228186.1]

 

Differences between GATHER STALE and GATHER AUTO

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.

Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!