Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10g System statistics - single and multi

Re: 10g System statistics - single and multi

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 20 May 2005 15:15:54 -0600
Message-Id: <6.2.1.2.2.20050520144430.10b6f5b8@pop.centrexcc.com>


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
Received on Fri May 20 2005 - 17:20:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US