RE: Which plan is better - what COST really means ?

I disagree with your assertion 2. Even with cpu_costing enabled, the CBO's 
cost measure is still the estimated # of single block reads. Multiblock 
reads and cpu consumption are converted to single block read "currency". 
See below

At 08:03 AM 1/16/2005, J.Velikanovs@xxxxxxxx wrote:
>======================================================================
>GRANT TOTAL:
>COST measured in SINGLE BLOCK PHYSICAL IO UNITS
>COST represents
>1. Before 9i (9i as well if cpu_costing feature doesn't used)
>IO OPERATIONS COUNT.
>2. Starting from (9i if cpu_costing feature is used)
>TIME to execute SQL.
>======================================================================
>
>2. If you use cpu_costing
>Taken from the article:
>Cost = (
>                 #SRds * sreadtim +
>                 #MRds * mreadtim +
>                 #CPUCycles / cpuspeed
>           ) / sreadtim

This is also published in the Oracle 9i Performance Tuning Guide and 
Reference. Applying basic math you get

cost = #SRds
        + #MRds * mreadtim/sreadtim
        + #CPUCycles / cpuspeed / sreadtim

i.e. cost is still expressed as a count of single block reads. Multiblock 
reads are now costed separately but expressed in single block read 
equivalents by multiplying them with the ration mreadtim/sreadtim (kind of 
the reciprocal of optimizer_index_cost_adj). Note that if mreadtim happens 
to be less than sreadtim, by accident when gathering or by deliberately 
setting it, that doesn't mean that multiblock reads are costed cheaper than 
single block reads. Instead, cpu_costing is turned off and costing reverts 
to the pre-9i formulas. That cpu_cost is expressed in single block IO 
currency is not as directly derivable from the formula but follows from the 
fact that the other two components are and that you can't add apples and 
oranges ( unless you want to make fruit salad).


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: