Re: 10g System statistics - single and multi

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To:
  • Date: Thu, 19 May 2005 15:09:43 -0600

Comments inline

Christo Kutrovsky wrote:

> The only purpose of SREADTIM is to be used in conjuction with MREADTIM
> to produce the ratio of "slowiness" of using multi-io as oposed to
> single IO.
> I was thinking that SREADTIM was used somehow to affect index range
> scans, in a way of estimating how much time it would take to do N ios.

With the new cost formula, single block reads get a cost of ( #SRDS * 
SREADTM ) / SREADTM. Therefore, sreadtm falls out and what is left is #SRDS

> So the Full scan "cost reduction ratio" is:
> ----------------------------        X  CPU COST

the cpu cost is added on, not multiplied

> CPU COST is also dependant on MREADTIM/SREADTIM and CPUSPEED somehow,
> not sure how yet, maybe Wolfgang knows.

No, cpu cost is not dependent on mreadtm, only on cpuspeed and sreadtm. 
It is all documented in the performance Tuning Guide:

Cost = (#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed ) / sreadtim
#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles *)
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second

or with a bit of transformation:

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

The first two summands together form the IO_cost and the last forms the 

Where did MBRC disappear to? #MRds = #blocks/MBRC

> Apologies for doubting your test for single reads. It just that in my
> mind, when you say "single reads" i always assume "random" reads.
It doesn't matter whether they are random or not, single block reads are 
single block reads.

> I was wondering, have you discovered any other cases where SREADTIM
> affects the cost of plans other then full table/index scans ?

As can be seen from the above formula, sreadtim affects the CPU cost. 
This may seem odd, but it stems from the fact that optimizer developers 
tried to keep the "meaning" of the plan cost the same when they 
introduced cpu costing and the cost meant (and still does mean) the 
estimated # of single block reads.
The cute thing is that by multiplying the plan cost with sreadtim you 
get an estimate for the elapsed time of the sql:

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


Wolfgang Breitling
Centrex Consulting Corporation

Other related posts: