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