Monitor Buffer Cache Usage
- KEEP POOL: Pool for small, frequently referenced table blocks
- RECYCLE POOL: Pool for data blocks that are read with a full-table scan
- STORAGE POOL: Pool for Oracle locks, latches, and SQL
- DEFAULT POOL: Pool for Oracle data blocks that do not meet the criteria for the KEEP or RECYCLE pools
Configuring the KEEP Pool
The purpose of the KEEP buffer pool is to retain objects in memory, thus avoiding I/O operations. Each object kept in memory results in a trade-off. It is more beneficial to keep frequently-accessed blocks in the cache.
One should avoid retaining infrequently-used blocks in the cache, as this results in less space for more active blocks.
If there are certain segments in your application that are referenced frequently, then consider storing the blocks from those segments in the KEEP buffer pool. Typical segments that are kept in the KEEP pool are small, frequently-used reference tables. To determine which tables are candidates, check the number of blocks from candidate tables by querying the V$BH view.
If there are certain segments in your application that are referenced frequently, then consider storing the blocks from those segments in the KEEP buffer pool. Typical segments that are kept in the KEEP pool are small, frequently-used reference tables. To determine which tables are candidates, check the number of blocks from candidate tables by querying the V$BH view.
To configure the KEEP pool:
- Compute an approximate size for the KEEP buffer pool. The size of the KEEP buffer pool depends on the objects to be kept in the buffer cache. To estimate its size, add the blocks used by all objects assigned to this pool. If you gathered statistics on the segments, query DBA_TABLES.BLOCKS and DBA_TABLES.EMPTY_BLOCKS to determine the number of blocks used.
- Taking two snapshots of system performance at different times. Query data from the KEEP pool for each snapshot using the V $DB_CACHE_ADVICE view
Examining the Buffer Cache Usage Pattern
The V$BH view shows the data object ID of all blocks that currently reside in the SGA. To determine which segments have many buffers in the pool, use this view to examine the buffer cache usage pattern. You can either examine the buffer cache usage pattern
for all segments or a specific segment, as described in the following sections:
Examining Buffer Cache Usage Pattern for All Segments
One method to determine which segments have many buffers in the pool is to query the number of blocks for all segments that reside in the buffer cache at a given time. Depending on buffer cache size, this might require a lot of sort space.
Example 4-3: shows a query that counts the number of blocks for all segments.
