Calculating buffer pool hit ratios in a Db2 pureScale environment

Example

Example 1: Find the overall hit rates across all members

This example is similar to the one shown in the preceding procedure, except that it uses an aggregate function to provide overall hit rates across all members.

SELECT VARCHAR(BP_NAME,20) AS BP, 
       SUM(POOL_DATA_GBP_L_READS) AS POOL_DATA_GBP_L_READS, 
       SUM(POOL_DATA_GBP_P_READS) AS POOL_DATA_GBP_P_READS 
FROM TABLE(MON_GET_BUFFERPOOL('',-2))
GROUP BY BP_NAME

Results:

BP                   POOL_DATA_GBP_L_READS POOL_DATA_GBP_P_READS
-------------------- --------------------- ---------------------
IBMDEFAULTBP                       6550198               1646347
IBMSYSTEMBP16K                           0                     0
IBMSYSTEMBP32K                           0                     0
IBMSYSTEMBP4K                            0                     0
IBMSYSTEMBP8K                            0                     0

  5 record(s) selected.

Example 2: Determining the GBP hit ratio for all data, index, and XML storage object (XDA) pages

To calculate the GBP hit ratio for all data, index, and XDA pages, use the following formula:

((pool_data_gbp_l_reads + pool_index_gbp_l_reads + pool_xda_gbp_l_reads)
  - (pool_data_gbp_p_reads + pool_index_gbp_p_reads + pool_xda_gbp_p_reads ))
  ÷ (pool_data_gbp_l_reads + pool_index_gbp_l_reads + pool_xda_gbp_l_reads) × 100

The following example uses the MON_GET_BUFFERPOOL table function to retrieve the data contained in the required monitor elements and calculates the hit ratio for each member:

WITH BPMETRICS AS (
     SELECT BP_NAME, 
            POOL_DATA_GBP_L_READS +
              POOL_INDEX_GBP_L_READS +
              POOL_XDA_GBP_L_READS 
            AS LOGICAL_READS, 
            POOL_DATA_GBP_P_READS + 
              POOL_INDEX_GBP_P_READS + 
              POOL_XDA_GBP_P_READS 
            AS PHYSICAL_READS,
            MEMBER 
     FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
     SELECT VARCHAR(BP_NAME,20) AS BP_NAME,
            LOGICAL_READS,
            PHYSICAL_READS,
     CASE WHEN LOGICAL_READS > 0 
          THEN DEC(((
            FLOAT(LOGICAL_READS) - FLOAT(PHYSICAL_READS)) / 
            FLOAT(LOGICAL_READS)) 
            * 100,5,2) 
          ELSE NULL END AS HIT_RATIO,
          MEMBER
     FROM BPMETRICS

Results:

BP_NAME          LOGICAL_READS PHYSICAL_READS HIT_RATIO MEMBER
---------------- ------------- -------------- --------- ------
IBMDEFAULTBP           5730213         617628     89.22      1
IBMSYSTEMBP4K                0              0         -      1
IBMSYSTEMBP8K                0              0         -      1
IBMSYSTEMBP16K               0              0         -      1
IBMSYSTEMBP32K               0              0         -      1
IBMDEFAULTBP           5724845         615395     89.25      3
IBMSYSTEMBP4K                0              0         -      3
IBMSYSTEMBP8K                0              0         -      3
IBMSYSTEMBP16K               0              0         -      3
IBMSYSTEMBP32K               0              0         -      3
IBMDEFAULTBP           5731714         615814     89.25      2
IBMSYSTEMBP4K                0              0         -      2
IBMSYSTEMBP8K                0              0         -      2
IBMSYSTEMBP16K               0              0         -      2
IBMSYSTEMBP32K               0              0         -      2
IBMDEFAULTBP           5024809         409159     91.85      0
IBMSYSTEMBP4K                0              0         -      0
IBMSYSTEMBP8K                0              0         -      0
IBMSYSTEMBP16K               0              0         -      0
IBMSYSTEMBP32K               0              0         -      0

  20 record(s) selected.

Example 3: Using the SUM aggregate function to compute an overall hit ratio

You can also use the SUM aggregate function to compute an overall hit ratio across all members as follows:

WITH BPMETRICS AS (
     SELECT SUM(POOL_DATA_GBP_L_READS) + 
       SUM(POOL_INDEX_GBP_L_READS) + 
       SUM(POOL_XDA_GBP_L_READS) 
     AS LOGICAL_READS, 
     SUM(POOL_DATA_GBP_P_READS) + 
       SUM(POOL_INDEX_GBP_P_READS) + 
       SUM(POOL_XDA_GBP_P_READS) 
     AS PHYSICAL_READS 
     FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
     SELECT LOGICAL_READS,
            PHYSICAL_READS,
     CASE WHEN LOGICAL_READS > 0 
     THEN DEC(((FLOAT(LOGICAL_READS) - FLOAT(PHYSICAL_READS)) / 
       FLOAT(LOGICAL_READS)) 
       * 100,5,2) 
     ELSE NULL END AS HIT_RATIO 
FROM BPMETRICS

Results:

LOGICAL_READS        PHYSICAL_READS       HIT_RATIO
-------------------- -------------------- ---------
            22211581              2255996     89.84

  1 record(s) selected.