lock이 걸렸을 때 blocker찾기 (10.09.20 )

예전 카페에서 폐지 공지가 와서 자료를 옮겨 놓습니다.

 

SELECT/*+ ordered use_hash( sw l ) */
  s.sid
 ,s.serial#
 ,s.module
 ,s.program
 ,sw.seconds_in_wait Sec
 ,TO_CHAR(l.sid) ||','||TO_CHAR(s1.Serial#)|| ':' || sw1.event ||decode(sw1.seconds_in_wait,0,NULL,'('||sw1.seconds_in_wait||'secs)')  holder
 FROM  
        v$session_wait sw
,       v$session      s
,       v$lock         l
,       v$session s1
,       v$session_wait sw1
WHERE  sw.event like 'enq%'
AND    S.sid    = sw.sid
AND    l.id1(+) = sw.p2
AND    l.id2(+) = sw.p3
AND    l.lmode(+) > 0
AND    l.sid(+) <> sw.sid
AND    s1.sid=sw1.sid
AND    l.sid=s1.sid
ORDER BYl.sid, sw.seconds_in_wait DESC;

 

 

참고자료 추가(2025.5.21)

✅ 1. Blocker vs Waiter 세션 쿼리

📌 쿼리 1: Blocker / Waiter 세션 매핑 (10g ~ 23c 사용 가능)

-- Oracle 10g, 11g, 12c, 18c, 19c, 21c, 23c 전부 사용 가능
SELECT
    s1.sid AS blocker_sid,
    s1.serial# AS blocker_serial,
    s1.username AS blocker_user,
    s2.sid AS waiter_sid,
    s2.serial# AS waiter_serial,
    s2.username AS waiter_user,
    s2.seconds_in_wait,
    s2.event
FROM
    v$lock l1,
    v$session s1,
    v$lock l2,
    v$session s2
WHERE
    l1.block = 1
    AND l1.sid = s1.sid
    AND l2.request > 0
    AND l2.sid = s2.sid
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;

---

✅ 2. DBA_BLOCKERS 및 DBA_WAITERS 뷰

📌 사용 가능 버전

뷰 이름	10g	11g	12c	18c	19c	21c	23c
DBA_BLOCKERS	✅	✅	✅	✅	✅	✅	✅
DBA_WAITERS	✅	✅	✅	✅	✅	✅	✅

 

-- 락을 보유한 세션 
SELECT * FROM dba_blockers; 
-- 대기 중인 세션 
SELECT * FROM dba_waiters;

---

✅ 3. v$session + v$lock + v$process 활용 (10g ~ 최신)

-- ✅ Oracle 10g ~ 23c 모두 사용 가능
SELECT
    l.session_id,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    l.lock_type,
    l.mode_held,
    l.mode_requested,
    l.blocking_others
FROM
    dba_locks l
JOIN
    v$session s ON l.session_id = s.sid
WHERE
    l.blocking_others = 'Blocking';

---

✅ 4. SQL 실행 중인 쿼리 추적 (12c 이상: SQL_ID 방식)

 

-- ✅ Oracle 12c 이상에서 정확한 추적 가능
SELECT
    s.sid, s.serial#, s.username, s.status,
    q.sql_text
FROM
    v$session s
JOIN
    v$sql q ON s.sql_id = q.sql_id
WHERE
    s.sid = <Waiter SID>;

🔸 10g/11g는 s.sql_hash_value로 연결 필요

-- Oracle 10g/11g용
SELECT s.sid, s.serial#, q.sql_text
FROM v$session s, v$sql q
WHERE s.sql_hash_value = q.hash_value AND s.sid = <SID>;

---

✅ 5. 프로세스 종료용 SPID 조회 (10g ~ 최신 동일)

-- Oracle 10g ~ 23c 공통
SELECT p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND s.sid = <SID>;

---

✅ 6. 세션 종료

sql

 

-- Oracle 10g ~ 최신 버전 공통
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

-- SPID 조회 방법 (Oracle → OS 매핑)
SELECT
    s.sid,
    s.serial#,
    p.spid AS "OS_PID",
    s.username,
    s.program
FROM
    v$session s,
    v$process p
WHERE
    s.paddr = p.addr
  AND s.sid = <SID>;

bash

 

-- OS 강제 종료 (Linux/Unix)
kill -9 <spid>

---

📚 출처 및 확인 문서

• Oracle 19c Docs: Monitoring Locks
• Oracle Support: 29256.1 - How to Identify and Resolve Locks
• Oracle Base: Blocking Locks in Oracle

---

✍️ 요약

쿼리 목적	사용 가능 버전
v$lock, v$session 매핑	10g ~ 23c
DBA_BLOCKERS, DBA_WAITERS	10g ~ 23c
SQL 추적 (SQL_ID 방식)	12c 이상 (정확도 ↑)
SPID 확인 및 종료	10g ~ 23c
세션 Kill	10g ~ 23c

 

---