Re: 10g System statistics - single and multi
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: Martic Zoran <zoran_martic@xxxxxxxxx>
- Date: Fri, 20 May 2005 17:06:47 -0400
Martin,
I am not trying to say that the optmizer should be someting overly
complex and totally unpredictable.
I just find that the logic Oracle uses for it's computation of the
cost reduction ratio is somewhat flawed.
The reduction ratio is based on sequencial single reads vs. sequencial
multiblock reads. As oposed to based on random single reads vs
sequencial multi-block reads.
I also see a flaw in what I think should be the right logic, as the
cost reduction ratio would be huge with the values of random single
read.
For example:
on my test array, using iometer, 8K single read:
sequencial single read =3D 0.45 ms
random single read =3D 6.3 ms
32 mrbc (256k) sequencial multi =3D 2.2 ms
Cost reduction with current formula (sequencial single read) :
2.2 / 0.45 / 32 =3D ~0.15=20
Cost reduction with random single read
2.2 / 6.3 / 32 =3D ~0.01=20
So, not taking into account any caching, if you multiply the cost of
plan with a FTS by sreadtim, you will have a some-what correct timing.
If you mutiply the cost of a plan with index range scan by sreatim,
you may have a very incorect value or a very correct value, depending
on the clustering factor and the "orderness" of the index itself.
(again assuming no caching at any lvl)
I will try to make a test case for both these situation.
By using this formula (sequencial single read instaed of random single
read), Oracle essentially increases the cost of FTS.
But when you throw caching into all this, if you are to use the random
sinle read time, then the cost of index access will be over-priced by
5-10 ms per block cached in memory.
So by using the sequencial single read, Oracle kind-a accounts for
caching in it's cost calculations and lowers costs for index access.
Does all this make any sense to you ?
Christo
On 5/20/05, Martic Zoran <zoran_martic@xxxxxxxxx> wrote:
> Christo,
>=20
> You should separate things that are maybe happening on
> the disk level or disk cache level from the Oracle
> optimizer COST.
>=20
> Oracle does not recognize is the read random disk I/O
> read or eventually sequential single disk read.
>=20
> For Oracle all single block reads are measured equally
> if not proved differently.
>=20
> What is happening after that at execution time neither
> Oracle or OS or I/O subsystem will give you measures,
> prediction or cost in front at the time COST is
> calculated.
>=20
> Also, not sure how are you going to prove anything
> about the difference while ORacle does not know
> anything about that difference or you have
> statistics/events recognizing or counting the
> difference.
>=20
> At the end, even Oracle developers are not building
> the neural network optimizer that will give you
> unpredictable execution plans because at the end you
> need to balance the gain of having simple
> deterministic (eve based on 100 of input parameters)
> useful algorithm and too complex non deterministic
> beast.
>=20
> Regards,
> Zoran Martic
>=20
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>=20
--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
//www.freelists.org/webpage/oracle-l
Other related posts:
