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
[…] DB2 Administrative SQL Cookbook: Finding Problem SQL in the Package Cache […]
Thanks Ember always for sharing… this query along with the Index Efficiency one helped me very much on my databases. Regards
[…] DB2 Administrative SQL Cookbook: Finding Problem SQL in the Package Cache […]
Amber,
Besides what you mentioned, will reorg and runstats age the sql out of pkg cache.
Thanks,
Wayne
They should invalidate the sections in the package cache, but I always to a rebind /all and usually also flush the package cache to be safe.
Hello Ember, I am running the first sql on IBM DB2 V10.5
SQL0801N Division by zero was attempted. SQLSTATE=22012
Can you tell me what I can do to fix this?
This error means that some denominator is coming out as 0. The simple way to solve it that leads to mildly incorrect results is just to add 1 to any number in a denominator. You can use nullif to get a null instead of a value, or you can wrap the calculations in a case statement to only calculate values if the denominator is greater than 0.