Re: Which plan is better - what COST really means ?

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


--
http://www.freelists.org/webpage/oracle-l