Re: 10g System statistics - single and multi

At 12:25 PM 5/20/2005, Christo Kutrovsky wrote:
> >the cpu cost is added on, not multiplied
>oops
>
> > It doesn't matter whether they are random or not, single block reads are
> > single block reads.
>
>I disagree with this one. Single block reads from dfmrc = 1 are very
>different then single block reads to access a table from a range scan.
>The range scan single block reads can be sequencial, on a freshly
>built index.

I disagree and raise you one :-)

I presume with "sequential read" you mean reads of consecutive blocks. If 
we agree on that definition then it is my proposition that such a thing 
exists only in an isolated single-user lab environment. If you consider a 
SAN that is accessed by multiple users on potentially multiple databases 
(and maybe other applications), then from the view of the disk read/write 
head there is no such thing as a sequential read. For your individual 
session you may think that you are reading consecutive blocks, but by the 
time you issue the next read for the next, supposedly adjacent, block, the 
chances that the head is still where you left it after your previous read 
are remote. So from the overall system point of view, it becomes a random read.
After I wrote this I had a terrible sense of deja-vu, hopped over to asktom 
and sure enough found this quote:
"Even if it was, that would only make a difference in a single user 
system!  do
you really think the heads will remain positioned where you left them after a
read?  As soon as you are done reading, the OS is off servicing some other 
read
-- thats why controllers and such have their own CPU's in effect.  There is
perhaps a 0.00001% chance the head will be where you left it in a multi-user
system."
http://asktom.oracle.com/pls/ask/f?p=4950:8:24292::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:730289259844,

To be precise, Tom's quote is in response to a question on the demerits of 
multiple extents, but I believe it is equally valid in this context.

If I find some time over the long weekend I will try and put a testcase 
together to validate our (Tom's and mine) assertion.


>Time exectation are the following:
>- sreadtim for sequencial = 0.2-0.5 ms
>- sreadtim for random = 5-10 ms

Again, what are the circumstances for you measurements. Are you measuring 
on the OS level? What else was happening in the system/database?


> >The cute thing is that by multiplying the plan cost with sreadtim you
> >get an estimate for the elapsed time of the sql:
>
>I disagree. This would only be valid for FTS. The value for sreadtim
>is for sequencial single reads. Random single reads have a much higher
>sreadtim.

I have to disagree again. I just did a mathematical transformation of the 
cost formula as documented in the Performance Tuning Guid:

cost = (#SRds * sreadtm + #MRds * mreadtm + #cpucycles / cpuspeed) / sreadtm

thus

cost * sreadtm = #SRds * sreadtm + #MRds * mreadtm + #cpucycles / cpuspeed

which has a dimension of seconds. So "cost * sreadtm" is the optimizer's 
approximation of the time it will take to process the query.

And your statement that "random single reads have a much higher sreadtim" 
is not correct. SREADTM is the average of all single block reads over the 
measurement period, comprising "random" as well as so-called "sequential" 
reads. It is incumbent on the dba to ensure that the measurement period 
covers a representative workload. Once that is done, then SREADTM IS the 
average time (in seconds) it takes to read a single block. Therefore, #SRds 
* SREADTM IS the average time it will take to read #SRds blocks. Likewise, 
MREADTM is the average time to read MBRC blocks with one read. Thus, #MRds 
* MREADTM = (#Blocks to read with multiblock read)/MBRC * MREADTM is the 
average time it will take to read the #blocks read with full scans, given 
the effective multiblock count of MBRC. And finally, #cpucycles is the 
estimate of the cpu operations to be performed during the sql, Since 
cpuspeed is a measure of operations/seconds, #cpucycles / cpuspeed also has 
a dimension of seconds and is the estimate of the cputime required to 
process the query. In the true spirit of YAPP that elapsed time = services 
time + wait time, the above formula is exactly an estimate that, with the 
first two summands being the IO or wait time and the third one being the 
service time.



Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com 

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

Other related posts: