Re: os cache vs. db cache

Hi,

One situation I've experienced was a smallish (< 250GB) third-party online operational database on Solaris where the OS cache acted as a cache for Full Table scans of tables around the 100-300 MB size. This worked well although it was more by accident than design.

Cheers,

Chris

Quoting Robyn <robyn.sands_at_gmail.com>:

> Thank you Mark and Brandon,
>
> This is the kind of information I'm looking for; I've read Steve's stuff but
> it's been a while and the AIX paper is new to me. I *think* we need to make
> some changes in our approach, but right now, I just want to gather and study
> as much information on the different options and approaches as possible.
>
> So, if anyone has additional links, documents or experiences, I'd appreciate
> the input.
>
> thank you ... Robyn
>
> On 7/9/07, Allen, Brandon < Brandon.Allen_at_oneneck.com> wrote:
>>
>> I'd be curious to hear anyone's reasons for preferring OS cache to DB
>> cache.
>>
>> It seems pretty clear cut to me that it is better to allow Oracle to
>> manage its own cache since it has much more knowledge available internally
>> to help predict which blocks are most likely to be needed again. I've had
>> good results with CIO (Concurrent, a.k.a non-buffered, non-inode-locking
>> I/O) on AIX, but I did increase db_cache_size to make up for the lack of
>> filesystem buffering - in one case from 600M to 1500M, in another I just
>> used CIO from the beginning so there was no before/after comparison, but
>> performance has been excellent with CIO. In the case where I switched from
>> regular, buffered I/O to CIO and increased db_cache_size from 600M to 1500M,
>> the performance of a fixed set of batch jobs improved from an average
>> runtime of 166 minutes to 129 minutes - so a 22% reduction in runtime, but
>> it's difficult to say how much of that improvement was from switching to CIO
>> and how much was just due to the increase in db_cache_size alone.
>>
>> Here's a great paper specifically on AIX CIO for more info:
>> http://www-03.ibm.com/servers/aix/whitepapers/db_perf_aix.pdf
>>

Chris Dunscombe

www.christallize.com

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