RE: buffer pools

Bill -

i certainly don't profess to be an Oracle P&T expert, but i think you are on a potentially fruitful path if there is a performance issue in need of a solution. it can make sense to assign a large infrequently queried table that has a history of flushing hot blocks to a separate cache. similarly, you may want a small frequently queried table assigned to a separate cache where you know its hot blocks are more likely to stay in memory. hence the names of these caches.

i would caution you to be sure that the action you take is solving a real problem. ideally you have a test environment and can replicate a production like workload there to verify the changes you make do some good. at minimum, gather meaningful performance statistics before and after your change from production. by meaningful i do not mean BCHR ;) and don't be afraid to re-evaluate if your after statistics are not what you had hoped. all part of the fine art of P&T.

HTH.
- Steve

-----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: STEVE OLLIG
  INET: sollig_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).