FreeLists / oracle-l / Re: Why does Optimizer relys on COST to produce the execution plan?

[Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>]

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
www.centrexcc.com 

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