Buffer Pools (^b)
This panel provides detailed information on the primary and secondary buffer pools.
|ProTop RT Label||Alertable Metric*||Description|
|Pool||N/A||Primary or Alternate buffer ppol|
|Tbls||N/A||Number of tables in the buffer pool|
|Idxs||N/A||Number of indices in the buffer pool|
|LOB||N/A||Number of LOBs in the buffer pool|
|LRU Stat||N/A||See table below|
|Buffers||N/A||Number of buffer blocks in the pool|
|Unused||N/A||Number of unused buffers in the buffer pool|
|Active||N/A||Number of active buffers in the buffer pool|
|DataBlk||N/A||Only available in late 11.x versions. Number of data blocks in the buffer pool.|
|IdxBlk||N/A||Only available in late 11.x versions. Number of index blocks in the buffer pool|
|Mstr||N/A||Only available in late 11.x versions. Number of master blocks in the buffer pool.|
|SEQ||N/A||Only available in late 11.x versions. Number of sequence blocks in the buffer pool.|
|Area||N/A||Only available in late 11.x versions. Number of area blocks in the buffer pool.|
|Ctrl||N/A||Only available in late 11.x versions. Number of control blocks in the buffer pool.|
|Obj||N/A||Only available in late 11.x versions. Number of object blocks in the buffer pool.|
|OLst||N/A||Number of object list blocks in the buffer pool|
|OCL||N/A||Number of object cluster list blocks in the buffer pool|
|CMap||N/A||Number of cluster map blocks in the buffer pool|
|Bmap||N/A||Nummber of block map blocks in the buffer pool|
|logRd||N/A||Number of logical reads from the buffer pool|
|OSRd||N/A||Number of reads from storage (disk)|
|logWr||N/A||Number of logical writes to the buffer pool|
|OSWr||N/A||Number of writes to storage (disk)|
|Hit||N/A||Percentage of read requests filled from the buffer pool|
*This panel is not alertable per se. With the commercial version of ProTop, you can configure alerts using the metric names in this column. For a full list of metrics, see the Alertable Metrics section.
LRU Status Values
|not set||The -lruskips parameter has not been specified|
|bypassed||There are still empty buffers in the buffer pool so the LRU mechanism has not yet activated|
|Skip #||The -lruskips parameter is active and set to #|
Buffer Pool Insight
The Least Recently Used chain is a linked list of all the buffers in the pool, ordered from most recently used to least recently used. When a block is accessed, it is moved to the MRU end of the chain, and when a new block must be loaded from disk, the block on the LRU end is evicted to make space for the new block. This LRU chain mechanism is not enabled until all the pool buffers have been filled, since it is only needed to decide which block to evict to make space for a new block.
On very active systems, LRU activity can cause significant CPU usage and latch contention on the LRU latch, as many processes fight to obtain the latch in order to move blocks to the MRU end of the chain.
Setting the startup parameter -lruskips <#> instructs the process to increment a counter on the block and to only move the block to the MRU end of the LRU chain when the counter reaches the value of the -lruskips parameter. This greatly reduces LRU latch activity and contention.
A typical use-case for the alternate buffer pool is to size it large enough to hold all the data and index blocks of the tables assigned to it, such that the pool never fills and there is no LRU chain at all. Hot tables are often put there: small tables that are read constantly and that we want to pin in shared memory.