DB2 Administrative SQL Cookbook: Finding Problem SQL in the Package Cache

I wrote a developerWorks article on finding problem SQL in your package cache. But I refine and play with my SQL over time, so I thought I’d share the version I’ve been using recently.

Purpose

To find problem SQL in the Package Cache across several importance performance categories. Computes how bad a particular statement is in comparison to other statements in the package cache by computing what percentage of a certain resource is used by a particular query.

Version

This SQL has been tested on DB2 9.7 fixpack 9a. It should work on any version of DB2 from 9.7 up. See below for syntax that works on DB2 9.1

Statement #1 – DB2 9.7 and up

WITH SUM_TAB (SUM_RR, SUM_CPU, SUM_EXEC, SUM_SORT, SUM_NUM_EXEC) AS (
        SELECT  FLOAT(SUM(ROWS_READ)),
                FLOAT(SUM(TOTAL_CPU_TIME)),
                FLOAT(SUM(STMT_EXEC_TIME)),
                FLOAT(SUM(TOTAL_SECTION_SORT_TIME)),
                FLOAT(SUM(NUM_EXECUTIONS))
            FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T
        )
SELECT
        SUBSTR(STMT_TEXT,1,10) as STATEMENT,
        ROWS_READ,
        DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) AS PCT_TOT_RR,
        TOTAL_CPU_TIME,
        DECIMAL(100*(FLOAT(TOTAL_CPU_TIME)/SUM_TAB.SUM_CPU),5,2) AS PCT_TOT_CPU,
        STMT_EXEC_TIME,
        DECIMAL(100*(FLOAT(STMT_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) AS PCT_TOT_EXEC,
        TOTAL_SECTION_SORT_TIME,
        DECIMAL(100*(FLOAT(TOTAL_SECTION_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) AS PCT_TOT_SRT,
        NUM_EXECUTIONS,
        DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) AS PCT_TOT_EXEC,
        DECIMAL(FLOAT(STMT_EXEC_TIME)/FLOAT(NUM_EXECUTIONS),10,2) AS AVG_EXEC_TIME
    FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T, SUM_TAB
    WHERE DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) > 10
        OR DECIMAL(100*(FLOAT(TOTAL_CPU_TIME)/SUM_TAB.SUM_CPU),5,2) >10
        OR DECIMAL(100*(FLOAT(STMT_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) >10
        OR DECIMAL(100*(FLOAT(TOTAL_SECTION_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) >10
        OR DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) >10
    ORDER BY ROWS_READ DESC FETCH FIRST 20 ROWS ONLY WITH UR;

Statement #2 – for DB2 9.1 and 9.5

WITH SUM_TAB (SUM_RR, SUM_CPU, SUM_EXEC, SUM_SORT, SUM_NUM_EXEC) AS (
        SELECT  FLOAT(SUM(ROWS_READ)),
                FLOAT(SUM(TOTAL_USR_CPU_TIME)) + FLOAT(SUM(TOTAL_SYS_CPU_TIME)),
                FLOAT(SUM(TOTAL_EXEC_TIME)),
                FLOAT(SUM(TOTAL_SORT_TIME)),
                FLOAT(SUM(NUM_EXECUTIONS))
            FROM SYSIBMADM.SNAPDYN_SQL AS T
        )
SELECT
        SUBSTR(STMT_TEXT,1,10) as STATEMENT,
        ROWS_READ,
        DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) AS PCT_TOT_RR,
        TOTAL_USR_CPU_TIME + TOTAL_SYS_CPU_TIME as TOTAL_CPU_TIME,
        DECIMAL(100*((FLOAT(TOTAL_USR_CPU_TIME)+FLOAT(TOTAL_SYS_CPU_TIME))/SUM_TAB.SUM_CPU),5,2) AS PCT_TOT_CPU,
        TOTAL_EXEC_TIME,
        DECIMAL(100*(FLOAT(TOTAL_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) AS PCT_TOT_EXEC,
        TOTAL_SORT_TIME,
        DECIMAL(100*(FLOAT(TOTAL_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) AS PCT_TOT_SRT,
        NUM_EXECUTIONS,
        DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) AS PCT_TOT_EXEC,
        DECIMAL(FLOAT(TOTAL_EXEC_TIME)/FLOAT(NUM_EXECUTIONS),10,2) AS AVG_EXEC_TIME
    FROM SYSIBMADM.SNAPDYN_SQL AS T, SUM_TAB
    WHERE DECIMAL(100*(FLOAT(ROWS_READ)/SUM_TAB.SUM_RR),5,2) > 10
        OR DECIMAL(100*((FLOAT(TOTAL_USR_CPU_TIME)+FLOAT(TOTAL_SYS_CPU_TIME))/SUM_TAB.SUM_CPU),5,2)  >10
        OR DECIMAL(100*(FLOAT(TOTAL_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) >10
        OR DECIMAL(100*(FLOAT(TOTAL_SORT_TIME)/SUM_TAB.SUM_SORT),5,2) >10
        OR DECIMAL(100*(FLOAT(NUM_EXECUTIONS)/SUM_TAB.SUM_NUM_EXEC),5,2) >10
    ORDER BY ROWS_READ DESC FETCH FIRST 20 ROWS ONLY WITH UR;

Sample Output

STATEMENT  ROWS_READ            PCT_TOT_RR TOTAL_CPU_TIME       PCT_TOT_CPU STMT_EXEC_TIME       PCT_TOT_EXEC TOTAL_SECTION_SORT_TIME PCT_TOT_SRT NUM_EXECUTIONS       PCT_TOT_EXEC AVG_EXEC_TIME
---------- -------------------- ---------- -------------------- ----------- -------------------- ------------ ----------------------- ----------- -------------------- ------------ -------------
SELECT ACA              5671140      81.71               657030        1.13                 1422         0.76                       0        0.00                 1860         6.92          0.76
WITH SYSIB               865658      12.47              1813570        3.12                 2903         1.56                       4        4.04                  214         0.79         13.56
select sna                62808       0.90                17975        0.03                   29         0.01                      13       13.13                   16         0.05          1.81
CALL SYSIB                 9418       0.13             17493719       30.18                45506        24.50                       0        0.00                 2121         7.89         21.45
SELECT T.t                 6600       0.09               383749        0.66                  617         0.33                      62       62.62                 2200         8.18          0.28
select str                 4353       0.06                52841        0.09                24344        13.10                       2        2.02                    3         0.01       8114.66
CALL SYSIB                   41       0.00             34754421       59.97                61356        33.03                       0        0.00                 2949        10.97         20.80

  7 record(s) selected.

Caveats and Modifications

• The statement text returned is limited to a small size to make the formatting work better here. You’ll want to either expand it so you can see the whole statement, or instead select the EXECUTABLE_ID and then later query mon_get_pkg_cache_stmt to get the STMT_TEXT based on that EXECUTABLE_ID.
• You can change the sort order to match your preference. I like rows_read because it’s most likely to help me identify where I can create an index to help a query.
• This statement only returns rows that consume 10% or more of one of the identified critical resources. You can adjust that percent in the where clause to match a different value or remove items from the where clause if you’re not interested in problem statements in a particular category.
• Output is also limited to 20 rows – this is arbitrary, and you may want to alter it. I usually find my biggest problems within 5 or fewer statements.
• The ‘D’ in the call of the mon_get_pkg_cache_stmt table function limits my output to only dynamic SQL. If you would like to look at both dynamic and static sql, you can use NULL instead