Re: os cache vs. db cache

Andrew,

The behavior had undergone tremendous changes between versions. In
Oracle8 and below (when we had the normal LRU-MRU management for
buffer cache)  it is just used to tell the table is small and buffers
are placed in the MRU end of the LRU list.

With 8i the buffer cache management algorithms had gone for an
overhaul and in this version, the small table buffers treated qually
with any other buffers (other than the Big table FTS) , agaist keeping
them in the cold end in case of regular FTS. This way it gets a hedge
over regular full scans.

In 9i and above this parameter is used as a threshold (number of
blocks) eligible for direct reads. Any tables below this threshold
will go thru the buffer cache during direct read.

My previous reply was not generic. It was specific to the post and
hope this clears the confusion.

On 7/10/07, Kerber, Andrew W. <Andrew.Kerber@xxxxxxx> wrote:

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-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of K Gopalakrishnan
Sent: Tuesday, July 10, 2007 1:32 PM
To: Brandon.Allen@xxxxxxxxxxx
Cc: Chris Dunscombe; robyn.sands@xxxxxxxxx; oracle-l
Subject: Re: os cache vs. db cache

Alan,

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 <Brandon.Allen@xxxxxxxxxxx> wrote:
> I think you'd still be better off to cache those full table scans in
the
> 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
would
> make your performance even better.
>
> For more info:
>
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statement
> s_7002.htm#i2215507
>
>
> -----Original Message-----
> From: Chris Dunscombe [mailto:chris@xxxxxxxxxxxxxxxxxxxxx]
>
> 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
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

Author: Oracle Database 10g RAC Handbook, Oracle Press 2006
http://www.amazon.com/gp/product/007146509X/
--
http://www.freelists.org/webpage/oracle-l



------------------------------------------------------------------------------
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.

==============================================================================





--
Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

Author: Oracle Database 10g RAC Handbook, Oracle Press 2006
http://www.amazon.com/gp/product/007146509X/
--
http://www.freelists.org/webpage/oracle-l

Other related posts: