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

Some context may get lost because I have to cut most of the quoting.

The topic of this thread is "what COST really means" and then Karen brought 
up the question why she does not observe the desired correlation between 
cost and execution time. I was attempting to answer that question by 
pointing out some of the reasons why the optimizer's "cost" calculations 
may be off.
In its attempt to find the best plan the optimizer needs some criterion to 
rank the different plans. The RBO used rules, the CBO uses cost expressed 
in the currency of block IO. How long it takes and how difficult that is 
wasn't part of the discussion.
Lastly, I beg to disagree. Violation of the the optimizer's assumption of 
uniformity of data distribution can cause great havoc with the CBO's 
costing attempts. And in reality, the uniform distribution assumption and 
predicate independence assumption are very often violated. A lot of the 
problems can be avoided or mitigated by good design but if you have a 
system that is built "database agnostic", i.e. which treats the database as 
a big data dump, that's where the CBO really struggles because it has no 
guidance about the data structures due to generic design nor form the 
statistics due to its assumptions being at odds with reality.

At 11:00 PM 1/14/2005, you wrote:

>The units of the "cost" are like monopoly money. They  help CBO to pick
>the best strategy, but other then that, CBO cost has no meaning
>whatsoever.

Jonathan's point is exactly that the cost is not an arbitrary currency like 
"monopoly money" but has by design a real correlation to the expected 
execution time - or at least ought to. I was trying to explain why it 
sometimes (still too often) misses the mark.

>Mladen Gogala
>Oracle DBA


Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com 

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

Other related posts: