監視
監視查詢，以更充分地瞭解佇列作業行為和資源耗用量。
每當現行工作量的資源需求超過已配置的資料庫伺服器資源容量時，都會將查詢排入佇列。佇列作業是預期行為，其本身並不指示發生問題或錯誤。不過，部分應用程式或查詢可以導致非預期的佇列作業，這會導致多餘的延遲。 例如，如果某個長時間執行的查詢所耗用的資料庫資源接近 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）
- 可以用來識別查詢來源（例如，階段作業授權 ID、應用程式名稱和陳述式文字）的資訊
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