Administrative SQL Cookbook: BLU Buffer Pool Hit Ratios

Posted by

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.

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

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.