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: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Thu, 19 May 2005 16:27:24 -0400
Message-ID: <52a152eb05051913271c206d48@mail.gmail.com>


I think i've found the flaw in my assumptions.

The only purpose of SREADTIM is to be used in conjuction with MREADTIM to produce the ratio of "slowiness" of using multi-io as oposed to single IO.

I was thinking that SREADTIM was used somehow to affect index range scans, in a way of estimating how much time it would take to do N ios.

The only purpose of MRBC is to get the "real" number of blocks on average, when doing Full scans.

Together, MREADTIME, SREADTIME, MRBC are used to calculate a ratio of cost reduction for fulltable scans. Effectivelly replacing the previous K number used as explained in "A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT". So the Full scan "cost reduction ratio" is:

   MREADTIM

----------------------------        X  CPU COST
MRBC * SREADTIM CPU COST is also dependant on MREADTIM/SREADTIM and CPUSPEED somehow, not sure how yet, maybe Wolfgang knows.

Thus, my confusion was that SREADTIM is used in calculating RANDOM IO when in fact it is used to calculate the full table scan cost reduction ratio.

Thus, Wolfgang, showing me his tests with dfmrc been 1 and me saying "but no sreadtim should be > mreadtim"

Thus Oracle's behaviour of ignoring these stats if mread <=3D sread. It just doesn't make sense.

Wolfgang, thanks for the nice discussion and helping me understand the real purpose of SREADTIM.

Apologies for doubting your test for single reads. It just that in my mind, when you say "single reads" i always assume "random" reads.

I was wondering, have you discovered any other cases where SREADTIM affects the cost of plans other then full table/index scans ?

On 5/19/05, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> 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 Orac=
le

>=20

> Correct, I am testing from OS side first.
>=20
> > point of view. Secondly, you are saying you are using async READ IO. AF=
AIK,
> > Oracle is not using async reads, only async writes. Reads are done by t=
he
> > foreground process which has nothing else to do until the requested/nee=
ded
> > data is in the buffer pool (or pgs for direct reads) so there is no poi=
nt
>=20

> 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.
>=20

> > 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 Orac=
le
> > (=3D db file sequential reads) , i.e. sreadtm times, vs multi block rea=
ds 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, Ora=
cle
> > 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 th=
e OS
> > into a series of single block reads for all I (or Oracle) know. Or it m=
ay
> > 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).
>=20

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

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

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

> Does that mean that you are setting dfmrc to 1, and running a full table =
scan ?
>=20
>=20
>=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 answ=
er...=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 IND=
EX 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 ta=
ble 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 ?
> >
> > Regards
> >
> > Wolfgang Breitling
> > Centrex Consulting Corporation
> > www.centrexcc.com
> >
> >
>=20
>=20

> --
> Christo Kutrovsky
> Database/System Administrator
> The Pythian Group

>=20

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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 19 2005 - 16:32:06 CDT

Original text of this message

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