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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/22/2002 10:57 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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):
1) 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
