Re: Should we stop analyzing?

At 03:29 PM 12/30/2003, you wrote:
>But then again, if re-collecting statistics causes your database performance
>to suddenly become very bad, it seems at first cut there are only two
>conclusions
>you can come to.
>
>1) CBO is broke if fresh statistics result in poor performance

That a plan changes due to changes in the statistics doesn't mean that the CBO is broke. That's the whole name of the game. The optimizer uses statistics - together with initialization parameters, heuristics and rules - to develop the anticipated best access path. If you change any of these, statistics by analyzing, initialization parameters by changes to the init.ora, or heuristics and rule by upgrading to a new version or applying a patch. I regard any of these changes as serious changes to the database which should go through a test and acceptance cycle. And that includes refreshing statistics. I am constantly amazed how nonchalantly most shops schedule daily, weekly, or whatever analyze jobs even if they batten down the hatches against changes to the application (Don Burleson alluded to that as well). Most of the time the changed statistics do not cause a change in access plans ( which immediately begs the question why do it then ), but ever so often the changed statistics cross a threshold to make a different plan appears to be better. It may be better, or it may turn out to be horrible. My point is: shouldn't that be tested first?

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).