GATHER AUTO overrides preferences

If you use DBMS_STATS.GATHER_SCHEMA_STATS or DBMS_STATS.GATHER_DATABASE_STATS with options=> ‘GATHER AUTO’ then the preferences for the tables whose statistics are gathered are ignored.

See this testcase which includes a SQL*Plus script and log demonstrating that GATHER_SCHEMA_STATS  ignores a preference.  I’ve seen the same thing with GATHER_DATABASE_STATS but it wasn’t as quick to build a test case.

So, if you depend on table preferences don’t use GATHER AUTO.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply