Formulas for calculating buffer pool hit ratios

Buffer pool hit ratios reflect the extent to which data needed for queries is found in memory, as opposed to having to be read in from external storage. You can calculate hit rates and ratios with formulas that are based on buffer pool monitor elements.

Local buffer pools

Table 1. Formulas for local buffer pool hit ratios. The formulas shown express the hit ratios as a percentage.
Type of page	Formula for calculating buffer pool hit ratio
Data pages	((pool_data_lbp_pages_found - pool_async_data_lbp_pages_found) / (pool_data_l_reads + pool_temp_data_l_reads)) × 100
Index pages	((pool_index_lbp_pages_found - pool_async_index_lbp_pages_found ) / (pool_index_l_reads + pool_temp_index_l_reads)) × 100
Column-organized pages	((pool_col_lbp_pages_found - pool_async_col_lbp_pages_found ) / (pool_col_l_reads + pool_temp_col_l_reads)) × 100
XML storage object (XDA) pages	((pool_xda_lbp_pages_found - pool_async_xda_lbp_pages_found ) / (pool_xda_l_reads + pool_temp_xda_l_reads)) × 100
Overall hit ratio	((pool_data_lbp_pages_found + pool_index_lbp_pages_found + pool_xda_lbp_pages_found + pool_col_lbp_pages_found - pool_async_data_lbp_pages_found - pool_async_index_lbp_pages_found - pool_async_xda_lbp_pages_found - pool_async_col_lbp_pages_found) / (pool_data_l_reads + pool_index_l_reads + pool_xda_l_reads + pool_col_l_reads + pool_temp_data_l_reads + pool_temp_xda_l_reads + pool_temp_index_l_reads + pool_temp_col_l_reads)) × 100

Group buffer pools (Db2 pureScale environments)

The formulas used to calculate group buffer pool hit ratios in a Db2® pureScale® environment are different from formulas for hit ratios used in other database environments. This difference is because of how the group buffer pool in the cluster caching facility works with the local buffer pools in each member to retrieve pages of data. The following formulas, which are based on buffer pool monitor elements, can be used to calculate hit ratios for data, index, and XML storage object pages, for both the local and group buffer pools.

Table 2. Formulas for group buffer pool (GBP) hit ratios. The formulas shown express the hit ratios as a percentage.
Type of page	Formula for calculating buffer pool hit ratio
Data pages	((pool_data_gbp_l_reads - pool_data_gbp_p_reads ) / pool_data_gbp_l_reads) × 100
Index pages	((pool_index_gbp_l_reads - pool_index_gbp_p_reads ) / pool_index_gbp_l_reads) × 100
XML storage object (XDA) pages	((pool_xda_gbp_l_reads - pool_xda_gbp_p_reads ) / pool_xda_gbp_l_reads) × 100
Column-organized pages	((pool_col_gbp_l_reads - pool_col_gbp_p_reads ) / pool_col_gbp_l_reads) × 100
Overall hit ratio	((pool_data_gbp_l_reads + pool_index_gbp_l_reads + pool_col_gbp_l_reads + pool_xda_gbp_l_reads - pool_data_gbp_p_reads - pool_index_gbp_p_reads - pool_col_gbp_p_reads - pool_xda_gbp_p_reads) / (pool_data_gbp_l_reads + pool_index_gbp_l_reads + pool_col_gbp_l_reads + pool_xda_gbp_l_reads)) x 100

In addition to the preceding formulas for calculating buffer pool hit ratios, you can also use the following formulas to show what percentage of the time pages that are prefetched are found in the GBP:

Prefetches for data pages: ((pool_async_data_gbp_l_reads - pool_async_data_gbp_p_reads) / pool_async_data_gbp_l_reads) × 100
Prefetches for index pages: ((pool_async_index_gbp_l_reads - pool_async_index_gbp_p_reads ) / pool_async_index_gbp_l_reads) × 100
Prefetches for column-organized pages: ((pool_async_col_gbp_l_reads - pool_async_col_gbp_p_reads) / pool_async_col_gbp_l_reads) × 100
Prefetches for XML storage object (XDA) pages: ((pool_async_xda_gbp_l_reads - pool_async_xda_gbp_p_reads ) / pool_async_xda_gbp_l_reads) × 100