Re: 10g System statistics - single and multi

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

I am playing arround with this formula.

cost(FTS) =3D #blocks/MBRC * MREADTM / SREADTM

There seems to be more to this. I can never get the number to be close enought to what oracle reports, for example:

db_file_multiblock_read_count is 128

drop table newkutro;
create table newkutro (a number);

exec dbms_stats.set_table_stats(user,'NEWKUTRO', numblks=3D>100000);
exec dbms_stats.set_system_stats('MBRC',128);
exec dbms_stats.set_system_stats('SREADTIM',2);
exec dbms_stats.set_system_stats('MREADTIM',16);

100 000 / 128 * 16/2 =3D 6250

select * from newkutro

reports a cost of 6549.

Also by halfing MBRC almost doubles the cost. By doubling SREADTIM it almost halves the cost, but by a different amount.

Could it be some kind of multiplier similar to the multiplier in your articles about reading 10053 traces ? In order to make the cost "compatible" with the old way of calculating.

To me it doesnt make sense to check for mread > sread . opinions ?

On 5/17/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> Christo,

>=20

> I don't know how you come to that conclusion. The value of MBRC does not
> 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 the
> MBRC value factor in.

>=20

> And I just realized that it misquoted the cost for a FTS under the new
> flrmula. It is of course

>=20

> cost(FTS) =3D #blocks/MBRC * MREADTM / SREADTM

>=20

> I suppose the optimizer developers chose to ignore the system statistics
> 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.

>=20

> Christo Kutrovsky wrote:

>=20


> > 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.

> >
> >

>=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 - 18:21:15 CDT