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