Jack, 7) GATHER_STALE
We just discussed this, is the MONITOR option set for all your tables? So that DBA_TAB_MODIFICATIONS is populated and then DBMS_STATS can determine if stats are stale. I know there is an article in Metalink that speaks to this. -----Original Message----- Sent: Wednesday, May 22, 2002 1:58 PM To: Multiple recipients of list ORACLE-L 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: Grabowy, Chris 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).
