Queuing caused by high sort memory usage

• Use the MON_GET_ACTIVITY table function to determine the sort memory requirements for each query that is currently either executing or idle. This table function also reports information about the current state of each executing or idle query.
• If demand or actual usage is high, look for queries with a high value for either of the following monitor elements:
  • The estimated_sort_shrheap_top monitor element reports the estimated peak demand for query sort memory.
  • The sort_shrheap_top monitor element reports the actual observed sort memory usage.

WITH TOTAL_MEM(CFG_MEM, MEMBER) AS (SELECT VALUE, MEMBER FROM SYSIBMADM.DBCFG WHERE NAME = 'sheapthres_shr')
SELECT A.MEMBER,
       A.COORD_MEMBER,
       A.ACTIVITY_STATE,
       A.APPLICATION_HANDLE,
       A.UOW_ID,
       A.ACTIVITY_ID,
       B.APPLICATION_NAME,
       B.SESSION_AUTH_ID,
       B.CLIENT_IPADDR,
       A.ENTRY_TIME,
       A.LOCAL_START_TIME,
       CASE WHEN (A.LOCAL_START_TIME IS NOT NULL) THEN
             TIMESTAMPDIFF(2, CHAR(A.LOCAL_START_TIME - A.ENTRY_TIME))
       ELSE
             A.WLM_QUEUE_TIME_TOTAL/1000
       END AS TOTAL_QUEUETIME_SECONDS,
       CASE WHEN (A.LOCAL_START_TIME IS NOT NULL) THEN
             TIMESTAMPDIFF(2, CHAR(CURRENT_TIMESTAMP-A.LOCAL_START_TIME))
       ELSE
             NULL
       END AS TOTAL_RUNTIME_SECONDS,
       CASE WHEN (A.LOCAL_START_TIME IS NOT NULL) THEN
             TIMESTAMPDIFF(2, CHAR(CURRENT_TIMESTAMP-A.LOCAL_START_TIME))-A.COORD_STMT_EXEC_TIME/1000
       ELSE
             NULL
       END AS TOTAL_CLIENT_WAIT_SECONDS,
       A.ADM_BYPASSED,
       A.ADM_RESOURCE_ACTUALS,
       A.QUERY_COST_ESTIMATE,
       A.ESTIMATED_RUNTIME,
       A.ESTIMATED_SORT_SHRHEAP_TOP AS ESTIMATED_SORTMEM_USED_PAGES,
       DEC((FLOAT(A.ESTIMATED_SORT_SHRHEAP_TOP)/FLOAT(C.CFG_MEM)) * 100, 5, 2) AS ESTIMATED_SORTMEM_USED_PCT,
       A.SORT_SHRHEAP_ALLOCATED AS SORTMEM_USED_PAGES,
       DEC((FLOAT(A.SORT_SHRHEAP_ALLOCATED)/FLOAT(C.CFG_MEM)) * 100, 5, 2) AS SORTMEM_USED_PCT,
       SORT_SHRHEAP_TOP AS PEAK_SORTMEM_USED_PAGES,
       DEC((FLOAT(A.SORT_SHRHEAP_TOP)/FLOAT(C.CFG_MEM)) * 100, 5, 2) AS PEAK_SORTMEM_USED_PCT,
       C.CFG_MEM AS CONFIGURED_SORTMEM_PAGES, 
       SUBSTR(A.STMT_TEXT, 1, 512) AS STMT_TEXT
FROM TABLE(MON_GET_ACTIVITY(NULL,-2)) AS A,
        TABLE(MON_GET_CONNECTION(NULL,-1)) AS B,
        TOTAL_MEM AS C
WHERE (A.APPLICATION_HANDLE = B.APPLICATION_HANDLE) AND (A.MEMBER = C.MEMBER) AND
      (A.ACTIVITY_STATE IN ('EXECUTING', 'IDLE'))
ORDER BY MEMBER, APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, ACTIVITY_STATE

The following sample output indicates that the 2 queries that are currently running are each consuming 32% of the total database sort memory. Both queries are in the IDLE state, waiting for the next request from the client.

APPLICATION_HANDLE   TOTAL_RUNTIME_SECONDS TOTAL_WAIT_ON_CLIENT_TIME_SECONDS ACTIVITY_STATE     SORTMEM_USED_PCT STATEMENT_TEXT
-------------------- --------------------- --------------------------------- ---------------  ------------------ --------------
             64                  4364                              4364 IDLE                           32.36 select * from 
             66                  4326                              4326 IDLE                           32.36 select * from