Re: 10g System statistics - single and multi

On 5/18/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> Christo,
> 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

Correct, I am testing from OS side first.

> point of view. Secondly, you are saying you are using async READ IO. AFAI=
K,
> 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/neede=
d
> data is in the buffer pool (or pgs for direct reads) so there is no point

Agreed. But having 4 async reads (random IO only) is the same as having 4 oracle sessions activelly doing IO (separate queries, or parallel or whatever). The async reads are just an adition, we can safelly ignore all results with multiple async reads. I ussually use them for "capacity" planning. i.e. 4 if I had 4 queries doing IO, that would be my responce time.

> 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
> (=3D db file sequential reads) , i.e. sreadtm times, vs multi block reads=
 as
> encountered by Oracle (=3D db file scattered reads), i.e. mreadtm times. =
With
> "as encountered by Oracle" I mean that for all intense and purpose, Oracl=
e
> sends off a single or multi-block IO request. What happens at the OS or S=
AN
> 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 a=
n
> AIX jfs2 filesystem with concurrent IO enabled, so I believe that
> eliminates the file system cache).

Yes fully agreed with what you've said so far.

However, by looking at your test case and timings, I think the way you are mesasuring single reads is incorrect.

>dfmrc count elapsed (usec)
>1 1985 297.355
>3 1 300.000

Does that mean that you are setting dfmrc to 1, and running a full table sc= an ?

>=20
> At 02:15 PM 5/18/2005, Christo Kutrovsky wrote:
> >Ah !=3D20
> >
> >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=
...=3D
> > 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
> >requests.
> >
> >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 =3D3D "db file sequencial read" =3D3D 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 =3D3D "db file scattered read" =3D3D sequencial IO =3D3D Full tabl=
e 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
> >observing)
> >sread =3D3D single block access, could be resulting from a dfmrc been 1.
> >I.e. could be either RANDOM or SEQUENCIAL IO.
> >mread =3D3D 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 ?
>=20
> Regards
>=20
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>=20
>=20

--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group

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