Re: Why does Optimizer relys on COST to produce the execution plan?

The optimizer needs some criterion to decide which plan to choose for a SQL. The RBO uses a sequence of rules and chooses the first rule in the sequence that applies (with some tie-breaking rules). The CBO uses an estimate of the resources needed to process each plan. This resource estimate is the "cost". In the absence of system statistics that cost is the estimate of logical IO that it will take to get the result set. With system statistics, the cpu cost of the individual plan steps weighs in as well.
The "cost", i.e. estimate of LIO required, is based on the underlying object statistics, the predicates and some built-in assumptions/heuristics. It is my firm conviction that the optimizer chooses the best plan possible IF it gets the estimates right. When you force a particular access path through hints you distort the meaning of the cost. Of course, the hinted plan will have a higher cost or else the CBO would have chosen it in the first place. The question to ask is not what other measure the CBO should use to base its decision on, but what led it to miscalculate the LIO "costs" of the plans. There are several possibilities: a) The cost of an FTS was artificially low due to a high db_file_multiblock_read_count.
b) The cost of an FTS was undervalued because of the missing cpu component. As you observed (and as Jonathan showed in his book), the cpu cost of an FTS can be substantial.
c) The cost of the index access was overvalued because of an incorrect rowcount estimate. Maybe a histogram on the predicate column might help
...
Find - and fix - the cause for the optimizer's wrong estimate and the optimizer will choose the correct plan without a hint.

At 11:32 PM 1/1/2006, The Human Fly wrote:
>Hello list,
>
>Wishing you a very happy and prosperous new year.
>
>Yesterday, I was happend to tune a query and got surprised the way
>Optimizer behaves. I think oracle should reconsidered about the
>Optimizer behaviour, which relys on cost value, as of 9207, to produce
>the explain plan. Initially when I run the query, oracle was doing
>FTS of two tables and response time was 0.08, there is a composite
>index and the column which I am using in the query is the leading
>column in the composite index. I thought Optimizer would choose INDEX
>SKIP SCAN, when I force to using the composite index, the query
>response time also was 0.08. But, the cost between the two explain
>plans are double. The query which was does FTS, cost was 1040 and the
>query which was using INDEX hint cost 3564. But, the big difference
>was logical reads. Query with FTS doing 10 thousand logical reads and
>query with INDEX hint was doing 3thousand logical reads. The
>difference is 7 thousand logical reads. I have also compared the CPU
>used by these query and the difference was around 70% between these
>two queries.

Why an index skip scan if the leading column is in the predicates? Unless, of course, it's not an equal predicate.

[snip]

>By the way, how do we count the value coming from v$sysstat for CPU
>used by this session'? How do I calculate this value? Is this CPU
>cycles or what?

That value is centiseconds.

Regards

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

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