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
--
http://www.freelists.org/webpage/oracle-l