GATHER AUTO vs GATHER STALE in Oracle 11.2


When using DBMS_STATS to gather statistics, we need a method to choose which objects to gather statistics for. This is done by the parameter “OPTIONS”. The possible values for this parameter are

  • GATHER
  • GATHER AUTO
  • GATHER STALE
  • GATHER EMPTY
  • LIST AUTO
  • LIST EMPTY
  • LIST STALE

Of the above, GATHER AUTO & GATHER STALE confuse many – let’s see what’s the main difference between the two.

GATHER STALE: Oracle gathers statistics on stale objects as determined by looking at the *_tab_modifications views. The estimate percent & degree have to be provided by the user.

GATHER AUTO : Oracle decides which objects to gather fresh statistics for and also decides how to get them. Oracle determines the estimate percent to be used. The parallelism used is based on the init.ora setting. This also used *_TAB_MODIFICATIONS to decide on the eligible candidates.

FootNote:

In 12c, Oracle has done away with most of these and provides only GATHER and GATHER AUTO

Reference:

https://docs.oracle.com/database/121/ARPLS/toc.htm

http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#CIHBIEII

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s