概述

国庆节偷个懒，分享一个关于锁的实用sql,不过统计是实时的，如果要查看一段时间的，建议将结果插入一张中间表。

一键获取锁相关信息

SELECT mm.inst_id,  mm.sid,  mm.TYPE,  mm.id1,  mm.id2,  LPAD(TRUNC(mm.ctime / 60 / 60), 3) || ' Hour ' || LPAD(TO_CHAR(TRUNC(mm.ctime / 60) - TRUNC(mm.ctime / 60 / 60) * 60, 'fm09'), 2) || ' Min ' || LPAD(TO_CHAR(mm.ctime - TRUNC(mm.ctime / 60) * 60, 'fm09'), 2) || ' Sec' ctime,  CASE WHEN mm.block = 1  AND mm.lmode != 0 THEN 'holder'  WHEN mm.block = 0  AND mm.request != 0 THEN 'waiter'  ELSE NULL END role,  CASE WHEN ee.blocking_session IS NOT NULL THEN 'waiting for SID ' || ee.blocking_session  ELSE NULL END blocking_session,  dd.sql_text sql_text,  cc.event wait_event  FROM gv$lock mm,  gv$session ee,  gv$sqlarea dd,  gv$session_wait cc  WHERE mm.sid IN (SELECT nn.sid  FROM (SELECT tt.*,  COUNT(1) OVER (PARTITION BY tt.TYPE,  tt.id1,  tt.id2) cnt, MAX(tt.lmode) OVER (PARTITION BY tt.TYPE,  tt.id1,  tt.id2) lmod_flag, MAX(tt.request) OVER (PARTITION BY tt.TYPE,  tt.id1,  tt.id2) request_flag  FROM gv$lock tt) nn  WHERE nn.cnt > 1  AND nn.lmod_flag != 0  AND nn.request_flag != 0)  AND mm.sid = ee.sid (+)  AND ee.sql_id = dd.sql_id (+)  AND mm.sid = cc.sid (+)  AND ((mm.block = 1  AND mm.lmode != 0)  OR (mm.block = 0  AND mm.request != 0))  ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC,  mm.ctime DESC

通过这条sql我们可以看到哪个事务被哪个事务所阻塞了，阻塞了多久及相关的sql。

优化后的sql

以下是对上面sql的进一步优化：

SELECT /*+ NO_CPU_COSTING */ mm.inst_id,  mm.sid,  mm.TYPE,  mm.id1,  mm.id2,  LPAD(TRUNC(mm.ctime / 60 / 60), 3) || ' Hour ' || LPAD(TO_CHAR(TRUNC(mm.ctime / 60) - TRUNC(mm.ctime / 60 / 60) * 60, 'fm09'), 2) || ' Min ' || LPAD(TO_CHAR(mm.ctime - TRUNC(mm.ctime / 60) * 60, 'fm09'), 2) || ' Sec' ctime,  CASE WHEN mm.block = 1  AND mm.lmode != 0 THEN 'holder'  WHEN mm.block = 0  AND mm.request != 0 THEN 'waiter'  ELSE NULL END role,  CASE WHEN ee.blocking_session IS NOT NULL THEN 'waiting for SID ' || ee.blocking_session  ELSE NULL END blocking_session,  dd.sql_text sql_text,  cc.event wait_event  FROM gv$lock mm,  gv$session ee,  gv$sqlarea dd,  gv$session_wait cc  WHERE EXISTS (SELECT 'X'  FROM (SELECT tt.*,  COUNT(1) OVER (PARTITION BY tt.TYPE,  tt.id1,  tt.id2) cnt, MAX(tt.lmode) OVER (PARTITION BY tt.TYPE,  tt.id1,  tt.id2) lmod_flag, MAX(tt.request) OVER (PARTITION BY tt.TYPE,  tt.id1,  tt.id2) request_flag  FROM gv$lock tt) nn  WHERE nn.cnt > 1  AND nn.lmod_flag != 0  AND nn.request_flag != 0  AND nn.sid = mm.sid)  AND mm.sid = ee.sid (+)  AND ee.sql_id = dd.sql_id (+)  AND mm.sid = cc.sid (+)  AND (mm.block = 1  AND mm.lmode <> 0  OR mm.block = 0  AND mm.request <> 0)  ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC,  mm.ctime DESC

--查看阻塞会话SID信息（holder）--考虑到多实例会存在sid一致的情况，所以加了实例区分SELECT INST_ID "实例ID", SID, SERIAL#, PROCESS, USERNAME, SCHEMANAME, OSUSER, MACHINE, PROGRAM, SQL_ID, MODULE, ACTION, LOCKWAIT, BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE, BLOCKING_SESSION "阻塞会话SID", EVENT "等待事件", WAIT_CLASS, LOGON_TIME, CASE WHEN STATE = 'WAITING' THEN SECONDS_IN_WAIT WHEN STATE = 'WAITING KNOWN TIME' THEN WAIT_TIME END AS "等待时间(s)", STATE FROM GV$SESSION WHERE INST_ID = '&INSTANCE' and SID = '&SID';

前面只是看到这条sql是什么，我们一定不能两眼一抹黑就直接kill了，怎么也得先看下这个事务具体在做什么。

查看会话ID在执行什么sql

select sid, sql_textfrom v$session a, v$sql bwhere sid in (&sid)and (b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id);

最终处理结果：

alter system kill session '1083,11362';

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~