GATHER AUTO != GATHER EMPTY + GATHER STALE

GATHER AUTO, GATHER EMPTY and GATHER STALE are the possible values of a parameter called OPTIONS in the DBMS_STATS.GATHER_DATABASE_STATS and DBMS_STATS.GATHER_SCHEMA_STATS procuedures. Their meanings have acutally been documented well. From the doc:

GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.

GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.

GATHER EMPTY: Gathers statistics on objects which currently have no statistics. also, return a list of objects found to have no statistics.

However DBA made a mistake. In a 10.2.0.4 production database, we execute following statement every four hour:

DBMS_STATS.gather_database_stats( ESTIMATE_PERCENT => 30, CASCADE=>TRUE,
OPTIONS=> 'GATHER AUTO', DEGREE=> 6, METHOD_OPT=> 'FOR ALL COLUMNS SIZE 1');

Now I understand with OPTIONS=> 'GATHER AUTO' in this statement, Oracle just ignores other settings. The results are Oracle uses parallel degree 32 (we have 16 CPUs) and Oracle collects histograms for some columns, which are certainly not our intentions. The problem becomes visible due to the execution of this statement causing CPU spike up to 100% in a short period of time (Normal CPU utilization ~10%) . Monitoring tool captures this spike and in turn causes upset at the management layer. In coincidence with an application problem occurred yesterday, the spike was pointed by every finger, although it has been there for quite a long time and no hard evidence it caused any problems. DBA now changed 'GATHER AUTO' to 'GATHER STALE'.