Oracle-L: RE: Burned by DBMS

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Mon, 07 Apr 2003 12:38:44 -0800
Message-ID: <F001.0057C3BE.20030407123844@fatcity.com>

Jesse,

I was perusing some notes on DBMS_STATS on MetaLink last month because we wanted to start using it regularly. A simple search for GATHER_STALE will help you find them. Anyhow, just about every note I read stated three basic items to do to get this to work right:

analyze the tables manually with the analyze command.
enable monitoring
use dbms_stats.gather_schema_stats with the "GATHER_STALE" option.

It seems to work for us, now.

Dick Goulet

-----Original Message-----
Sent: Monday, April 07, 2003 4:17 PM
To: Multiple recipients of list ORACLE-L

Hey all,

Just upgraded a 3rd-party app's DB from 8.1.6.0.0 to 32-bit 8.1.7.2.0 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:

The 3rd-party app created a new 8.1.7.2.0 DB and imported the 8.1.6.0.0's full export.
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.
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.
Test DB with slightly less data had the "good" explain plan, so I exported stats using DBMS_STATS.EXPORT_SCHEMA_STATS.
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);
The explain plan is now hosed with the FTS/hash inside a 7-level NL.
Re-wipe all stats as before.
Run ANALYZE TABLE CHARLIE_FOXTROT.tables COMPUTE STATISTICS for all tables in the schema.
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

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Goulet, Dick
INET: DGoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

RE: Burned by DBMS_STATS **AGAIN**

RE: Burned by DBMS_STATS AGAIN