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

Syed:

To have a look how CBO decide, which plan to choose, you can trace event 10053 at level 1. This will give you details of selection process.

Thanks
-Hairsh Kalra

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
>
>
>
Received on Tue Jan 03 2006 - 06:47:47 CST