Thursday, February 19, 2009

Blocking Sessions

Summary
The script following finds the blocking sessions in the database:
SELECT                                                           /*+ RULE */
        LPAD ('--->', DECODE (A.REQUEST, 0, 0, 5)) || A.SID SID,
         B.SERIAL#,
         DECODE (TRUNC (SYSDATE - LOGON_TIME),
                 0, NULL,
                 TRUNC (SYSDATE - LOGON_TIME) || ' Days' || ' + ')
         || TO_CHAR (
               TO_DATE (TRUNC (MOD (SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),
               'HH24:MI:SS')
            LOGON,
         B.STATUS,
         B.SCHEMANAME SCHEMA,
         DECODE (O.OWNER || '.' || O.OBJECT_NAME,
                 '.', NULL,
                 O.OWNER || '.' || O.OBJECT_NAME)
            OBJECT,
         O.OBJECT_TYPE,
         B.OSUSER,
         B.MACHINE,
         B.MODULE,
         B.PROGRAM,
         DECODE (BLOCK, 0, NULL, 'BLOCKER')
         || DECODE (REQUEST, 0, NULL, '-->WAITER')
            BLOCKER,
         DECODE (A.LMODE,
                 0, 'None',
                 1, 'Null',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive',
                 A.LMODE)
            LMODE,
         DECODE (
            TRUNC (CTIME / 86400),
            0, TO_CHAR (TO_DATE (CTIME, 'SSSSS'), 'HH24:MI:SS'),
            TRUNC (CTIME / 86400) || ' Days + '
            || TO_CHAR (
                  TO_DATE (CTIME - (TRUNC (CTIME / 86400)) * 86400, 'SSSSS'),
                  'HH24:MI:SS'))
            TIME,
         A.TYPE,
            'alter system kill session '
         || ''''
         || A.SID
         || ', '
         || B.SERIAL#
         || ''''
         || ' immediate;'
            KILL_SESSION,
         DECODE (
            OBJECT_TYPE,
            NULL, NULL,
               'Dbms_Rowid.rowid_create(1, '
            || ROW_WAIT_OBJ#
            || ', '
            || ROW_WAIT_FILE#
            || ',
'
            || ROW_WAIT_BLOCK#
            || ', '
            || ROW_WAIT_ROW#
            || ')')
            ROW_ID
    FROM V$LOCK A, V$SESSION B, DBA_OBJECTS O
   WHERE A.SID = B.SID AND (LMODE = 0 OR BLOCK = 1)
         AND O.OBJECT_ID(+) =
                DECODE (B.ROW_WAIT_OBJ#, -1, NULL, B.ROW_WAIT_OBJ#)
ORDER BY A.ID1, A.REQUEST;

For RAC databases the above script will not discover the blockings between sessions in different nodes.
Try this one:
  SELECT B.INST_ID,
         LPAD ('--->', DECODE (A.REQUEST, 0, 0, 5)) || A.SID SID,
         A.ID1,
         A.ID2,
         A.LMODE,
         A.BLOCK,
         A.REQUEST,
         DECODE (A.TYPE,
                 'MR', 'Media Recovery',
                 'RT', 'Redo Thread',
                 'UN', 'User Name',
                 'TX', 'Transaction',
                 'TM', 'DML',
                 'UL', 'PL/SQL User Lock',
                 'DX', 'Distributed Xaction',
                 'CF', 'Control File',
                 'IS', 'Instance State',
                 'FS', 'File Set',
                 'IR', 'Instance Recovery',
                 'ST', 'Disk Space Transaction',
                 'TS', 'Temp Segment',
                 'IV', 'Library Cache Invalidation',
                 'LS', 'Log Start or Switch',
                 'RW', 'Row Wait',
                 'SQ', 'Sequence Number',
                 'TE', 'Extend Table',
                 'TT', 'Temp Table',
                 A.TYPE)
            LOCK_TYPE,
         B.PROGRAM,
         B.OSUSER,
         B.USERNAME,
         B.STATUS,
         B.MODULE,
         B.ACTION,
         B.LOGON_TIME,
         B.LAST_CALL_ET,
            'alter system kill session '
         || ''''
         || A.SID
         || ', '
         || B.SERIAL#
         || ''''
         || ' immediate;'
            KILL_SESSION,
         DECODE (
            OBJECT_TYPE,
            NULL, NULL,
               'Dbms_Rowid.rowid_create(1, '
            || ROW_WAIT_OBJ#
            || ', '
            || ROW_WAIT_FILE#
            || ', '
            || ROW_WAIT_BLOCK#
            || ', '
            || ROW_WAIT_ROW#
            || ')')
            ROW_ID
    FROM GV$LOCK A, GV$SESSION B, DBA_OBJECTS O
   WHERE     (A.ID1, A.ID2) IN (SELECT ID1, ID2
                                  FROM GV$LOCK
                                 WHERE LMODE = 0)
         AND A.INST_ID = B.INST_ID
         AND A.SID = B.SID
         AND O.OBJECT_ID(+) =
                DECODE (B.ROW_WAIT_OBJ#, -1, NULL, B.ROW_WAIT_OBJ#)
ORDER BY A.ID1, A.ID2, A.REQUEST;

Tip: If the column BLOCK=2, 'Global' then this lock is global, it comes from the other instance.
Tip: The last column of the query for blocked sessions is ROWID of the record!
To find the exact locked row execute:
SELECT *
  FROM OBJECT
 WHERE ROWID = DBMS_ROWID.ROWID_CREATE (1,
                                        48804,
                                        31,
                                        114809,
                                        210);

No comments:

Post a Comment