The impact of queuing on the database

The MON_GET_DATABASE table function displays 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)

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

SELECT 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_DATABASE(-2)) AS T

This sample output indicates that queuing is affecting only 1.36% of executed queries, but that the average queue time (1399644 milliseconds, which equals about 23 minutes) is quite high.

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   
-------------------- -------------------- -------------------- ---------------- -------------------- -------------------- -------------------- --------------------
                 365                    2                    5             1.36              7040323                19183              6998224              1399644