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

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.

regards

--
LSC





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

> http://jaffardba.blogspot.com/

>

> ----------------------------------------------------------------------------------

> "Winners don't do different things. They do things differently."

> --

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

>

>

>