Scenario: Monitoring buffer pool efficiency using built-in administrative views

Note: The information that follows discusses buffer pools in environments other than Db2 pureScale® environments. Buffer pools work differently in Db2® pureScale environments. For more information, see Buffer pool monitoring in a Db2 pureScale environment.
John, a DBA, suspects that poor application performance in the SALES database is a result of buffer pools that function inefficiently. To investigate, he takes a look at the buffer pool hit ratio using the MON_BP_UTILIZATION administrative view: 
CONNECT TO SALES;
SELECT BP_NAME, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT,
       XDA_HIT_RATIO_PERCENT, COL_HIT_RATIO_PERCENT
  FROM SYSIBMADM.MON_BP_UTILIZATION;
John sees that the hit ratio for one of the buffer pools is very low, which means that too many pages are being read from disk instead of being read from the buffer pool.
He then decides to use the MON_BP_UTILIZATION administrative view to see whether the prefetchers require tuning: 
CONNECT TO SALES;
SELECT BP_NAME, PREFETCH_RATIO_PERCENT FROM SYSIBMADM.MON_BP_UTILIZATION;
The value of PREFETCH_RATIO_PERCENT tells him the percentage of pages read asynchronously with prefetching. A low value indicates that a high percentage of data is being read directly from disk, and might indicate that more prefetchers are required.
Since the value for PREFETCH_RATIO_PERCENT seems within the acceptable range, John uses the MON_BP_UTILIZATION administrative view to investigate how well the page cleaners are working to clear space for incoming data pages: 
CONNECT TO SALES;
SELECT BP_NAME, AVG_WRITE_TIME, SYNC_WRITES_PERCENT,
       AVG_SYNC_WRITE_TIME, AVG_ASYNC_WRITE_TIME
  FROM SYSIBMADM.MON_BP_UTILIZATION;

The value of SYNC_WRITES_PERCENT tells John what percentage of physical write requests that were performed synchronously. If this number is low, it might means that the page cleaners are working well to clear space in the buffer pool ahead of incoming requests for new data pages. If this number is high, then a higher number of physical writes are being performed by database agents while an application waits for data a data page to be read into the buffer pool.

John sees that the value of SYNC_WRITES_PERCENT is 75 percent, so he decides to configure more page cleaners for the SALES database to increase the rate of asynchronous writes. After increasing the number of page cleaners, he can use the buffer pool administrative views again to see the effects of his tuning.