RE: Cache a table

Tom,

I think you are correct, if we are talking about Oracle 9, where oracle switched from "most recently used" to "most frequently used" algorithm.

But, prior to that, it seems possible to think of scenarios, where "cache" would be helpful. May be, that's one of the reasons, why oracle changed algorithm.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Mercadante, Thomas F
Sent: Tuesday, October 21, 2003 2:09 PM
To: Multiple recipients of list ORACLE-L

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.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).