Re: 10g System statistics - single and multi
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- Date: Fri, 20 May 2005 15:15:54 -0600
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
