Administrative SQL Cookbook: BLU Buffer Pool Hit Ratios

Purpose

These statements calculates the buffer pool hit ratio for both BLU and non-BLU activity. This post includes three SQLs – for calculating at the database, bufferpool, and tablespace level. The separate BLU calculations are not yet included in sysibadm.bp_hitratio as of the writing of this article.

Source

While I’ve modified this statement, it comes from these two sources, both influenced by David Kalmuk:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1407monitor-bluaccel/index.html
http://www.dbisoftware.com/blog/db2nightshow.php?id=619

Statement for Database Level

WITH POOL_DATA( ROW_DATA_PAGES_FOUND,
                COL_DATA_PAGES_FOUND,
                DATA_PAGES_FOUND,
                ROW_DATA_L_READS,
                COL_DATA_L_READS,
                DATA_L_READS )
AS (SELECT SUM(POOL_DATA_LBP_PAGES_FOUND -
               POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS ROW_DATA_PAGES_FOUND,
           SUM(POOL_COL_LBP_PAGES_FOUND -
               POOL_ASYNC_COL_LBP_PAGES_FOUND) AS COL_DATA_PAGES_FOUND,
           SUM(POOL_DATA_LBP_PAGES_FOUND +
               POOL_COL_LBP_PAGES_FOUND -
               POOL_ASYNC_DATA_LBP_PAGES_FOUND -
               POOL_ASYNC_COL_LBP_PAGES_FOUND) AS DATA_PAGES_FOUND, 
           SUM(POOL_DATA_L_READS +
               POOL_TEMP_DATA_L_READS) AS ROW_DATA_L_READS,
           SUM(POOL_COL_L_READS +
               POOL_TEMP_COL_L_READS) AS COL_DATA_L_READS,
           SUM(POOL_DATA_L_READS +
               POOL_COL_L_READS +
               POOL_TEMP_DATA_L_READS +
               POOL_TEMP_COL_L_READS) AS DATA_L_READS
     FROM TABLE(MON_GET_DATABASE(-2)) AS T)
SELECT CASE WHEN DATA_L_READS > 0
            THEN DEC((FLOAT(DATA_PAGES_FOUND) / 
                      FLOAT(DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS DATA_PAGE_HIT_RATIO,
       CASE WHEN ROW_DATA_L_READS > 0
            THEN DEC((FLOAT(ROW_DATA_PAGES_FOUND) /
                      FLOAT(ROW_DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS ROW_DATA_PAGE_HIT_RATIO,
       CASE WHEN COL_DATA_L_READS > 0
            THEN DEC((FLOAT(COL_DATA_PAGES_FOUND) /
                      FLOAT(COL_DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS COL_DATA_PAGE_HIT_RATIO 
FROM POOL_DATA
with ur;

Sample Output for BPHR at the Database Level

DATA_PAGE_HIT_RATIO ROW_DATA_PAGE_HIT_RATIO COL_DATA_PAGE_HIT_RATIO
------------------- ----------------------- -----------------------
              98.61                   99.68                   94.98

  1 record(s) selected.

Statement for Buffer Pool Level

WITH POOL_DATA( BP_NAME,
        ROW_DATA_PAGES_FOUND,
                COL_DATA_PAGES_FOUND,
                DATA_PAGES_FOUND,
                ROW_DATA_L_READS,
                COL_DATA_L_READS,
                DATA_L_READS )
AS (SELECT substr(BP_NAME,1,18),
       (POOL_DATA_LBP_PAGES_FOUND -
               POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS ROW_DATA_PAGES_FOUND,
           (POOL_COL_LBP_PAGES_FOUND -
               POOL_ASYNC_COL_LBP_PAGES_FOUND) AS COL_DATA_PAGES_FOUND,
           (POOL_DATA_LBP_PAGES_FOUND +
               POOL_COL_LBP_PAGES_FOUND -
               POOL_ASYNC_DATA_LBP_PAGES_FOUND -
               POOL_ASYNC_COL_LBP_PAGES_FOUND) AS DATA_PAGES_FOUND, 
           (POOL_DATA_L_READS +
               POOL_TEMP_DATA_L_READS) AS ROW_DATA_L_READS,
           (POOL_COL_L_READS +
               POOL_TEMP_COL_L_READS) AS COL_DATA_L_READS,
           (POOL_DATA_L_READS +
               POOL_COL_L_READS +
               POOL_TEMP_DATA_L_READS +
               POOL_TEMP_COL_L_READS) AS DATA_L_READS
     FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS T)
SELECT  BP_NAME,
    CASE WHEN DATA_L_READS > 0
            THEN DEC((FLOAT(DATA_PAGES_FOUND) / 
                      FLOAT(DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS DATA_PAGE_HIT_RATIO,
       CASE WHEN ROW_DATA_L_READS > 0
            THEN DEC((FLOAT(ROW_DATA_PAGES_FOUND) /
                      FLOAT(ROW_DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS ROW_DATA_PAGE_HIT_RATIO,
       CASE WHEN COL_DATA_L_READS > 0
            THEN DEC((FLOAT(COL_DATA_PAGES_FOUND) /
                      FLOAT(COL_DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS COL_DATA_PAGE_HIT_RATIO 
FROM POOL_DATA
with ur;

Sample Output for BPHR at the Database Level

BP_NAME            DATA_PAGE_HIT_RATIO ROW_DATA_PAGE_HIT_RATIO COL_DATA_PAGE_HIT_RATIO
------------------ ------------------- ----------------------- -----------------------
IBMDEFAULTBP                     99.97                   99.97                       -
BUFF32K                          94.95                   98.74                   94.70
BUFF16K                          99.98                   99.98                   99.98
BUFF8K                           99.24                   99.24                       -
BUFF4K                           99.13                   99.13                       -
IBMSYSTEMBP4K                        -                       -                       -
IBMSYSTEMBP8K                        -                       -                       -
IBMSYSTEMBP16K                       -                       -                       -
IBMSYSTEMBP32K                       -                       -                       -

  9 record(s) selected.

Statement for Tablespace Level

WITH POOL_DATA( TBSP_NAME,
        ROW_DATA_PAGES_FOUND,
                COL_DATA_PAGES_FOUND,
                DATA_PAGES_FOUND,
                ROW_DATA_L_READS,
                COL_DATA_L_READS,
                DATA_L_READS )
AS (SELECT substr(TBSP_NAME,1,30),
       (POOL_DATA_LBP_PAGES_FOUND -
               POOL_ASYNC_DATA_LBP_PAGES_FOUND) AS ROW_DATA_PAGES_FOUND,
           (POOL_COL_LBP_PAGES_FOUND -
               POOL_ASYNC_COL_LBP_PAGES_FOUND) AS COL_DATA_PAGES_FOUND,
           (POOL_DATA_LBP_PAGES_FOUND +
               POOL_COL_LBP_PAGES_FOUND -
               POOL_ASYNC_DATA_LBP_PAGES_FOUND -
               POOL_ASYNC_COL_LBP_PAGES_FOUND) AS DATA_PAGES_FOUND, 
           (POOL_DATA_L_READS +
               POOL_TEMP_DATA_L_READS) AS ROW_DATA_L_READS,
           (POOL_COL_L_READS +
               POOL_TEMP_COL_L_READS) AS COL_DATA_L_READS,
           (POOL_DATA_L_READS +
               POOL_COL_L_READS +
               POOL_TEMP_DATA_L_READS +
               POOL_TEMP_COL_L_READS) AS DATA_L_READS
     FROM TABLE(MON_GET_TABLESPACE('',-2)) AS T)
SELECT  TBSP_NAME,
    ROW_DATA_L_READS,
    COL_DATA_L_READS,
    CASE WHEN DATA_L_READS > 0
            THEN DEC((FLOAT(DATA_PAGES_FOUND) / 
                      FLOAT(DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS DATA_PAGE_HIT_RATIO,
       CASE WHEN ROW_DATA_L_READS > 0
            THEN DEC((FLOAT(ROW_DATA_PAGES_FOUND) /
                      FLOAT(ROW_DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS ROW_DATA_PAGE_HIT_RATIO,
       CASE WHEN COL_DATA_L_READS > 0
            THEN DEC((FLOAT(COL_DATA_PAGES_FOUND) /
                      FLOAT(COL_DATA_L_READS)) * 100, 5, 2)
            ELSE NULL
       END AS COL_DATA_PAGE_HIT_RATIO 
FROM POOL_DATA
with ur;

Sample Output for BPHR at the Tablespace Level

TBSP_NAME                      ROW_DATA_L_READS     COL_DATA_L_READS     DATA_PAGE_HIT_RATIO ROW_DATA_PAGE_HIT_RATIO COL_DATA_PAGE_HIT_RATIO
------------------------------ -------------------- -------------------- ------------------- ----------------------- -----------------------
SYSCATSPACE                               112771783                    0               99.92                   99.92                       -
TEMPSPACE1                                273494060                    0               99.99                   99.99                       -
USERSPACE1                                      265                    0               83.01                   83.01                       -
LOBSPACE1                                     13381                    0               80.24                   80.24                       -
CMIPS_TS16K                                83283779             11317952               99.98                   99.98                   99.98
AG_IE_RSLT_TS                                512863              4686219               82.48                   99.84                   80.58
AG_IE_RSN_TS                                    411                  280               79.59                   84.42                   72.50
TAB16K                                          265                    0               99.24                   99.24                       -
SSIRS_LG_TBL_TS                               38312              8598834               72.95                   71.75                   72.96
CIS_ODS_LG_TBL_TS                            226824              1900955               82.21                   99.63                   80.13
WF_ODS_LG_TBL_TS                              13168             12020654               67.19                   97.71                   67.16
CIS_ODS_TS32K                               6848238             11398098               96.43                   99.71                   94.45
SSIRS_TS32K                                 4104862             76690676               99.44                   98.72                   99.48
EBT_ODS_TS32K                                  9933              5329227               74.42                   90.58                   74.39
WEBFILES_ODS_TS32K                             1212               177281               92.36                   85.23                   92.41
SMART_ODS_TS32K                                3311                 9262               76.31                   94.23                   69.90
CWS_ODS32K                                  1471139             75392240               99.75                   97.88                   99.78
GIS_UTIL_TS32K                                89161              1769118               85.44                   56.74                   86.88
TAB4K                                     224206209                    0               99.13                   99.13                       -
TAB8K                                           265                    0               99.24                   99.24                       -
TAB32K                                        70847              1170441               84.40                   87.02                   84.24
SSIRS_DMART_STG_TS                              265                    0               77.73                   77.73                       -
SYSTOOLSPACE                                3061926                    0               99.82                   99.82                       -
SYSTOOLSTMPSPACE                                  0                    0                   -                       -                       -

  24 record(s) selected.
Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.