Re: 10g System statistics - single and multi

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@xxxxxxxxxxxxx> 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

Other related posts: