Historical resource usage for queries

It can be useful to examine historical resource usage for queries that have completed execution. Queries with very large sort memory requirements might cause queuing when run concurrently with other work. Identifying and tuning these queries can reduce overall queue time observed on the database.

You can obtain information about the resource usage of past queries that are still in the package cache by using the MON_GET_PKG_CACHE_STMT table function. For queries that are no longer in the package cache, historical resource usage information can be captured by using either a package cache event monitor (which captures 1 record for each query ejected from the package cache) or an activity event monitor (which captures 1 record for each execution of a query). Some example queries are provided to illustrate how the resource usage information can be extracted and utilized.

Examples

Example 1: Identify the top 20 sort memory consuming queries in the package cache

The following query uses the MON_GET_PKG_CACHE_STMT table function.

WITH SORTMEM (SHEAPTHRESSHR, MEMBER) AS (SELECT VALUE, MEMBER FROM SYSIBMADM.DBCFG WHERE NAME = 'sheapthres_shr'),
     SORTCONSUMERS(SORTUTIL, EXEC_ID) AS 
         (SELECT MAX(DEC((FLOAT(SORT_SHRHEAP_TOP)/FLOAT(SHEAPTHRESSHR))*100, 5,2)) AS SORT_SHRHEAP_UTIL,
                 EXECUTABLE_ID      
          FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS T,
               SORTMEM AS Q
          WHERE T.MEMBER = Q.MEMBER
          GROUP BY EXECUTABLE_ID
          ORDER BY SORT_SHRHEAP_UTIL DESC
          FETCH FIRST 20 ROWS ONLY)
SELECT SORTUTIL,
       SUBSTR(STMT_TEXT, 1, 100) STMT
FROM SORTCONSUMERS S,
     TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) AS T
WHERE S.EXEC_ID = T.EXECUTABLE_ID
ORDER BY SORTUTIL DESC

The output lists the queries in the order of their sort memory utilization.

SORTUTIL STMT                                                                                                
-------- ---------------------------------------------------------------------------------
   20.03 SELECT C1, C2 …
   10.70 SELECT TABNAME FROM SYSCAT.TABLES  WHERE TABNAME='HMON_COLLECTION' AND  TABSCHEM
     ...
  20 record(s) selected.

Example 2: Construct a histogram of query memory usage

The following query queries the tables of an activity event monitor. It assumes that an activity event monitor named A is created; consequently, the names of the event monitor tables are ACTIVITY_A, ACTIVITYSTMT_A, and so on.

WITH SORTMEM (SHEAPTHRESSHR, MEMBER) AS (SELECT VALUE, MEMBER FROM SYSIBMADM.DBCFG WHERE NAME = 'sheapthres_shr'),
     ACT_SORT(SORT_SHRHEAP_UTIL) AS 
     (SELECT MAX(DEC((FLOAT(SORT_SHRHEAP_TOP)/FLOAT(SHEAPTHRESSHR))*100, 5,2)) 
      FROM ACTIVITY_A A,
           SORTMEM B 
      WHERE A.PARTITION_NUMBER = B.MEMBER 
      GROUP BY APPL_ID, UOW_ID, ACTIVITY_ID)
SELECT RANGE AS PCT_MEM_UTIL, COUNT(*) NUM_IN_BIN FROM
( SELECT
     CASE WHEN SORT_SHRHEAP_UTIL < 1 THEN
        '< 1%'
     WHEN SORT_SHRHEAP_UTIL < 5 THEN
        '> 1% and < 5%'
     WHEN SORT_SHRHEAP_UTIL < 10 THEN
        '> 5% and < 10%'
     WHEN SORT_SHRHEAP_UTIL < 20 THEN
        '> 10% and < 20%'
     WHEN SORT_SHRHEAP_UTIL < 30 THEN
        '> 20% and < 30%'
     WHEN SORT_SHRHEAP_UTIL < 50 THEN
        '> 30% and < 50%'
     WHEN SORT_SHRHEAP_UTIL < 75 THEN
        '> 50% and < 75%'
     ELSE
        '> 75%'
     END AS RANGE FROM ACT_SORT) GROUP BY RANGE ORDER BY RANGE ASC

The sample output shows that 12 queries each require more than 75% of the configured memory. Due to their large memory requirements, these queries are likely to cause queuing if they are run concurrently with other queries.

PCT_MEM_UTIL    NUM_IN_BIN                       
--------------- ---------------------------------
< 1%                                        52310
> 1% and < 5%                                4942
> 5% and < 10%'                              1753 
> 10% and < 20%                              9782
> 20% and < 30%                               151
> 30% and < 50%                                36
> 50% and < 75%                                21
> 75%                                          12

The following query can be run to identify the statement text for these 12 statements. This query finds the statement text for any activity that used more than 75% of the configured sort memory.

WITH SORTMEM (SHEAPTHRESSHR, MEMBER) AS (SELECT VALUE, MEMBER FROM SYSIBMADM.DBCFG WHERE NAME = 'sheapthres_shr'),
     ACT_SORT(APPL_ID, UOW_ID, ACTIVITY_ID, SORT_SHRHEAP_UTIL) AS
     (SELECT APPL_ID,
             UOW_ID,
             ACTIVITY_ID,
             MAX(DEC((FLOAT(SORT_SHRHEAP_TOP)/FLOAT(SHEAPTHRESSHR))*100, 5,2)) AS SORT_SHREAP_UTI
      FROM ACTIVITY_A A,
           SORTMEM B
      WHERE A.PARTITION_NUMBER = B.MEMBER AND
            DEC((FLOAT(SORT_SHRHEAP_TOP)/FLOAT(SHEAPTHRESSHR))*100, 5,2) > 75
      GROUP BY APPL_ID, UOW_ID, ACTIVITY_ID)
SELECT SORT_SHRHEAP_UTIL, SUBSTR(STMT_TEXT, 1, 100) AS STMT
FROM ACT_SORT P,
     ACTIVITYSTMT_A Q
WHERE P.APPL_ID = Q.APPL_ID AND
      P.UOW_ID = Q.UOW_ID AND
      P.ACTIVITY_ID = Q.ACTIVITY_ID