Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

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

From: The Human Fly <>
Date: 2006-01-02 07:32:47

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 - 07:32:47 CST

Original text of this message