RE: os cache vs. db cache
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'Andrew.Kerber@xxxxxxx'" <Andrew.Kerber@xxxxxxx>, kaygopal@xxxxxxxxx, Brandon.Allen@xxxxxxxxxxx
- Date: Tue, 10 Jul 2007 21:10:34 +0100
Andrew,
How large are the tables that you're caching? What's the total size of the
cached tables, and how large is your SGA?
I'd like to try this, as I have a server with memory to burn (64 GB, of which
>40GB is unused).
Paul Baumgartel
CREDIT SUISSE
Information Technology
Securities Processing Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kerber, Andrew W.
Sent: Tuesday, July 10, 2007 3:17 PM
To: kaygopal@xxxxxxxxx; Brandon.Allen@xxxxxxxxxxx
Cc: Chris Dunscombe; robyn.sands@xxxxxxxxx; oracle-l
Subject: RE: os cache vs. db cache
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/
--
//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.
==============================================================================
--
//www.freelists.org/webpage/oracle-l
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
