Re: 10g System statistics - single and multi

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 19 May 2005 15:09:43 -0600
Message-ID: <428D0097.4080606@centrexcc.com>

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:
>
> MREADTIM
> ---------------------------- X CPU COST
> MRBC * SREADTIM
>

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
where

#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 CPU_cost.

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

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 19 2005 - 17:14:25 CDT