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 <sjaffarhussain@xxxxxxxxx> 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." > -- > //www.freelists.org/webpage/oracle-l > > >