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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply