Christo,
As Wolfgang nicely explained to you it is hard to me
to accept that Oracle will go that deep to think that
some single block read (SBR) is random or
"sequential".
No way Oracle is going to play this game because the
head on the disk(s) are who knows where, your
"sequential" requests on the I/O sub queue(s) are who
knows how apart one from each other.
The only game Oracle is playing is to find out the
effective MBRC, the speed of MBR and the speed of SBR
to use the formula Oracle put bravely in 9i Tuning
Guide while smartly removing it from 10g Tuning Guide.
The only thing Oracle is aware is all the good/crap
things he knows about his buffer cache and how long
some disk I/O related waits took.
At the moment I have never seen such a good
relationship between Oracle and I/O subsystem
suppliers to merge statistics and have better
controlled environemnt.
Oracle is doing what it can and not even considering
such a variety of I/O subsystems and OS caching
layers.
At the end why would Oracle play that hard game when
simple MBR and SBR timing is good enough to do good
predictions, of course only when you have system
statistics collected properly.
Oracle will maybe evolve into having system statistics
per object or at least per file/disk. That will be
much better to use then global system statistics about
MBR and SBR.
Regards,
Zoran Martic
- Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
wrote:
> 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 = 0.45 ms
> random single read = 6.3 ms
> 32 mrbc (256k) sequencial multi = 2.2 ms
>
> Cost reduction with current formula (sequencial
> single read) :
>
> 2.2 / 0.45 / 32 = ~0.15
>
> Cost reduction with random single read
>
> 2.2 / 6.3 / 32 = ~0.01
>
> 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
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 21 2005 - 01:48:02 CDT
