RE: Tim Gorman's "...Cost-Based Optimizer.doc"

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
Sent: 16 June 2005 19:07
To: Wolfgang Breitling
Cc: somckit.khemmanivanh_at_weyerhaeuser.com; cmarquez_at_collegeboard.org; oracle-l_at_freelists.org 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


--
http://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
--
http://www.freelists.org/webpage/oracle-l