Monitoring general queuing behavior in a customized adaptive WLM environment

The MON_GET_SERVICE_SUPERCLASS and MON_GET_SERVICE_SUPERCLASS_STATS table functions display the following information:

• Number of successfully completed queries (ACT_COMPLETED_TOTAL)
• Number of aborted or failed queries (ACT_ABORTED_TOTAL)
• Number of queued statements (WLM_QUEUE_ASSIGNMENTS_TOTAL)
• Total elapsed time spent executing application requests (TOTAL_APP_RQST_TIME)
• Total queue time (WLM_QUEUE_TIME_TOTAL)
• Service class resource entitlement (RESOURCE_ENTITLEMENT)
• Service class resource entitlement type (RESOURCE_SHARE_TYPE)

You can use these results to compute the percentage of queries that are being queued for each service class, and the average time that each query spends in the admission queue.

SELECT SUBSTR(T.SERVICE_SUPERCLASS_NAME, 1, 30) AS SERVICE_CLASS,
       DECIMAL(RESOURCE_ENTITLEMENT, 5, 2) AS RESOURCE_ENTITLEMENT,
       RESOURCE_SHARE_TYPE, 
       SUM(ACT_COMPLETED_TOTAL) AS STMTS_COMPLETED, 
       SUM(ACT_ABORTED_TOTAL) AS STMTS_FAILED, 
       SUM(WLM_QUEUE_ASSIGNMENTS_TOTAL) AS STMTS_QUEUED, 
       CASE WHEN (SUM(ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL) > 0) THEN
          DEC((FLOAT(SUM(WLM_QUEUE_ASSIGNMENTS_TOTAL))/FLOAT(SUM(ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL))) * 100, 5, 2) 
       ELSE
          0
       END AS PCT_STMTS_QUEUED, 
       SUM(TOTAL_APP_RQST_TIME) AS RQST_TIME_MS, 
       CASE WHEN (SUM(ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL) > 0) THEN 
          SUM(TOTAL_APP_RQST_TIME) / SUM(ACT_COMPLETED_TOTAL + ACT_ABORTED_TOTAL)
       ELSE
          0
       END AS AVG_APP_RQST_TIME_MS, 
       SUM(WLM_QUEUE_TIME_TOTAL) AS TOTAL_QUEUE_TIME_MS, 
       CASE WHEN (SUM(WLM_QUEUE_ASSIGNMENTS_TOTAL) > 0) THEN
          SUM(WLM_QUEUE_TIME_TOTAL) / SUM(WLM_QUEUE_ASSIGNMENTS_TOTAL) 
       ELSE
          0
       END AS AVG_QUEUE_TIME_MS 
FROM TABLE(MON_GET_SERVICE_SUPERCLASS(NULL,-2)) AS T,
     TABLE(MON_GET_SERVICE_SUPERCLASS_STATS(NULL,-2)) AS Q
WHERE T.SERVICE_CLASS_ID = Q.SERVICE_CLASS_ID AND
      T.MEMBER = Q.MEMBER
GROUP BY T.SERVICE_SUPERCLASS_NAME, 
         Q.RESOURCE_ENTITLEMENT,
         Q.RESOURCE_SHARE_TYPE

The information reported is similar to that reported by MON_GET_DATABASE, but it is broken down further by service class. Examining these counters at the database level, we see that only 80 out of 100090 queries have been queued and conclude that queuing does not have a large affect on work in the database. However, when looking at the service class level it becomes clear that queuing has a significant affect on queries in the SC1 service class: 88% of the activities in this service class are experiencing queuing. This is probably expected due to the low resource limit with a hard share type that is configured for this service class. If the queuing in this service class is affecting performance, consider increasing the resources assigned to the service class.

SERVICE_CLASS                  RESOURCE_ENTITLEMENT RESOURCE_SHARE_TYPE STMTS_COMPLETED      STMTS_FAILED         STMTS_QUEUED         PCT_STMTS_QUEUED RQST_TIME_MS         AVG_APP_RQST_TIME_MS TOTAL_QUEUE_TIME_MS  AVG_QUEUE_TIME_MS   
------------------------------ -------------------- ------------------- -------------------- -------------------- -------------------- ---------------- -------------------- -------------------- -------------------- --------------------
                SC1                           10.00 HARD                                  90                    0                   80            88.88             15336615               170406           500000                 6250
SYSDEFAULTUSERCLASS                           90.00 SOFT                              100000                    0                    0             0.00             23960089                  239