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_at_umb.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of K Gopalakrishnan
> Sent: Tuesday, July 10, 2007 1:32 PM
> To: Brandon.Allen_at_oneneck.com
> Cc: Chris Dunscombe; robyn.sands_at_gmail.com; 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_at_oneneck.com> 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_at_thedunscombes.f2s.com]
> >
> > 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