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

["Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>]

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
--
http://www.freelists.org/webpage/oracle-l