Cary Millsap wrote: >People seem to cloud the concepts of the pre-run estimated runtime of a >query (officially its "estimated cost") and the post-run operationally >measured runtime of a query. The problem with a CBO is that before a = >query >runs, it can only estimate a cost using a model. The model is not = >perfect >(although it's usually more perfect than the input--db and schema >statistics--than DBAs feed the model). > > I suspect that the model actually implements Dan Tow's "simplex method" optimization technique. I call it a "simplex method", because Dan's method is to create nodes (one for each table), interconnect them with join conditions and run along the graph until certain conditions reach the minimum value. Philosophically, it's very similar to the simplex method. Now, if only Dan was able to create a linear function f:R(n)->R that could be used for that purpose, it would be a real simplex method application. That, in turn, has a consequence of making parse calls excruciatingly painful and expensive. >Now, AFTER a query runs, it's easy to see whether the estimated cost was = >any >good or not. But who wants the CBO to run a query six ways before = >choosing a >plan?! > > That would be the trial and error method, frequently used by CBO optimizators of their own queries. >If I recall correctly, 10g has some operational memory built into the = >CBO >that allows the CBO to use operationally measured response times for a = >given >query as an input into computing the next plan. > > That would be a logical continuation of the trend which started in 9i with bind variable peeking and system statistics, to give the optimizer some kind of experience. Now, I am expecting my CBO to say to me in harsh voice something like "I'll be back", with the strong Austrian accent. If you are right about this, learning from the previous plan is the first step from the optimizer to to a plan terminator. >Mr. Sharman, care to comment? (And by the way, will I be seeing you in = >M=E5l=F8v >next week?) > > Are Scott Gosset or Tom Kyte members of this list? Tom Kyte's input is the reason why started following Usenet newsgroups again. Connor McDonald and Jonathan are both present there as well as cantankerous but extremely knowledgeable Mr. Howard Rogers. -- Mladen Gogala Oracle DBA Ext. 121 -- //www.freelists.org/webpage/oracle-l