enq:TX – row lock contention等待事件在生产中熟称为锁表,现将自己遇到的产生锁表情况总结如下:
1. 统计信息不准确,造成update 语句走错执行计划,有锁表情况。解决方法:重新收集统计信息,开启统计信息自动收集任务。
2. 表中含有位图索引。解决方法为将位图索引修改为普通b 树索引。
3. Io 消耗高导致直接路径读后锁表。解决方法:关闭直接路径读参数。
4. 应用系统排查并发原因。
awr报告表现为:
处理脚本:
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, s.LOGON_TIME, 'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || ''';' 复制出来执行 FROM V$LOCKED_OBJECT T, ALL_OBJECTS O, V$SESSION S WHERE T.OBJECT_ID = O.OBJECT_ID AND T.SESSION_ID = S.SID;
--查看锁 SELECT DISTINCT decode(E.inst_id, 1, 'DB1', 2, 'DB2') DB服务器, decode(E.BLOCKING_SESSION,'',E.USERNAME,'--'||E.USERNAME) 登陆用户, O.OWNER || '.' || O.OBJECT_NAME 锁对象, E.LOGON_TIME, decode(e.WAIT_TIME,-2,'不确定',-1,'<0.1s',0,to_char(E.SECONDS_IN_WAIT)||'s',to_char(e.WAIT_TIME)||'s') AS 等待时间, E.SID, decode(E.BLOCKING_SESSION, '', '根锁为此会话', '根锁会话:' || E.BLOCKING_SESSION) 阻塞SID, E.BLOCKING_SESSION_STATUS STATUS, (CASE WHEN SQL_TEXT IS NULL THEN '(SID:' || E.SID || ')会话 SQL已跑完' ELSE '(SID:' || E.SID || ')会话 正执行SQL:' || SQL_.SQL_TEXT END) SQL_TEXT, --E.CLIENT_INFO AS IP, /*OLD ip,需要触发器支持*/ DECODE(E.CLIENT_INFO ,NULL, (SELECT SUBSTR(SUBSTR(A.COMMENT$TEXT, INSTR(A.COMMENT$TEXT, 'HOST=') + 5, 100), 1, INSTR(SUBSTR(A.COMMENT$TEXT, INSTR(A.COMMENT$TEXT, 'HOST=') + 5, 100), ')') - 1) IP FROM SYS.AUD$ a WHERE a.SESSIONID = (e.audsid) ) ,E.CLIENT_INFO) AS IP地址, /*AUD$.SESSIONID需要索引*/ LO.REQUEST, E.STATE, E.EVENT, E.MACHINE, E.INST_ID, E.BLOCKING_SESSION, DECODE(E.BLOCKING_SESSION, '', 'ALTER SYSTEM KILL SESSION ''' || E.SID || ',' || E.SERIAL# ||''' immediate ;','') AS norac_KILL, DECODE(E.BLOCKING_SESSION, '', 'ALTER SYSTEM KILL SESSION ''' || E.SID || ',' || E.SERIAL# ||',@'||E.inst_id||''' immediate ;','') rac_KILL FROM GV$SESSION E LEFT JOIN GV$SQL SQL_ ON SQL_.SQL_ID = E.SQL_ID AND SQL_.INST_ID = E.INST_ID JOIN GV$LOCKED_OBJECT L ON L.SESSION_ID = E.SID AND L.INST_ID = E.INST_ID JOIN ALL_OBJECTS O ON L.OBJECT_ID = O.OBJECT_ID JOIN GV$LOCK LO ON (LO.BLOCK != 0 OR LO.REQUEST != 0) WHERE LO.SID = L.SESSION_ID AND LO.SID = E.SID AND LO.INST_ID = L.INST_ID AND LO.INST_ID = E.INST_ID and SQL_TEXT IS not NULL ORDER BY E.inst_id, E.BLOCKING_SESSION,E.SID DESC
--操作系统杀锁 select inst_id, 'kill -9 ' || spid os_kill_session from (select p.inst_id, p.spid, a.sid, a.serial#, a.sql_id, a.event, a.status, a.program, a.machine, connect_by_isleaf as isleaf, sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree, level as tree_level from gv$session a, gv$process p where a.inst_id = p.inst_id and a.paddr = p.addr start with a.blocking_session is not null connect by (a.sid || '@' || a.inst_id) = prior (a.blocking_session || '@' || a.blocking_instance)) where isleaf = 1 order by tree_level asc;