记录两个排查与enq: TX - row lock contention事件相关的SQL
参考文档： Detecting blocking Locks in 10.2 and above [ID 729727.1]
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 4月 18 16:56:15 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> SELECT DISTINCT A.SID "waiting sid", 2 D.SQL_TEXT "waiting SQL", 3 A.ROW_WAIT_OBJ# "locked object", 4 A.BLOCKING_SESSION "blocking sid", 5 C.SQL_TEXT "SQL from blocking session", 6 c.SQL_ID 7 FROM gV$SESSION A, gV$ACTIVE_SESSION_HISTORY B, gV$SQL C, V$SQL D 8 WHERE A.EVENT = 'enq: TX - row lock contention' 9 AND A.SQL_ID = D.SQL_ID 10 AND A.BLOCKING_SESSION = B.SESSION_ID 11 AND C.SQL_ID = B.SQL_ID 12 AND B.CURRENT_OBJ# = A.ROW_WAIT_OBJ# 13 AND B.CURRENT_FILE# = A.ROW_WAIT_FILE# 14 AND B.CURRENT_BLOCK# = A.ROW_WAIT_BLOCK#; SQL> SELECT DISTINCT A.SID "waiting sid", 2 A.EVENT, 3 C.SQL_TEXT "SQL from blocked session", 4 B.SID "blocking sid", 5 B.EVENT, 6 B.SQL_ID, 7 B.PREV_SQL_ID, 8 D.SQL_TEXT "SQL from blocking session" 9 FROM gV$SESSION A, gV$SESSION B, gV$SQL C, gV$SQL D 10 WHERE A.EVENT = 'enq: TX - row lock contention' 11 AND A.BLOCKING_SESSION = B.SID 12 AND C.SQL_ID = A.SQL_ID 13 AND D.SQL_ID = NVL(B.SQL_ID, B.PREV_SQL_ID);
