Re: 10g System statistics - single and multi
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Fri, 20 May 2005 14:25:23 -0400
>the cpu cost is added on, not multiplied
oops
> It doesn't matter whether they are random or not, single block reads are
> single block reads.
I disagree with this one. Single block reads from dfmrc =3D 1 are very
different then single block reads to access a table from a range scan.
The range scan single block reads can be sequencial, on a freshly
built index.
I will try to find time soon and run a test similar to yours, and
compare single block reads times from dfmrc =3D 1 and from a index with
a high clustering factor to maximize randomness.
Time exectation are the following:
- sreadtim for sequencial =3D 0.2-0.5 ms
- sreadtim for random =3D 5-10 ms
>The cute thing is that by multiplying the plan cost with sreadtim you
>get an estimate for the elapsed time of the sql:
I disagree. This would only be valid for FTS. The value for sreadtim
is for sequencial single reads. Random single reads have a much higher
sreadtim.
As I said, i will try to make a test soon and show this inside oracle,
not through extern IO testing applicaiton.
Christo
On 5/19/05, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> Comments inline
>=20
> Christo Kutrovsky wrote:
>=20
> > The only purpose of SREADTIM is to be used in conjuction with MREADTIM
> > to produce the ratio of "slowiness" of using multi-io as oposed to
> > single IO.
> >
> > I was thinking that SREADTIM was used somehow to affect index range
> > scans, in a way of estimating how much time it would take to do N ios.
>=20
> With the new cost formula, single block reads get a cost of ( #SRDS *
> SREADTM ) / SREADTM. Therefore, sreadtm falls out and what is left is #SR=
DS
>=20
> >
> > So the Full scan "cost reduction ratio" is:
> >
> > MREADTIM
> > ---------------------------- X CPU COST
> > MRBC * SREADTIM
> >
>=20
> the cpu cost is added on, not multiplied
>=20
> >
> > CPU COST is also dependant on MREADTIM/SREADTIM and CPUSPEED somehow,
> > not sure how yet, maybe Wolfgang knows.
>=20
> No, cpu cost is not dependent on mreadtm, only on cpuspeed and sreadtm.
> It is all documented in the performance Tuning Guide:
>=20
>=20
> Cost =3D (#SRds * sreadtim +
> #MRds * mreadtim +
> #CPUCycles / cpuspeed ) / sreadtim
> where
> #SRDs - number of single block reads
> #MRDs - number of multi block reads
> #CPUCycles - number of CPU Cycles *)
> sreadtim - single block read time
> mreadtim - multi block read time
> cpuspeed - CPU cycles per second
>=20
> or with a bit of transformation:
>=20
> Cost =3D #SRds + #MRds*mreadtim/sreadtim + #CPUCycles / cpuspeed / sreadt=
im
>=20
> The first two summands together form the IO_cost and the last forms the
> CPU_cost.
>=20
> Where did MBRC disappear to? #MRds =3D #blocks/MBRC
>=20
> > Apologies for doubting your test for single reads. It just that in my
> > mind, when you say "single reads" i always assume "random" reads.
> It doesn't matter whether they are random or not, single block reads are
> single block reads.
>=20
> >
> > I was wondering, have you discovered any other cases where SREADTIM
> > affects the cost of plans other then full table/index scans ?
> >
>=20
> As can be seen from the above formula, sreadtim affects the CPU cost.
> This may seem odd, but it stems from the fact that optimizer developers
> tried to keep the "meaning" of the plan cost the same when they
> introduced cpu costing and the cost meant (and still does mean) the
> estimated # of single block reads.
> The cute thing is that by multiplying the plan cost with sreadtim you
> get an estimate for the elapsed time of the sql:
>=20
> cost*sreadtim =3D #SRds*sreadtim + #MRds*mreadtim + #CPUCycles/cpuspeed
>=20
>=20
> --
> Regards
>=20
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
> --
> //www.freelists.org/webpage/oracle-l
>=20
--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
//www.freelists.org/webpage/oracle-l
Other related posts:
