Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Why does Optimizer relys on COST to produce the execution plan?

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

From: Li-Shan Cheng <>
Date: 2006-01-02 09:27:09

Cost depends on tons of factors, parameter setting, object statistics, segment storage settings etc etc etc.

I am pretty sure J. Lewis can give you much more hints. Or you can get a copy of his book which is entirely about CBO behaviour.



On 1/2/06, 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.
> I have not enabled the system level statistics. I question is that,
> there is nothing related with cost value, then, why does oracle heavly
> rely on this value to produce the 'best execution plan'?
> I dont know whether the behaviour might change once we get the system
> level stats.
> 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?
> Thanks and once again a very happy new year.
> --
> Best Regards,
> Syed Jaffar Hussain
> OCP 8i & 9i DBA,
> Banque Saudi Fransi,
> Saudi Arabia
> ----------------------------------------------------------------------------------
> "Winners don't do different things. They do things differently."
> --
Received on Mon Jan 02 2006 - 09:27:09 CST

Original text of this message