Good point about the "change-control" issue.
When we consider that re-analyzing stats can cause huge changes to data access patterns I�m continuously amazed at the number of shops that re-analyze on a schedule and have the �Monday Morning� syndrome.
I have worked for shops where they must �certify� every change, no matter how trivial. Mostly banks and medical systems.
These �certified� shops are stuck. On one hand, they are obligated to follow the best-practices of their vendor, yet obligated not to make any untested changes in production.
Even Oracle is schizophrenic on the issue; my contacts in the real-world performance group are zealously in favor of the �take one deep sample� approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics.
Personally, I love the automatic histogram generation �skewonly� and the �auto� option in dbms_stats, and use it for all my 9ir2 clients.
However, I remain skeptical about the benefits of �dynamic sampling� and �workload analysis� automation tools for most shops.
In my experience, the vast majority of shops DO NOT benefit from re-analysis, and I�ve got shops where re-analysis NEVER results in CBO changes.
Regards,
> 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" <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
>
> 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: [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).
>
