Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: 10g System statistics - single and multi

From: Wolfgang Breitling <>
Date: Wed, 18 May 2005 21:04:19 -0600
Message-Id: <>

I am not quite sure what you are testing. First of all it appears to me that you are testing on the OS level whereas I am testing from the Oracle point of view. Secondly, you are saying you are using async READ IO. AFAIK, Oracle is not using async reads, only async writes. Reads are done by the foreground process which has nothing else to do until the requested/needed data is in the buffer pool (or pgs for direct reads) so there is no point for Oracle doing async reads. Please, anybody chime in to correct me. So, I am testing the time for single block reads as encountered by Oracle (= db file sequential reads) , i.e. sreadtm times, vs multi block reads as encountered by Oracle (= db file scattered reads), i.e. mreadtm times. With "as encountered by Oracle" I mean that for all intense and purpose, Oracle sends off a single or multi-block IO request. What happens at the OS or SAN level is beyond Oracle. That multi-block IO request may be broken by the OS into a series of single block reads for all I (or Oracle) know. Or it may be served from the file or SAN cache. (actually in my tests I was using an AIX jfs2 filesystem with concurrent IO enabled, so I believe that eliminates the file system cache).

At 02:15 PM 5/18/2005, Christo Kutrovsky wrote:
>Ah !=20
>I know understand the flaw of your test OR the flaw of my
>understanding for sread vs mread.
>before i start.
> >I don't quite understand how to read this. what does, e.g., "8k read-1"
> >mean as opposed to "8k read-4"?
>8k read-1 - request 1 io, wait for answer, request 1 io, wait for answer...=
> etc.
>8k read-4 - request 4 ios, wait for 1 io answer and send another
>request... etc. In such away, that there would be always 4 pending IO
>Basically async IO. In the Oracle world, that would be multiple
>sessions doing IO. Or the dbwriter with asyncIO enabled.
>The problem with our discussion is our assumptions.
>My assumption:
>sread =3D "db file sequencial read" =3D Random IO from say, an INDEX range
>scan accessing the table. (not the range scan itself, as on a freshly
>rebuild index, the range scan will be almost sequencial IO, but the
>table access will probably be random IO)
>mread =3D "db file scattered read" =3D sequencial IO =3D Full table scan or
>full index scan.
>Your assumption (i could be wrong, i concluded that this is your
>assumption based on the test you are doing, and the timings you were
>sread =3D single block access, could be resulting from a dfmrc been 1.
>I.e. could be either RANDOM or SEQUENCIAL IO.
>mread =3D sequencial IO with dfmrc > 1
>Basically your test shows the benefit of uding dfmrc, and shows how
>even at the last step, doubling from 64 to 128, the time increases
>only 1.88 times, i.e. still some benefit from large reads.
>My tests however, show disk times of RANDOM access. Which is very
>different, from your tests, which test disk times of SEQUENCIAL
>access, with different IO sizes.
>Makes sense ?


Wolfgang Breitling
Centrex Consulting Corporation

Received on Wed May 18 2005 - 23:08:58 CDT

Original text of this message