dbms_stats issues on 8i

From: Jack Silvey <jack_silvey_at_YAHOO.COM>
Date: Wed, 22 May 2002 09:57:37 -0800
Message-ID: <F001.00467F22.20020522095737@fatcity.com>

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: 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). Received on Wed May 22 2002 - 12:57:37 CDT