Re: 10g System statistics - single and multi
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- Date: Tue, 17 May 2005 16:54:21 -0400
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@xxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
