세션과 블로킹 진단

#핵심 요약

대기 이벤트는 세션이 CPU에서 실행되지 않고 자원을 기다리는 상태의 이름임
Idle이 아닌 이벤트와 대기 시간으로 병목을 분류함

대기 이벤트는 WAIT_CLASS로 묶임
Application(락), User I/O, Concurrency, Cluster(RAC 전용), Idle 등이며 Idle은 분석 대상에서 뺌

다른 세션이 커밋하지 않은 행을 변경하려 하면 첫 트랜잭션이 끝날 때까지 enq: TX - row lock contention(Application 클래스)으로 기다림

락을 가진 세션(Holder)이 그대로면 기다리는 세션(Waiter)만 처리해서는 해소되지 않음
업무를 확인해 Holder가 커밋, 롤백하게 하고, 불가능하면 Holder를 종료함

RAC에서는 Holder와 Waiter가 서로 다른 인스턴스에 있을 수 있음
GV$SESSION의 BLOCKING_INSTANCE, BLOCKING_SESSION, INST_ID로 찾음

#개념과 동작 원리

실습의 조치 순서: 대기 이벤트로 원인을 분류하고, 그 이벤트의 해결 방법을 공식 문서에서 확인하고, 조치 후 다시 조회해 검증함

주의: RAC에서는 노드마다 같은 SID가 있을 수 있음
실습 출력에서 SID 209가 1번 노드의 Holder(<SCHEMA>, INACTIVE)와 2번 노드의 다른 세션 두 군데에 나왔음
세션을 지정할 때는 반드시 INST_ID와 SERIAL#까지 함께 봄
단일 인스턴스의 세션 조회는 세션 점검에 있음

#필수 명령어 또는 SQL

#대기 이벤트와 대기 클래스

SELECT COUNT(*) FROM v$event_name;

SELECT wait_class, COUNT(*)
  FROM v$event_name
 GROUP BY wait_class
 ORDER BY 2 DESC;

SELECT inst_id, sid, event, wait_class
  FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND wait_class <> 'Idle'
 ORDER BY inst_id, event;

결과 해석: 개별 실행 출력은 기재 없음

#행 락 대기 재현

-- 커밋하지 않음
UPDATE emp SET sal = 7000 WHERE ename = 'ALLEN';

-- 멈춘 채 대기
UPDATE emp SET sal = 0 WHERE ename = 'ALLEN';

결과 해석: 개별 실행 출력은 기재 없음

#Waiter와 Holder 찾기

SELECT inst_id, sid, event, blocking_instance, blocking_session
  FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND event LIKE 'enq: TX%';

결과 해석:

실습 결과: INST_ID 2, SID 246, enq: TX - row lock contention, BLOCKING_INSTANCE 1
2번 노드 세션이 1번 노드 세션을 기다림

SELECT inst_id, sid, serial#, username, status, prev_sql_id
  FROM gv$session
 WHERE (inst_id, sid) IN (SELECT blocking_instance, blocking_session
                            FROM gv$session
                           WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND event LIKE 'enq: TX%');

결과 해석:

GV$LOCK으로도 같은 자원을 대조할 수 있음(두 번째 실습)

SELECT inst_id, sid, type, id1, id2, lmode, request
  FROM gv$lock
 WHERE type = 'TX'
 ORDER BY id1, id2, inst_id;

결과 해석: 같은 TYPE, ID1, ID2에서 LMODE=6, REQUEST=0인 행이 Holder, LMODE=0, REQUEST=6인 행이 Waiter임(실습 출력: 1번 노드 SID 209가 Holder, 2번 노드 SID 212가 Waiter)
TM 락(LMODE=3)도 함께 보임
LMODE 숫자의 공식 의미는 기재 없음

#Holder 종료

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>,@<INST_ID>' IMMEDIATE;

바꿀 입력값: <SID>, <SERIAL#>, <INST_ID>는 실행 직전에 위 조회로 다시 확인한 값
결과 해석: Holder의 미커밋 작업은 롤백되고, 롤백 시간에 따라 Waiter가 풀리는 시점이 늦어질 수 있음
해소 후 Waiter의 추가 대기 여부를 다시 조회함
실습 Waiter는 대기가 풀린 뒤 실습 변경을 ROLLBACK했음

#같은 블록의 다른 행과 같은 행

실습에서는 dbms_rowid로 SCOTT과 KING 행이 같은 파일, 블록에 있는 것을 확인한 뒤, 1번 노드가 SCOTT 행을 고치고 커밋하지 않은 상태에서 2번 노드가 KING 행을 고치자 바로 성공했고, 2번 노드가 SCOTT 행을 고치자 enq: TX - row lock contention으로 멈췄음
블록은 노드 사이를 오가지만 행 갱신 락은 행 단위임

#확인 문제: 같은 행을 기다리는 두 Waiter의 공통 Holder

상황: 2번 노드의 인사 담당자 두 명이 SMITH 사원의 부서와 커미션을 각각 고치려는데 둘 다 멈춤
실습에서는 1번 노드가 SMITH 행을 UPDATE하고 커밋하지 않은 채, 2번 노드 두 창에서 deptno와 comm을 각각 고침

SELECT inst_id, sid, event FROM gv$session WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND event LIKE 'enq: TX%';

SELECT inst_id, sid, blocking_instance, blocking_session
  FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND event LIKE 'enq: TX%';

SELECT inst_id, sid, serial#, username
  FROM gv$session
 WHERE inst_id = 1 AND sid = <HOLDER_SID>;

결과 해석:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>,@1' IMMEDIATE;

SELECT COUNT(*) FROM gv$session WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND event LIKE 'enq: TX%';

결과 해석:

서로 다른 컬럼이어도 같은 행을 고치면 두 Waiter 모두 기다림

예상값: TX 대기 2건, 두 Waiter의 Holder가 모두 1번 노드의 같은 세션, 종료 뒤 대기 0건(또는 두 Waiter끼리의 대기 1건)

실습이 끝나면 2번 노드 두 창에서 ROLLBACK함
실행 결과 확인 불가

#사용 조건과 주의사항

세션 종료는 그 세션의 작업을 잃게 하므로 업무 확인 후 판단함

실습 두 번째 페이지에는 SQL Developer로 SCAN 리스너에 접속하려고 VM 라우팅과 방화벽(NAT)을 바꾼 기록이 있음