Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cache a table

RE: Cache a table

From: Melanie Caffrey <mcaffrey_at_proximo.com>
Date: Tue, 21 Oct 2003 14:44:26 -0800
Message-ID: <F001.005D3E66.20031021144426@fatcity.com>

 Just to add to Alex's point below, John Beresniewicz wrote a paper some time ago (about two or three years) that effectively explains the different buffer pools, and provides test results of the mid-point insertion algorithms:  

http://www.orapub.com/cgi/genesis.cgi?p1=sub <http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs122> &p2=abs122  

If you don't already have a user account on the orapub site, it's quick to create one, and, of course, free.  

Cheers,
Melanie

-----Original Message-----
Alexander.Feinstein_at_mitchell1.com
Sent: Tuesday, October 21, 2003 5:34 PM
To: Multiple recipients of list ORACLE-L

John,Tom,

There is a difference between pools {DEFAULT vs. KEEP and RECYCLE}. By default only DEFAULT pool use "mid-point" insert. It is controlled by hidden parameters
_db_percent_hot_default (Percent of default buffer pool considered hot) default 50
_db_percent_hot_keep (Percent of keep buffer pool considered hot) default 0
_db_percent_hot_recycle (Percent of recycle buffer pool considered hot) default 0

Table's attribute "CACHE" controls whether blocks read by FTS will be placed on LRU or MRU end of the LRU list, but only up to _small_table_threshold (threshold level of table size for forget-bit enabled during scan), default is 2% of db_block_size.

Alex.

-----Original Message-----
Sent: Tuesday, October 21, 2003 12: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!

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Melanie Caffrey
  INET: mcaffrey_at_proximo.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).
Received on Tue Oct 21 2003 - 17:44:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US