How to analyze lock holders and waiters across RAC in Oracle

Pair every waiter (LMODE=0 request) with its holder from GV$LOCK across all RAC instances, and decode lock mode + requested mode to readable text (Row Share, Row Exclusive, Share, Exclusive, ...). Shows the SQL_ID each side is running so you can jump straight to the blocking statement.

SET LINES 125 PAGES 1000 ECHO OFF FEED ON
COL sid              FORMAT a6
COL serial#          FORMAT 999999
COL inst_id          FORMAT 999    HEADING 'INST|ID'
COL id1              FORMAT 99999999
COL id2              FORMAT 99999999
COL lmode            FORMAT a15
COL request          FORMAT a15
COL block            FORMAT 9999
COL blocking_session FORMAT 9999999 HEADING 'BLOCKING|SESSION'
COL seconds_in_wait  FORMAT a8      HEADING 'SECONDS|IN_WAIT'
COL blocker          FORMAT a8

SELECT DECODE(l.request, 0, 'Holder:', ' Waiter:') AS blocker,
       LPAD(' ', DECODE(l.request, 0, 0, 1)) || l.sid AS sid,
       s.serial#, l.inst_id,
       TO_CHAR(TRUNC(SYSDATE) + (l.ctime / 24 / 60 / 60), 'HH24:MI:SS') AS seconds_in_wait,
       s.status, s.logon_time,
       DECODE(l.request, 0, '', ' ') || DECODE(s.sql_id, NULL, s.prev_sql_id, s.sql_id) AS sql_id,
       DECODE(l.lmode,
              0, '(0)None',
              1, '(1)Null',
              2, '(2)Row Share',
              3, '(3)Row Exclu',
              4, '(4)Share',
              5, '(5)Share Row Ex',
              6, '(6)Exclusive') AS lmode,
       DECODE(l.request,
              0, '(0)None',
              1, '(1)Null',
              2, '(2)Row Share',
              3, '(3)Row Exclu',
              4, '(4)Share',
              5, '(5)Share Row Ex',
              6, '(6)Exclusive') AS request,
       l.type, l.block
FROM   gv$session s,
       gv$lock    l
WHERE  EXISTS (SELECT 1 FROM gv$lock o
               WHERE  o.lmode = 0
               AND    o.id1   = l.id1
               AND    o.id2   = l.id2)
AND    l.inst_id = s.inst_id
AND    l.sid     = s.sid
ORDER BY l.id1, l.request;