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

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