Choosing the size of the
- KEEP, and
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.