Oracle-L: Re: dbms_stats issues on 8i

From: <Jared.Still_at_radisys.com>
Date: Wed, 22 May 2002 11:00:52 -0800
Message-ID: <F001.00468209.20020522110052@fatcity.com>

So Jack, if I'm reading between the lines correctly, you find no reason to use DBMS_STATS prior to 9i?

My 8i databases are purrfectly happy with ANALYZE, and I'm loathe to change something (that works) just to use the latest and greatest. I don't like hemorrhaging out on the bleeding edge.

Jared

Jack Silvey <jack_silvey_at_yahoo.com>
Sent by: root_at_fatcity.com
05/22/2002 10:57 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        dbms_stats issues on 8i

Hello fellow orabrains,

Although Oracle has made it clear that DBMS_STATS is the future and that ANALYZE will be desupported, and it seems to work well in 9i, here are some things that I uncovered in my research of it during our initial stats process implementation (we were on 8.1.6 during this time):

GATHER_DATABASE_STATS gathers stats on SYS schema.

Also creates possible deadlock scenario that terminates the process with DD stats half gathered, and the workaround is to delete the SYS stats.

2) ANALYZE_SCHEMA does not work

I found this to be true in our warehouse. Fixed in 9i.

3) ANALYZE_SCHEMA does not gather stats on all tables

Workaround is to analyze those tables manually (er, um,.....never mind)

5) DBMS_STATS does not gather all stats

Gathers only stats relevant to CBO, such as num_rows, not those such as empty_blocks. Not really relevant, necessarily.

6) PARALLEL clause does not work unless you specify FOR ALL COLUMNS SIZE x

7) GATHER_STALE clause does not gather stale stats

Also found this to be true, which is why I wrote a looping analyze procedure.

8) Would not analyze our partitioned fact table at the top level, no matter what I tried. Waited 9 hours, restarted, and waited another 6. Used parallel degree 24 and still no good. Gave it up and used analyze which did each partition in ~3-5 minutes.

9) Cannot use ANALYZE after dbms_stats.

DBMS_STATS sets GLOBAL_FLAG to "Y", which prevents ANALYZE from storing stats for that table. Workaround is to delete stats with DBMS_STATS.DELETE_TABLE_STATS and then use ANALYZE.

There are others, like the DESC index problem that I did not research, just noted in passing.

/jack silvey

Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--

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

Author: Jack Silvey
INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------