RE: Cache a table

I always wondered why Oracle thought this was a useful table attribute.

My gut feeling is that it is an extra that does little.

For example, say we want to keep a code table in memory because it is constantly being hit for column verifiction. By definition, if a table is constantly being queried, it's segments will be in memory because they never age out. That sounds like cacheing to me.

And then I remember a specific piece of Oracle documentation saying that, even though we may mark a table to be "cached", it *still* may be aged out if memory is needed for other data blocks.

Like I said, sounds a little like "here you have it, and here you don't".

I'm sure that my impression is wrong and someone will correct me. But I doubt I will use the "CACHE" option anytime soon.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L

My understanding is that the KEEP and RECYCLE Pools are just 'names' in the sense that they are placeholders for assigning an object to the BUFFER_POOL { KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for KEEP and RECYCLE are exactly the same. Assigning a specific object to one of these named pools segregates objects by retention-requirements. Thus, KEEP does not imply a different treatment of the Buffers - rather it makes sure that objects that you would like to 'keep' around are specifically directed to a common pool and vice versa....

Does anyone have additional information that can verify this? I heard this from a knowledgeable Oracle instructor in an Oracle Tuning training Class.

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional!

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Tim Gorman [mailto:tim_at_sagelogix.com]
>Sent: Tuesday, October 21, 2003 6:59 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Cache a table
>
>
>Good points, Arup.
>
>Actually, I would argue that there is better reason to
>consider using the
>RECYCLE pool than to consider how to "cache" tables or use the
>KEEP pool.
>The advantage of effective use of the RECYCLE pool is better
>behavior in the
>rest of the Buffer Cache...
>
>When you think of it, the default DEFAULT buffer pool and the
>KEEP pool have
>essentially the same purpose: long-term caching of blocks.
>What keeps them
>from accomplishing that mission but objects whose blocks waste
>space and
>energy cycling into and out from the Buffer Cache?
>
>It's kind of like a school teacher admonishing his/her class that "a
>troublesome few have ruined things for everybody". When I was
>in school,
>"troublemakers" were segregated from the rest of the class, sometimes
>cumulatively into a separate classroom (we called ourselves
>"the mentals"
>and read Mad magazines all the time, which accounts for a lot, then and
>now). Nowadays, I'm sure that such a measure isn't considered
>for fear of
>lawsuit for hurting the "self-esteem" of the poor dears.
>Never mind the
>confusion between the useless feel-good phrase "self-esteem"
>and the more
>useful and thought-provoking phrase "self-respect". Oh well,
>better stop
>now...
>
>Anyway, marking a table as CACHE and placing it in a KEEP
>buffer pool which
>is large enough to accommodate all of the used blocks is the
>closest thing
>to pinning a table into the Buffer Cache as you'll get, as
>Arup described.
>
>Of course, there is little benefit from such a move, as Arup
>also mentioned.
>
>Just yesterday, I visited a customer who had a series of SQL
>statements that
>were executing some 10 million times _each_ per day, averaging
>about 20-1500
>LIOs per execution. They each had a 99.99999999999% "buffer cache hit
>ratio", yet strangely enough the performance on the server is
>absolute crap
>because the eight brand-new 2Ghz CPUs on the server are busy
>as hell with no
>time to spare for anything.
>
>Well, you know and I know that they simply need more CPUs,
>which is what HP
>is busy telling them, today right as we "speak". Moreover, Oracle
>Consulting is shoulder to shoulder with them, nodding their
>heads. No way
>does the crap custom-built application need to be altered in
>any minor way,
>so that it doesn't keep performing the same useless validation
>query on the
>same set of static lookup tables over and over again for each
>row inserted,
>when the JDBC thin client can easily query these tables only
>once and store
>the results. Nope. No sirree...
>
><Cliff-Clavin-voice>
>It's a little-known fact that Java code actually has the consistency of
>concrete, once in production. There are so many interdependencies from
>shared modules and RPCs that people are terrified of modifying
>anything,
>probably for good reason. Far easier to shift blame or say
>"hear hear" when
>the vendor proposes another 4-8 CPUs.
>
>Ah, I believe I'll have another beer when you're ready, Sammy...
></Cliff-Clavin-voice>
>
>Anyway, first tune the SQL. Then, tune to the application to
>get rid of
>unnecessary SQL. Then and only then, consider tuning the
>Buffer Cache to
>segregate "bad" tables to the RECYCLE pool or "pinning" tables
>to the KEEP
>pool. Reversing the order is a great way to convert a happy
>application
>capable of running on a small server to an unhappy application
>demanding a
>huge server...
>
>
>
>on 10/21/03 5:21 AM, Arup Nanda at orarup_at_hotmail.com wrote:
>
>> Never. Altering the table to cache does not gurantee that it
>will be always
>> be available in the cache. It simply means the table will be
>placed in the
>> Least recently used end of the LRU list and it will age away
>as time goes
>> by, just like any other table.
>>
>> A better approach is to use KEEP pool and place teh table
>(and all other
>> tables that are accessed frequently) there. This is
>particualrly true for
>> datawarehouses wherethe lookup tables or small dimension
>tables can be
>> placed in KEEP pool.
>>
>> Ah, come tho think about it, actually there is one situation
>where I will
>> consider the CACHE option, when I restart the instance and
>want the hit
>> ratio to look good :)
>>
>> HTH.
>>
>> Arup Nanda
>>
>> ----- Original Message -----
>> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>> Sent: Tuesday, October 21, 2003 3:39 AM
>>
>>
>>> Hi all,
>>>
>>> when you would consider to put a table a cache...
>>>
>>> rgds
>>>
>>> gb
>>>
>>>
>_______________________________________________________________
>_________
>>> Want to chat instantly with your online friends? Get the
>FREE Yahoo!
>>> Messenger http://mail.messenger.yahoo.co.uk
>>> --
>>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>> --
>>> Author: =?iso-8859-1?q?Gunnar=20Berglund?=
>>> INET: oracledbasweden_at_yahoo.co.uk
>>>
>>> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>>> San Diego, California -- Mailing list and web
>hosting services
>>>
>---------------------------------------------------------------------
>>> To REMOVE yourself from this mailing list, send an E-Mail message
>>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>> the message BODY, include a line containing: UNSUB ORACLE-L
>>> (or the name of mailing list you want to be removed from). You may
>>> also send the HELP command for other information (like subscribing).
>>>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Tim Gorman
> INET: tim_at_sagelogix.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).