RE: Tim Gorman's "...Cost-Based Optimizer.doc"
- From: Stephen Barr <ascaroth969@xxxxxxxxxxx>
- To: mgogala@xxxxxxxxxxxxxxxxxxxx, breitliw@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 17 Jun 2005 09:32:45 +0100 (BST)
Even with a Symmetrix things aren't as rosy as EMC
tries to make out.
The "intelligent pre-fetch" operates at the level of
the DA such that the DA starts prefecthing tracks when
there are "sequential" track requests in the cache. If
there is no activity on a particular sequence for a
specific amount of time, the prefetch will die.
Keeping this in mind it's actually extremely important
to get the host level striping correct in order to get
maximum gain from pre-fetching. If the stripe size is
too small then you may be shattering atomic reads
across multiple disks and if it's too large the
pre-fetch may die before coming round to the first
disk again.
We found large performance gains from getting this
right - however, the cache effect was ONLY visible in
an environment where there were a small amount of
users. Once we start to scale up, the benefit from
pre-fetching becomes totally random and very minimal -
almost to the extent where we can't measure it.
As for sequential reads (oracle) being slower than
scattered reads - it may also be the results of your
sequential reads queuing up behind other scattered
reads if it's a multi-user environment and you just
happened to run your tests at a time when heavy
scattered reads were occurring on the same physical
device as your sequential reads (something that occurs
a lot if you follow the vendors advice of using a SAN
as a black box).
I suppose the moral is that prefetch is nice if it
works, but shouldn't be designed for or relied upon in
any way. A disk cache isn't as important as other
factors such as stripe sizing / metas / physical
placement on disk.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Mladen Gogala
Sent: 16 June 2005 19:07
To: Wolfgang Breitling
Cc: somckit.khemmanivanh@xxxxxxxxxxxxxxxx;
cmarquez@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Tim Gorman's "...Cost-Based
Optimizer.doc"
Wolfgang Breitling wrote:
>
> I believe that if scattered, i.e. multi-block, reads
are faster than
> sequential, i.e. single-block, reads it is because
the SAN recognized
> a read pattern and prefetched the next bunch of
block into the cache.
Now, that would only be possible if your SAN apparatus
is more
intelligent then Geraldo Rivera. Not that it's very
hard,
but the only type of SAN device that can recognize
patterns is EMC
Symmetrix (the very top of the line) and they are
prohibitively expensive.
--
Mladen Gogala
Oracle DBA
Ext. 121
--
//www.freelists.org/webpage/oracle-l
___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
