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:
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 5:34 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Cache a tableJohn,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 0Table'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-----
From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 21, 2003 12:09 PM
To: Multiple recipients of list ORACLE-L
Subject: 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:[EMAIL PROTECTED]]
>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 [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]>
>> Sent: Tuesday, October 21, 2003 3:39 AM
>>
>>
>>> Hi all,
>>>
>>> when you would consider to put a table a cache...
>>>
>>> rgds
>>>
>>> gb
>>>
>>>
