Queuing caused by queued queries with high sort memory estimates

If neither threads nor sort memory for executing or idle queries appears to be constrained, the problem might be a queued query with a high memory demand. Such a query can cause the admission queue to block incoming work, because the database must wait for existing work to drain so that there are enough resources to admit new queries. To identify such a query, use the monitoring query described here.

  • Use the MON_GET_ACTIVITY table function to determine the sort memory requirements for each currently queued query.
  • Use the estimated_sort_shrheap_top monitor element to report the estimated peak demand for query sort memory.
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.WLM_QUEUE_TIME_TOTAL/1000
         AS TOTAL_QUEUETIME_SECONDS,
       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,
       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 ('QUEUED'))
ORDER BY MEMBER, APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, ACTIVITY_STATE
The following sample output indicates that a single queued query requires 64% of the configured shared sort memory.
APPLICATION_HANDLE   TOTAL_RUNTIME_SECONDS TOTAL_WAIT_ON_CLIENT_TIME_SECONDS ACTIVITY_STATE    ESTIMATED_SORTMEM_USED_PCT STATEMENT_TEXT
-------------------- --------------------- --------------------------------- --------------- ---------------------------- --------------
                  64                  4364                              4364 QUEUED                                 64.36 select * from