监视
监视查询，以更好地了解排队行为和资源耗用情况。
每当当前工作负载的资源需求超过数据库服务器上配置的资源容量时，查询就会进行排队。排队是预期行为，本身并不表示发生问题或错误。但是，某些应用程序或查询可能会导致意外的排队，从而导致不必要的延迟。例如，耗用接近 100% 数据库资源的长时间运行查询可能会阻止其他即将进行的工作。您可以使用通过表函数公开的监视元素来了解排队行为，确定哪些查询所耗用的资源最多。如有必要，您可使用 FORCE APPLICATION 命令来终止提交查询的应用程序，从而取消有问题的查询。
在发出任何监视查询之前，请将执行监视的连接放入缺省管理工作负载 (SYSDEFAULTADMWORKLOAD)。这可确保监视查询本身不会进行排队。为此，请使用 WLM_SET_CLIENT_INFO 存储过程，并指定 SYSDEFAULTADMWORKLOAD 作为输入工作负载名称，例如：
CALL WLM_SET_CLIENT_INFO(null,null,null,null,'SYSDEFAULTADMWORKLOAD')CALL WLM_SET_CLIENT_INFO(null,null,null,null,null)- 成功完成的查询数
- 中止或失败的查询数
- 已排队的语句数
- 执行应用程序请求所耗用的时间总计
- 排队时间总计
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 TSTMTS_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为更好了解数据库活动的当前状态，例如当前正在运行或排队的查询数目，请使用 MON_GET_ACTIVITY 表函数：
- 处于 EXECUTING 状态的查询当前正在耗用资源，并且正在由数据库引擎进行处理。
- 处于 IDLE 状态的查询当前正在耗用资源，但在客户机上遭阻止，正在等待下一个客户机请求。
- 处于 QUEUED 状态的查询正在等待资源。
以下监视查询会返回查询总数，以及已绕过自适应工作负载管理器（因此不适合排队）的查询数。
SELECT ACTIVITY_STATE,
SUM(ADM_BYPASSED) AS BYPASSED,
COUNT(*)
FROM TABLE(MON_GET_ACTIVITY(NULL,-1)) AS T
GROUP BY ACTIVITY_STATEACTIVITY_STATE BYPASSED COUNT
-------------------------------- -------------------- ---------------------------------
EXECUTING 1 1
IDLE 0 2
QUEUED 0 1要了解最受约束的资源是线程数还是排序内存量，请使用 SYSIBMADM.DBCFG 视图来查看已配置的资源，并使用 MON_GET_ACTIVITY 和 MON_GET_DATABASE 表函数来了解当前资源耗用情况：
- 如果最受约束的资源是线程，那么导致排队的原因是并发查询数量，因为大部分查询将使用相同数量的线程。（为简单起见，以下查询假定活动使用缺省等级，这将导致每个内核有一个线程，因此简单的活动计数即为每个内核的当前负载。）
- 如果最受约束的资源是排序内存，请使用本主题稍后描述的监视查询来确定耗用排序内存最多的查询的名称和状态。
WITH LOADTRGT(LOADTRGT) AS (SELECT MAX(VALUE) FROM SYSIBMADM.DBCFG WHERE NAME = 'wlm_agent_load_trgt'),
SORTMEM (SHEAPTHRESSHR, SHEAPMEMBER) AS (SELECT VALUE, MEMBER FROM SYSIBMADM.DBCFG WHERE NAME = 'sheapthres_shr'),
STMTS(NUMSTMT) AS (SELECT COUNT(*) FROM TABLE(MON_GET_ACTIVITY(NULL,-2))
AS T WHERE ADM_BYPASSED = 0 AND (ACTIVITY_STATE = 'EXECUTING' OR ACTIVITY_STATE = 'IDLE') AND MEMBER=COORD_PARTITION_NUM),
ALLOCMEM(ALLOCMEM, ALLOCMEMBER) AS (SELECT SORT_SHRHEAP_ALLOCATED, MEMBER FROM TABLE(MON_GET_DATABASE(-2)) AS T)
SELECT MAX(DEC((FLOAT(ALLOCMEM)/FLOAT(SHEAPTHRESSHR))*100, 5,2)) AS PERCENT_SORTMEM_USED,
MAX(DEC((FLOAT(NUMSTMT)/FLOAT(LOADTRGT))*100,5,2)) AS PERCENT_THREADS_USED
FROM LOADTRGT, SORTMEM, STMTS, ALLOCMEM
WHERE SHEAPMEMBER=ALLOCMEMBER
PERCENT_SORTMEM_USED PERCENT_THREADS_USED
-------------------- --------------------
76.99 11.76要了解当前执行中的活动的资源耗用情况和状态，请使用 MON_GET_ACTIVITY 表函数。以下监视查询会返回下列信息：
- 资源信息（effective_query_degree、sort_shrheap_allocated 和 sort_shrheap_top）
- 查询是否已绕过自适应工作负载管理器 (adm_bypassed)
- 当前状态（EXECUTING 或 IDLE）
- 可用于识别查询来源的信息，例如会话授权标识、应用程序名称和语句文本
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.EFFECTIVE_QUERY_DEGREE,
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)
ORDER BY MEMBER, APPLICATION_HANDLE, UOW_ID, ACTIVITY_ID, ACTIVITY_STATEAPPLICATION_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