Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Burned by DBMS_STATS **AGAIN**

Burned by DBMS_STATS **AGAIN**

From: Jesse, Rich <>
Date: Mon, 07 Apr 2003 12:16:44 -0800
Message-ID: <>

Hey all,

Just upgraded a 3rd-party app's DB from to 32-bit on 64-bit Solaris. The Oracle install was not standard -- it was "installed"
(i.e. files just copied to $ORACLE_HOME) by the 3rd-party installer, and
therefore, not patchable with the Oracle Installer. I was using DBMS_STATS successfully on 8.1.6 and am trying to continue using it, but I'm being thrown for a loop. Here's the highly-abbreviated scenario:

  1. The 3rd-party app created a new DB and imported the's full export.
  2. Re-ran a full stats gather using an error-trapped procedure that calles the slightly buggy DBMS_STATS.GATHER_SCHEMA_STATS for the app's schema.
  3. One particularly icky SQL now runs with a FTS on a 250K row table as the inner hash to a 7-level NL. The previous DB used index lookups on all tables in an 8-level deep NL. The old plan executed in a few seconds; the new one takes minutes.
  4. Test DB with slightly less data had the "good" explain plan, so I exported stats using DBMS_STATS.EXPORT_SCHEMA_STATS.
  5. After deleting all stats in test DB using both ANALYZE...DELETE... and DBMS_STATS.DELETE_SCHEMA_STATS and verifying that DBA_TAB_HISTOGRAMS, DBA_TABLES, and DBA_INDEXES had no stats, I ran this: dbms_stats.gather_table_stats ( ownname => 'CHARLIE_FOXTROT', tabname => cursor_looped.table_name, estimate_percent => NULL, block_sample => FALSE, method_opt => 'FOR ALL COLUMNS SIZE 1', degree => 1, granularity => 'DEFAULT', cascade => TRUE, stattab => NULL, statid => NULL, statown => NULL);
  6. The explain plan is now hosed with the FTS/hash inside a 7-level NL.
  7. Re-wipe all stats as before.
  8. Run ANALYZE TABLE CHARLIE_FOXTROT.tables COMPUTE STATISTICS for all tables in the schema.
  9. Explain plan is now the "good" one with all indexes and no FTS.

As I've done in past headaches when using DBMS_STATS, I checked the DBA_TAB_HISTOGRAMS view and get different counts for the ANALYZE than I do for using DBMS_STATS with a METHOD_OPT of either "FOR ALL COLUMNS SIZE 1" or "FOR INDEXED COLUMNS SIZE 1". I have verified that the ANALYZE uses "SIZE 1", but not the rest of the method_opt (which columns).

So, back and forth I go again -- ANALYZE appears to work better than DBMS_STATS at gathering more accurate object statistics. This leaves me with questions:

What method opt is the default for ANALYZE? Where the hell is this documented? It's not in the SQL Ref manual. I've found it once before but can't seem to recall where (see thread "Defaults for ANALYZE" on 03/04/2002).
Should I bother with DBMS_STATS, sacrificing gathering performance and reporting for "correct" stats?
If, as Oracle reports, DBMS_STATS is generating correct stats and ANALYZE isn't, what am I doing wrong?
Should I finish off the Pete's Wicked Ale tonite or go with the Sierra Nevada Pale Ale?

Frustratedly yours,

Rich Jesse                        System/Database Administrator           Quad/Tech International, Sussex, WI USA

Please see the official ORACLE-L FAQ:
Author: Jesse, Rich

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Apr 07 2003 - 15:16:44 CDT

Original text of this message