Re: Should we stop analyzing?

This makes Oracle's position with 10g interesting, given that the default behaviour is to collect statistics all over the place automatically. If it's built in by the supplier, does it count as a change ?

Jared's point is valid - in theory, if you keep statistics up to date, then the CBO should produce the optimum plan; if you fail to keep statistics up to date, the CBO plans can cease to be optimal, or may change to become sub-optimal. Moreover, in theory, if a plan changes on a change of statistics, it will be a better, or at worst equal cost, plan with at worst no change in performance. Of course, in the real world, we know that there are various reasons why things go wrong at the boundary points between plans, which is why we like to stick the statistics down well within our preferred boundary.

Of course, following your argument about change control to its logical conclusion, since a change in the data may change execution plans, which may introduce untested portions of Oracle code, any data change should also be subject to change control.

Despite any whimsical arguments, though, your basic premise is the important one. You need to know the application to do the job correctly. If you know how the data evolves, you will know how to get the minimum amount of work done that allows the optimizer to do its job well.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 30, 2003 11:44 PM

>
> 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: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).