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: Tue, 17 May 2005 17:07:12 -0400
Message-ID: <52a152eb05051714071e95aacf@mail.gmail.com>


Found this article:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data= base_id=3DNOT&p_id=3D153761.1

One specific extract is interesting:
If the mreadtim is 1.2 times higher than the sreadtim with a multiblockreadcount set to 8, then the I/O cost part will be equivalent to the Oracle8i I/O cost.

I didnt check for this, but keep it in mind when playing arround with costs= .

On 5/17/05, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> Yes, this is exactly my observation. I dont understand why Oracle did
> this ! They will never have this right, it seems.

>=20

> actually, i tried mread =3D 1.00000001 and sread =3D 1 -> cost =3D ~650
>=20

> So basically, this makes using a very high value for mbrc (so that
> your mread > sread) almost mandatory in 10g, otherwise your FTS costs
> would use the old formula.
>=20
>=20

> On 5/17/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> > I found that Oracle does not use the new costing formula id mreadtm is
> > not larger than sreadtm. If sreadtm >=3D mreadtm it reverts back to the
> > the cost formula which uses the adjusted dfmrc value rather than the
> > system statistics mbrc value to estimate the cost of an FTS. That was
> > true for Oracle9i. I have not done extensive work with 10g and things
> > like that can change by patch release.
> >
> > What you describe would be consistnt with that observation
> >
> > mreadtm =3D sreadtm =3D 1 =3D=3D> cost(FTS) =3D #blocks/adjustedDFMRC =
=3D ~1400
> >
> > mreadtm =3D 2, sreadtm =3D 1 =3D=3D> cost(fts) =3D #blocks/mbrc =3D ~10=
50
> >
> > mreadtm =3D 2, sreadtm =3D 3 =3D=3D> cost(FTS) =3D #blocks/adjustedDFMR=
C =3D ~1400
> >
> > Christo Kutrovsky wrote:
> > > Hello,
> > >
> > > What i've discovered was something very strange. When I had both
> > > values to 1, my cost of a FTS would be ~1400. When I set multi to 2,
> > > the cost would go DOWN to ~1050. When I set it to 3, it would go back
> > > to ~1400.
> > >
> > > What I've discovered is that when SINGLE >=3D3D MULTI, then oracle us=
es N
> > > * SINGLE for full table scans some formula independant from the value
> > > of MULTI.
> > >
> > > In a way, this makes sense, as it is not possible to have 1 read to b=
e
> > > slower then MANY reads. But, this is an average value, thus it is
> > > quite possible (and likelly on many systems) that multi-block reads
> > > are faster then single block reads.
> > >
> > > However Oracle doesn't handle this case at all. I find this to be a
> > > severe limitation. What do you think ?
> > >
> >
> > --
> > Regards
> >
> > Wolfgang Breitling
> > Centrex Consulting Corporation
> > www.centrexcc.com
> >
>=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 Tue May 17 2005 - 17:11:49 CDT

Original text of this message

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