Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: os cache vs. db cache

RE: os cache vs. db cache

From: Kerber, Andrew W. <>
Date: Tue, 10 Jul 2007 14:16:48 -0500
Message-ID: <>

I have to disagree on this one. I have been through the process several times of identifying the key tables, sizing the db_cache_size to include all those tables, and putting the tables in the cache, and watching the performance improve, often dramatically. Even with very large tables. Admittedly you have to have the memory to burn, but its pretty much a direct relationship, the larger the cache you have the better the performance.

-----Original Message-----
[] On Behalf Of K Gopalakrishnan Sent: Tuesday, July 10, 2007 1:32 PM
Cc: Chris Dunscombe;; oracle-l Subject: Re: os cache vs. db cache


I beg to differ here. Caching tables work excellently on paper. But when you put that in to practice it will be otherwise. In real life, they will not cached in the buffer cache and also they are subject to the normal LRU or touchcount aging. There is a _small_table_threshold defines the tables which are eligible for caching and/or when the table is bigger than 2% of the buffer cache they will not be cached.

So the point here is, for the OP, with the 200-300M tables will NOT be cached in the buffer cache unless he sets the db_cache_size (buffer cache) 10-15G.

On 7/10/07, Allen, Brandon <> wrote:
> I think you'd still be better off to cache those full table scans in
> Oracle cache (e.g. alter table my_fts_tab cache). That way, you avoid
> having to copy them from the OS cache to the DB cache and all the
> overhead that is involved with performing a consistent get, which
> make your performance even better.
> For more info:
> s_7002.htm#i2215507
> -----Original Message-----
> From: Chris Dunscombe []
> 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.

Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004

Author: Oracle Database 10g RAC Handbook, Oracle Press 2006

NOTICE:  This electronic mail message and any attached files are confidential.  The information is exclusively for the use of the individual or entity intended as the recipient.  If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited.  If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.


Received on Tue Jul 10 2007 - 14:16:48 CDT

Original text of this message