FreeLists / oracle-l / Re: Why does Optimizer relys on COST to produce the execution plan?

[The Human Fly <sjaffarhussain@xxxxxxxxx>]

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 <Christian.Antognini@xxxxxxxxxxxx> 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