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:59:53 -0400
Message-ID: <52a152eb050517145930f4deff@mail.gmail.com>


About my statement:
"very high value for mbrc (so that your mread > sread)"

Sorry Wolfgang, now that we have 2 sets of "multiblock read count" it's a little mess. Above, for mbrc, i was refering to db_file_multiblock_read_count.

Here's the explanation:

If db_file_multiblock_read_count is low (say 8 or 16) and you calculated system stats, chances are that mread will be < sread. Or in other words, reading sequencially 64K takes far less time then reading 8K randomly.

Thus oracle will use the old formula for full table scan costs, and the effect on full scans from system statistics is zero.

if you use a large value for db_file_multiblock_read_count (say 64 or 128 the max) then mread will likelly be > sread. Thus the new formula will be used.

This is what I ment.
Sorry about the confusion :)

On 5/17/05, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
> Wolfgang,

>=20

> "I don't know how you come to that conclusion."
>=20

> If you refer to the statement under the document, it is not me that is
> concluding this. It is what is said in that metalink document !!
>=20

> I find it very strange that they have such logic.
>=20
>=20

> On 5/17/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> > Christo,
> >
> > I don't know how you come to that conclusion. The value of MBRC does no=
t
> > factor into the decision whether to use the new cost formula or not. It
> > is mreadtm which needs to be greater than sreadtm for the new cost
> > formula (and cpu costing, at least in 9i) to be used. Only then does th=
e
> > MBRC value factor in.
> >
> > And I just realized that it misquoted the cost for a FTS under the new
> > flrmula. It is of course
> >
> > cost(FTS) =3D #blocks/MBRC * MREADTM / SREADTM
> >
> > I suppose the optimizer developers chose to ignore the system statistic=
s
> > if they look suspicious and from their point of view mreadtm has to be
> > greater than sreadtm and if it is not they ignore the entire thing. Of
> > course, that could change at any time. Until then you need to check the
> > values of your gathered system statistics for mreadtm > sreadtm.
> >
> > Christo Kutrovsky wrote:
> >
> > > 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 ~6=
50
> > >
> > > 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.
> > >
> > >
> >
> > --
> > 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 - 18:04:36 CDT

Original text of this message

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