| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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?
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/
 |
 |