[Oracle] Lock 모니터링

-- ###########################################################
-- Lock Waing Session 점검
--
-- 대용량 시스템 오라클 Lock 모니터링 및 Lock Holding Session Kill
-- ###########################################################
SELECT l.inst_id
     , DECODE(s.blocking_session,'',s.sid,'') AS sid
     , DECODE(s.blocking_session,'','▽',' └ '||s.sid) AS wait_sid
     , DECODE(s.blocking_session,'','alter system kill session ''' || s.sid || ',' || s.serial# || ''';','') AS kill
     , s.seconds_in_wait AS "Wait Time"
     , s.last_call_et
     , s.status
     , l.type
     , s.username
     , s.sql_id
     , (SELECT object_name || '(' || SUBSTR(object_type, 1, 1) || ')'
          FROM dba_objects
         WHERE object_id = s.row_wait_obj#) AS locked_obj
     , s.machine
     , s.module
     , s.event
     , DECODE(s.sql_id,NULL,(SELECT sql_text FROM gv$sqlarea WHERE sql_id = s.prev_sql_id AND ROWNUM = 1),
                            (SELECT sql_text FROM gv$sqlarea WHERE sql_id = s.sql_id      AND ROWNUM = 1)) AS sql_stmt
  FROM gv$session s
     , (SELECT /*+ no_merge */ inst_id  --현재 대기중인 세션만 lock info 조회
             , sid
             , type
             , lmode
          FROM gv$lock 
         WHERE sid IN (SELECT DISTINCT DECODE(LEVEL,1,sid,2,blocking_session)
                         FROM (SELECT sid,blocking_session
                                 FROM gv$session
                                WHERE lockwait IS NOT NULL)
                      CONNECT BY LEVEL <= 2)
           AND type = 'TM'
           AND lmode > 1
         GROUP BY inst_id,sid,type,lmode) l
 WHERE l.sid = s.sid
 ORDER BY DECODE(s.blocking_session,NULL,l.sid,s.blocking_session),l.inst_id,wait_sid DESC
;