出现tx锁的情况

出现问题执行如下SQL
SELECT event,COUNT(*) FROM v$session WHERE wait_class# <> 6 GROUP BY event ORDER BY count(*) DESC

blocking_session始作俑者的SESSION和SQL
SELECT S.USERNAME,
       S.MACHINE,
       S.INST_ID,
       S.SID,
       S.SERIAL#,
       q'[ALTER SYSTEM KILL SESSION ']' || SID || q'[,]' || SERIAL# || q'[';]' AS 杀掉用户会话,
       S.SQL_ID,
       S.EVENT,
       S.LAST_CALL_ET AS "持续未提交时间",
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT
  FROM GV$SESSION S, GV$SQL Q
 WHERE S.SQL_ID = Q.SQL_ID
   AND S.SID IN (SELECT BLOCKING_SESSION
                   FROM GV$SESSION
                  WHERE BLOCKING_SESSION IS NOT NULL);


你懂得，逐个搞层层扒

有时间SQL_ID列为空，此时找不到那个所谓"始作俑者的SQL"，如下：
 SELECT *
   FROM V$SESSION
  WHERE SID IN (SELECT BLOCKING_SESSION
                  FROM GV$SESSION
                 WHERE BLOCKING_SESSION IS NOT NULL);

这时候我们通过下面方法：
SELECT S.USERNAME,
       S.MACHINE,
       S.INST_ID,
       S.SID,
       S.SERIAL#,
       q'[ALTER SYSTEM KILL SESSION ']' || SID || q'[,]' || SERIAL# ||
        q'[';]' AS 杀掉用户会话,
       Q.SQL_ID,
       S.EVENT,
       S.LAST_CALL_ET AS "持续未提交时间",
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT
  FROM GV$SESSION S, GV$SQL Q
 WHERE S.INST_ID = Q.INST_ID
   AND S.PREV_EXEC_START = Q.LAST_ACTIVE_TIME
   AND S.USERNAME = Q.PARSING_SCHEMA_NAME
   AND S.MODULE = Q.MODULE
   AND S.ACTION_HASH = Q.ACTION_HASH
   AND S.SID IN (SELECT BLOCKING_SESSION
                   FROM GV$SESSION
                  WHERE BLOCKING_SESSION IS NOT NULL);   

 SELECT A.INST_ID,
        A.SID,
        A.EVENT,
        A.SQL_ID,
        B.SQL_TEXT          RUNNING_SQL,
        C.SQL_IN_SESSION,
        C.SQL_ID_IN_V$SQL,
        C.SQL_TEXT          BLOCKING_SQL,
        A.BLOCKING_SESSION,
        A.BLOCKING_INSTANCE
   FROM GV$SESSION A,
        (SELECT SQL_ID, SQL_TEXT
           FROM (SELECT SQL_ID,
                        SQL_TEXT,
                        ROW_NUMBER() OVER(PARTITION BY SQL_ID ORDER BY SQL_ID) AS RN
                   FROM GV$SQL)
          WHERE RN = 1) B,
        (SELECT A.INST_ID,
                A.SID,
                A.SQL_ID   SQL_IN_SESSION,
                B.SQL_ID   SQL_ID_IN_V$SQL,
                B.SQL_TEXT
           FROM GV$SESSION A, GV$SQL B
          WHERE A.INST_ID = B.INST_ID
            AND A.PREV_EXEC_START = B.LAST_ACTIVE_TIME
            AND A.USERNAME = B.PARSING_SCHEMA_NAME
            AND A.MODULE = B.MODULE) C
  WHERE A.SQL_ID = B.SQL_ID
    AND A.BLOCKING_SESSION IS NOT NULL
    AND A.BLOCKING_SESSION = C.SID
    AND A.BLOCKING_INSTANCE = C.INST_ID;

被阻塞的
SELECT S.USERNAME,
       S.MACHINE,
       S.INST_ID,
       S.SID,
       S.SERIAL#,
       S.SQL_ID,
       S.EVENT,
       S.LAST_CALL_ET AS "被锁时间(s)",
       S.BLOCKING_SESSION_STATUS,
       Q.SQL_TEXT,
       Q.SQL_FULLTEXT
  FROM GV$SESSION S, GV$SQL Q
 WHERE S.SQL_ID = Q.SQL_ID
   AND S.BLOCKING_SESSION IS NOT NULL;


select decode(request, 0, 'Holder:', ' Waiter:') || s.inst_id || ':' ||
       s.sid || ',' || s.serial# sess,
       id1,
       id2,
       lmode,
       request,
       l.type,
       ctime,
       s.username,
       s.sql_id,
       s.event
    -- ,s.service_name
  from gv$lock l, gv$session s
 where (id1, id2, l.type) in
       (select id1, id2, type from gv$lock where request > 0)
   and l.sid = s.sid
   and l.inst_id = s.inst_id
 order by id1, ctime desc, request

或者
SELECT /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */
 A.USERNAME,
 A.MACHINE,
 A.SID,
 A.SERIAL#,
 A.LAST_CALL_ET "Seconds",
 B.ID1,
 C.SQL_TEXT     "SQL"
  FROM V$SESSION A, V$LOCK B, V$SQLTEXT C
 WHERE A.USERNAME IS NOT NULL
   AND A.LOCKWAIT = B.KADDR
   AND C.HASH_VALUE = A.SQL_HASH_VALUE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29990276/viewspace-1979920/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29990276/viewspace-1979920/