RE: DBMS_STATS.gather_database_stats

Walter,

You might want to check the following Bug and ML note. I covered this in my recent paper on the CBO at IOUG - cut and paste below:

MYTH: "ANALYZE THE ENTIRE DATABASE, INCLUDING SYS" This is usually the result of an over-enthusiastic move to the CBO. The internal Data dictionary (mostly owned by SYS) is heavily optimized for the RBO, a carry over from the days of Oracle 6 when RBO was the only child in the family. Many Data dictionary views are hinted by RULE, but some are not. Messing around with them is not good for the health of the Database! On a more serious note, Database deadlocks have been known to occur when analyzing the SYS schema as rows being inserted into the Histogram-related internal tables lock themselves out. For further details, refer to Metalink Note 35272.1.
An interesting side note to this myth is the hidden issue with the DBMS_UTILITY.ANALYZE_DATABASE procedure. Invocation of this in-built package/procedure used to generate statistics for all users, including SYS. Bug 969814, released as late as 8.1.7, fixes ANALYZE_DATABASE so it does not analyze the dictionary tables FET$ and UET$.

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Walter K [mailto:ORA1034_at_sbcglobal.net]
> Sent: Thursday, April 25, 2002 11:46 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: DBMS_STATS.gather_database_stats
>
>
> I can't answer the question about the performance
> after deleting the statistics because we have a
> contract DBA here that insists it's okay to analyze
> the internal schemas even though he's the one that
> acknowledges the performance problem if the stats get
> a little stale! Unfortunately, he always gets his way,
> right or wrong...<sigh>...
>
> --- Original Message ---
> To: Multiple recipients of list ORACLE-L <ORACLE-
> L_at_fatcity.com>
>
> >Walter,
> >this is actually a bug. It's supposedly fixed in 9i
> though I haven't tried
> >it. Anybody?
> >Do you still see a performance hit when you delete
> the statistics from the
> >SYS objects?
> >
> >Regards,
> >Mike Hately
> >
> >-----Original Message-----
> >Sent: 25 April 2002 15:33
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >If you're not supposed to analyze SYS and SYSTEM then
> >can anyone explain why the
> >DBMS_STATS.GATHER_DATABASE_STATS procedure does?
> >
> >We have run into scenarios where the data dictionary
> >becomes almost unusable until SYS gets analyzed again
> >via this procedure. I.e. can't describe v$ views,
> >selects against v$ views take forever to return
> >results, etc.
> >
> >The obvious solution is to not use this particular
> >procedure but it still begs the question WHY doesn't
> >it exclude SYS and SYSTEM? Are there any reasons why
> >you would want to analyze these schemas?
> >
> >Thanks.
> >-w
> >
> >
> >
> >
> >______________________________________________________
> ______________________
> >________________________
> >
> >This email and any attached to it are confidential
> and intended only for the
> >individual or
> >entity to which it is addressed. If you are not the
> intended recipient,
> >please let us know
> >by telephoning or emailing the sender. You should
> also delete the email and
> >any attachment
> >from your systems and should not copy the email or
> any attachment or
> >disclose their content
> >to any other person or entity. The views expressed
> here are not necessarily
> >those of
> >Churchill Insurance Group plc or its affiliates or
> subsidiaries. Thank you.
> >Churchill Insurance Group plc. Company Registration
> Number - 2280426.
> >England.
> >Registered Office: Churchill Court, Westmoreland
> Road, Bromley, Kent BR1
> >1DP.
> >
> >
> >--
> >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >--
> >Author: Hately Mike
> > INET: Mike.Hately_at_churchill.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: Walter K
> INET: ORA1034_at_sbcglobal.net
>
> 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: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).