Size multiple buffer pools in Oracle

When sizing multiple buffer pools in Oracle, the primary objective is to optimize the usage of memory resources to improve overall database performance. Buffer pools help manage data and index blocks in the Oracle database. Oracle provides three different buffer pools:

1. the default buffer pool,
2. the KEEP buffer pool, and
3. the RECYCLE buffer pool.

Here is how to size multiple buffer pools when tuning the instance:

1. Analyze the workload and access patterns: Before sizing the buffer pools, gather information about the database's workload, access patterns, and the types of objects that are frequently accessed. Tools like Automatic Workload Repository (AWR) reports, Automatic Database Diagnostic Monitor (ADDM), and Statspack can provide insights into object usage patterns and performance statistics.
2. Determine the size of the default buffer pool: The default buffer pool is used for all objects that don't have specific assignments to the KEEP or RECYCLE buffer pools. To size the default buffer pool, consider the following factors:
   
   1. Amount of available memory on the system.
   2. The database's overall buffer cache hit ratio.
   3. The size of the database and the types of objects in use.
   4. The expected workload and growth rate of the database. The DB_CACHE_SIZE initialization parameter sets the size of the default buffer pool.
3. Size the KEEP buffer pool: The KEEP buffer pool is intended for objects that should remain in memory for optimal performance. To size the KEEP buffer pool, consider the following:
   1. Identify frequently accessed objects, such as small lookup tables or frequently used indexes.
   2. Calculate the total size of these objects.
   3. Set the size of the KEEP buffer pool using the DB_KEEP_CACHE_SIZE initialization parameter. Keep in mind that making the KEEP buffer pool too large may result in wasted memory, while making it too small may lead to performance issues.
4. Size the RECYCLE buffer pool: The RECYCLE buffer pool is designed for objects that are accessed infrequently or have a low reuse rate. These objects should not be kept in the buffer cache to avoid displacing more frequently accessed objects. To size the RECYCLE buffer pool, consider the following:
   
   1. Identify infrequently accessed objects that have a low buffer cache hit ratio.
   2. Calculate the total size of these objects.
   3. Set the size of the RECYCLE buffer pool using the DB_RECYCLE_CACHE_SIZE initialization parameter.
      Be cautious not to make the RECYCLE buffer pool too large, as it could result in wasted memory.
5. Monitor and adjust: Regularly monitor the performance of the buffer pools using tools like AWR, ADDM, and Statspack. These tools can help you identify any issues with the buffer pool sizes and provide recommendations for adjustments. Be prepared to modify buffer pool sizes as the database workload changes over time.

Remember that sizing multiple buffer pools is an iterative process, and it's essential to continually monitor and adjust the settings based on your database's changing workload and performance requirements.

Choosing the size of the

1. DEFAULT,
2. KEEP, and
3. RECYCLE

pools is an iterative process. Initially, you will configure your pools with a large amount for the DEFAULT pool and a small amount for your KEEP and RECYCLE pools. As you identify tables and indices that belong in the KEEP and RECYCLE pools, you can adjust your init.ora parameters and then assign the tables or indices to the appropriate buffer pool with the STORAGE clause.
It is recommend that you begin with a small allocation to the KEEP and RECYCLE pools and then add to the pool as you identify tables that should be isolated from the DEFAULT pool. As you add tables to the KEEP and RESERVED pools, you can monitor their buffer hit ratio and determine if an increase in size is appropriate.
However, bear in mind that the RECYCLE pool buffer hit ratio is supposed to be low. By definition, tables assigned to the RECYCLE pool should be those tables that are infrequent re-reads of the same data blocks.
The buffer hit ratio for the KEEP pool is of foremost importance, even more than the DEFAULT pool. Since the KEEP pool catches blocks that are re-read very frequently, a declining buffer hit ratio for this pool indicates that additional data block buffer should be allocated at the next opportunity.

The Oracle® Database Administrator's Reference 10g Release 2 (10.2) for UNIX-Based Operating Systems notes these guidelines for choosing the best Oracle blocksizes:
Oracle recommends smaller Oracle Database block sizes (2 KB or 4 KB) for online transaction processing (OLTP) or mixed workload environments and larger block sizes (8 KB, 16 KB, or 32 KB) for decision support system (DSS) workload environments.
The Oracle 11.2 Database Performance Tuning Guide notes the advantages and disadvantages of different blocksizes:
V$BUFFER_POOL displays information about all buffer pools available for the instance.