Re: Burned by DBMS_STATS **AGAIN**

What are the differences in the statistics between analyzed and "dbms_statsed" ?
AFAIK an analyze collects size 1 histograms (lo and high values) for all columns, so it should be equivalent to "for all columns size 1". The table is not by any chance partitioned?

At 12:16 PM 4/7/2003 -0800, you wrote:
>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:
>
>1) The 3rd-party app created a new 8.1.7.2.0 DB and imported the
>8.1.6.0.0'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
>
>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).

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).