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