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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: J.Velikanovs@xxxxxxxx
  • Date: Sun, 16 Jan 2005 09:43:25 -0700

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:
>COST represents
>1. Before 9i (9i as well if cpu_costing feature doesn't used)
>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).


Wolfgang Breitling
Centrex Consulting Corporation 


Other related posts: