RE: buffer pools

Bill

   I don't think too many analytical studies have been done on multiple buffer pools, but since I am extremely interested in this subject, I'll provide my opinion, which is worth every bit you paid for it.

   Just dividing the buffer pool into three pieces, in my opinion, would degrade performance. The reason is you are denying Oracle the opportunity to use the total space most efficiently. The normal situation would probably be one buffer being overtaxed and the others undertaxed.

   So, why would we use three buffer pools? Well the answer must be that we have knowledge about the tables and their usage that Oracle doesn't have access to. The only thing Oracle knows about a data or index block is when it was last used and whether it was retrieved as a distinct select or a table scan.

   The usual philosophy is that you can pick some small tables that are used a lot and tell Oracle "keep the blocks from this table in memory". Hence the KEEP pool. Again, not just any small tables, but the ones that are hit often.

   Then you pick some really vast tables whose access pattern is broad. That is, no "hot" blocks. You tell Oracle "don't bother to keep blocks from this table in memory". Hence the RECYCLE pool.

   I've tried applying this philosophy, and it seems to help some, but hasn't made a vast difference. But I am very receptive to alternate philosophies, which is why I am responding to your question.

   Part of the problem is the usage pattern of the data changes frequently. Somebody kicks off a report that needs a lot of blocks that haven't been used for awhile. That is the fallacy of measuring the Buffer Hit Ratio. As you take successive measurements, it changes quite dynamically. So when you really make a change that will affect BHR, you must take quite a few measurements over time to confirm the effect of the change. Ideally you would apply some statistics principles to compare two samples of measurements and determine if they are indeed different. And of course keep track of the wait statistics to make sure your BHR measurements are valid ;-)

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
[mailto:becker.bill_at_marshfieldclinic.org] Sent: Wednesday, February 12, 2003 9:30 AM To: Multiple recipients of list ORACLE-L

Hello,

Env: 9.2.0.2 on Solaris 2.9

We are currently considering a proposal regarding the use of the 3 buffer pools represented by db_cache_size, db_keep_cache_size, and db_recycle_cache_size. I am wondering if this is a good idea or a bad idea. The proposal follows.

>>

The buffer pool space can be divided into 3 separate pools: db_cache_size, db_keep_cache_size, and db_recycle_cache_size. Despite the names, the blocks are all treated exactly the same with regard to the Least Recently Used (LRU) algorithm. The retention time of a database block in any of these pools is dependent upon the size of the pool, how often the block is referenced, and the probability of the block being displaced by a more popular block.

The names Oracle has assigned to these pools reflect more of an intention than anything else; the keep pool is intended to be sized large enough to retain all frequently-referenced data; the recycle pool is intended to be sized small to recycle blocks not desired in memory, and the db_cache_size
pool is intended for everything else.

Funtionally, Oracle could have named these db_cache1, db_cache2 and db_cache3.

Currently, we utilize just 1 cache, the db_cache_size. I am proposing that we utilize all 3 caches in some way; the rationale for this is that it is better to
have 3 smaller caches of 800M, each managing 1000 objects, than it is to have 1 large
cache of 2.4G managing 3000 objects.
>>

The rest of this proposal suggests a method for distributing the various tables
and indexes in our system to the 3 caches; it suggests a roughly equal division
among the 3 caches based upon subject area and usage stats.

1. Is the information above accurate?
2. Is it better to have 3 smaller caches than 1 large cache? (Assuming the objects are distributed among the caches about equally based upon usage stats)

Thanks to those who read this far.
More thanks to any responders.
Most thanks to responders with helpful suggestions.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: becker.bill_at_marshfieldclinic.org

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: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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).