X-list: oracle-l Return-Path: Subject: Re: Why does Optimizer relys on COST to produce the execution plan? From: The Human Fly Message-id: 97b7fd2f0601020417k1d6f8b6bpf5045085109040fe@mail.gmail.com Date: 2006-01-02 13:17:05 Hi Christian, >>> The model cannot rely on runtime statistics because the query has not run yet. This seam obvious. Therefore the model can only estimate the work that has to be done to execute the query. > You are absolutely right. It just skipped from my mind. Thank you. I will have a try once I enable the system level statistics on the database. On 1/2/06, Christian Antognini wrote: > Jaffar > > >I have not enabled the system level statistics. > > Then it's your fault ;-) > > >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'? > > The model cannot rely on runtime statistics because the query has not run yet. This seam \ obvious. Therefore the model can only estimate the work that has to be done to execute the \ query. > > Historically the cost is based on: > - the number of I/O performed to execute an execution plan > - some INIT.ORA parameters > > If you give a look to the figures of your query, the cost of the index scan and the LIO \ performed to execute it are quite close. The problem, probably, is that the FTS are too \ highly cost. (db_file_multiblock_read_count too large?) > > >I dont know whether the behaviour might change once we get the system > >level stats. > > With system statistics the model changes, therefore the behavior may change as well. In \ your case, I guess, the FTS will be more expensive. > > >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? > > http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/apc2.htm > > > > HTH > Chris > -- 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