Re: 10g System statistics - single and multi

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Tue, 17 May 2005 16:54:21 -0400
Message-ID: <52a152eb05051713544f014099@mail.gmail.com>

Yes, this is exactly my observation. I dont understand why Oracle did this ! They will never have this right, it seems.

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

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.

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.

>=20

> What you describe would be consistnt with that observation
>=20
> mreadtm =3D sreadtm =3D 1 =3D=3D> cost(FTS) =3D #blocks/adjustedDFMRC =3D=
 ~1400

>=20

> mreadtm =3D 2, sreadtm =3D 1 =3D=3D> cost(fts) =3D #blocks/mbrc =3D ~1050
>=20
> mreadtm =3D 2, sreadtm =3D 3 =3D=3D> cost(FTS) =3D #blocks/adjustedDFMRC =
=3D ~1400

>=20

> 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 uses=
 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 be
> > 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 ?
> >

>=20

> --
> Regards

>=20

> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com

>=20

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

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

Received on Tue May 17 2005 - 16:59:04 CDT