本文于 11 天前发布,最后更新于 10 天前
一、问题描述
当发现达梦数据库所在服务器CPU爆满需要排查时,可通过如下方法排查。
二、问题排查
(1)首先,查询已执行超过2秒的活动SQL
SELECT * FROM
(
SELECT
SESS_ID,
SQL_TEXT,
DATEDIFF (SS, LAST_RECV_TIME, SYSDATE) Y_EXETIME,
SF_GET_SESSION_SQL (SESS_ID) FULLSQL,
CLNT_IP
FROM
V$SESSIONS
WHERE
STATE = 'ACTIVE'
)
WHERE
Y_EXETIME >= 2;
(2)查询是否有阻塞
SELECT * FROM V$TRXWAIT;
(3)查询被阻塞的会话和引起阻塞的会话
SELECT
SYSDATE STATTIME,
DATEDIFF (SS, S1."LAST_SEND_TIME", SYSDATE) SS,
'被阻塞的会话' WT,
S1."SESS_ID" WT_SESS_ID,
S1."SQL_TEXT" WT_SQL_TEXT,
S1."STATE" WT_STATE,
S1."TRX_ID" WT_TRX_ID,
S1."USER_NAME" WT_USER_NAME,
S1."CLNT_IP" WT_CLNT_IP,
S1."APPNAME" WT_APPNAME,
S1."LAST_SEND_TIME" WT_LAST_SEND_TIME,
'引起阻塞的会话' FM,
S2."SESS_ID" FM_SESS_ID,
S2."SQL_TEXT" FM_SQL_TEXT,
S2."STATE" FM_STATE,
S2."TRX_ID" FM_TRX_ID,
S2."USER_NAME" FM_USER_NAME,
S2."CLNT_IP" FM_CLNT_IP,
S2."APPNAME" FM_APPNAME,
S2."LAST_SEND_TIME" FM_LAST_SEND_TIME
FROM
V$SESSIONS S1,
V$SESSIONS S2,
V$TRXWAIT W
WHERE
S1.TRX_ID = W.ID
AND S2.TRX_ID = W.WAIT_FOR_ID;
根据查到的引起阻塞的信息,确认是否可以杀掉阻塞源头的会话,可以杀掉源头尝试消除阻塞。
杀掉后如未完全消除阻塞,需要再次确认是否可以进行进一步的清除会话,以完全解除阻塞。
可能用到的SQL-查杀所有引起阻塞的会话
DECLARE
V_CNT INT;
BEGIN
SELECT COUNT(SESS_ID) INTO V_CNT FROM "SYS"."V$SESSIONS" WHERE STATE = 'ACTIVE';
IF V_CNT>=50 THEN
FOR REC IN (SELECT SESS_ID FROM V$SESSIONS WHERE STATE = 'ACTIVE' AND SQL_TEXT LIKE 'UPDATE %' AND CLNT_IP<>'::1' ORDER BY 1)
LOOP
EXECUTE IMMEDIATE 'CALL SP_CLOSE_SESSION('||REC.SESS_ID||');';
END LOOP;
END IF;
END;
/
注意
由于一般阻塞都是由更新数据引起,所以SQL中以清除UPDATE语句为例。
另附
清除所有会话的方法
BEGIN
FOR REC IN
(
SELECT * FROM V$SESSIONS WHERE SESS_ID <> SESSID()
)
LOOP
SP_CLOSE_SESSION(REC.SESS_ID);
END LOOP;
END;