Re: 10g System statistics - single and multi

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Fri, 20 May 2005 17:06:47 -0400
Message-ID: <52a152eb050520140658a79688@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 20 2005 - 17:11:27 CDT