The buffer pool hit ratio

Buffer pool hit ratio is a measure of how often a page access (a getpage) is satisfied without requiring an I/O operation.

Begin program-specific programming interface information. You can help some of your applications and queries by making the buffer pools large enough to increase the buffer hit ratio.

Accounting reports, which are application related, show the average hit ratio for multiple occurrences of applications or threads. An accounting trace report shows the hit ratio for a single application or thread. The IBM® OMEGAMON® for Db2 Performance Expert on z/OS® buffer pool statistics report shows the hit ratio for the subsystem as a whole. For example, the buffer-pool hit ratio is shown in the BPOOL HIT RATIO (%) field in the following figure.

Figure 1. IBM OMEGAMON for Db2 Performance Expert on z/OS database buffer pool statistics (modified)
TOT4K  READ OPERATIONS       QUANTITY  TOT4K  WRITE OPERATIONS      QUANTITY
---------------------------  --------  ---------------------------  --------
BPOOL HIT RATIO (%)            73.12   BUFFER UPDATES                 220.4K
                                       PAGES WRITTEN                35169.00
GETPAGE REQUEST               1869.7K  BUFF.UPDATES/PAGES WRITTEN       6.27
GETPAGE REQUEST-SEQUENTIAL    1378.5K                                        
GETPAGE REQUEST-RANDOM         491.2K  SYNCHRONOUS WRITES               3.00
                                       ASYNCHRONOUS WRITES           5084.00
SYNCHRONOUS READS            54187.00                                       
SYNCHRON. READS-SEQUENTIAL   35994.00  PAGES WRITTEN PER WRITE I/O      5.78
SYNCHRON. READS-RANDOM       18193.00                                       
                                       HORIZ.DEF.WRITE THRESHOLD        2.00
GETPAGE PER SYN.READ-RANDOM     27.00  VERTI.DEF.WRITE THRESHOLD        0.00
                                       DM THRESHOLD                     0.00
SEQUENTIAL PREFETCH REQUEST  41800.00  WRITE ENGINE NOT AVAILABLE       0.00
SEQUENTIAL PREFETCH READS    14473.00  PAGE-INS REQUIRED FOR WRITE     45.00
PAGES READ VIA SEQ.PREFETCH    444.0K                                       
S.PRF.PAGES READ/S.PRF.READ     30.68                                       
                                                                            
LIST PREFETCH REQUESTS        9046.00                                       
LIST PREFETCH READS           2263.00                                       
PAGES READ VIA LST PREFETCH   3046.00                                       
L.PRF.PAGES READ/L.PRF.READ      1.35                                       
 
DYNAMIC PREFETCH REQUESTED    6680.00                                       
DYNAMIC PREFETCH READS         142.00                                       
PAGES READ VIA DYN.PREFETCH   1333.00                                       
D.PRF.PAGES READ/D.PRF.READ      9.39                                       
PREF.DISABLED-NO BUFFER          0.00                                       
PREF.DISABLED-NO READ ENG        0.00                                       
 
PAGE-INS REQUIRED FOR READ     460.4K

The buffer hit ratio uses the following formula to determine how many getpage operations did not require an I/O operation:

Hit ratio = ((getpage-requests - pages-not-in-bp - pages-read-from-disk) /
            (getpage-requests - pages-not-in-bp)) * 100
The formula uses the following values, from fields in the buffer pool statistics report:
  • getpage-requests is the number of getpage requests, from theGETPAGE REQUEST field.
  • pages-not-in-bp is the number of getpage requests that failed because the page was not in the buffer pool, from the COND. REQUEST - NOT FOUND field.
  • pages-read-from-disk is the sum of the values from the following fields:
    • SYNCHRONOUS READS: Number of synchronous reads
    • PAGES READ VIA SEQ.PREFETCH: Number of pages read through sequential prefetch.
    • PAGES READ VIA LST PREFETCH: Number of pages read through list prefetch.
    • PAGES READ VIA DYN.PREFETCH: Number of pages read through dynamic prefetch.

If there are 1000 getpage requests, all requested pages are in the buffer pool, and 100 pages are read from disk, the equation is as follows: 

Hit ratio = ((1000 - 0 - 100)/1000)) * 100

The hit ratio in this case is 90%.

Highest and lowest hit ratios

Highest hit ratio
The highest possible value for the hit ratio is 1.0 (for 100%), which is achieved when every page requested is always in the buffer pool. Reading index non-leaf pages tend to have a very high hit ratio since they are frequently re-referenced and thus tend to stay in the buffer pool.
Lowest hit ratio
The lowest hit ratio occurs when the requested page is not in the buffer pool; in this case, the hit ratio is 0 or less. A negative hit ratio means that prefetch has brought pages into the buffer pool that are not subsequently referenced. The pages are not referenced because either the query stops before it reaches the end of the table space or Db2 must take the pages away to make room for newer ones before the query can access them.

A low hit ratio is not always bad

While it might seem desirable to make the buffer hit ratio as close to 100% as possible, do not automatically assume a low buffer-pool hit ratio is bad. The hit ratio is a relative value, based on the type of application. For example, an application that browses huge amounts of data using table space scans might very well have a buffer-pool hit ratio of 0, or possibly even a negative number because of prefetch processing. What you want to watch for is those cases where the hit ratio drops significantly for the same application. In those cases, it might be helpful to investigate further.

Buffer Pool hit ratio is not meaningful for buffer pools that have high number of sequentially accessed objects. You can move sequentially accessed objects to a separate buffer pool to improve buffer pool hit ratio for randomly accessed objects.