[ORACLE] DBMS_LOCK_ALLOCATED 완전 가이드 : 락 할당 상태 분석과 성능 최적화 전략

[ORACLE] DBMS_LOCK_ALLOCATED

1. DBMS_LOCK과 DBMS_LOCK_ALLOCATED란?

DBMS_LOCK은 사용자 정의 락(user-defined lock)을 생성, 요청, 해제하는 Oracle 패키지입니다. DBMS_LOCK_ALLOCATED는 이 패키지를 통해 시스템이 할당한 락 리소스 현황을 조회할 수 있는 중요한 뷰로, 시스템 락 컨텐션과 리소스 병목 상태를 분석하는 데 활용됩니다.

2. DBMS_LOCK_ALLOCATED 뷰의 구조

컬럼명	설명
LOCKHANDLE	락 식별자 (32-char RAW 값)
SHORTNAME	락 별칭 (가독성을 위한 문자열 지정)
SERIAL#	세션 내부 시리얼 번호
SID	세션 식별자
REQUEST	락 요청 상태 (0=없음, 1=토킹, 2=대기)
MODE_HELD	현재 보유 중인 모드

3. 조회 예시 및 활용 방법

다음 쿼리는 현재 락 할당 및 요청 현황을 보여줍니다:

SELECT sid, serial#, shortname, request, mode_held
FROM v$lock_allocated
ORDER BY sid, request DESC;

REQUEST = 2는 세션이 락을 얻기 위해 대기 중인 상태를 의미하며, TTL(Time To Live)과 병행 분석이 필요합니다.

4. 실무 진단 시나리오

• 락 대기 증가 모니터링: REQUEST=2가 급증하면 SQL 튜닝이나 트랜잭션 분리 검토
• 세션별 락 리소스 추적: 특정 SID에서 락 과다 보유 여부 확인
• 상위 Shortname 분석: 동일 락 이름이 반복적으로 대기 상태라면 병목 유발 가능성
• 락 모드 충돌 탐지: MODE_HELD와 REQUEST 유형 분석

5. 자동 모니터링 및 경고 시스템 구성

다음은 PL/SQL을 이용한 락 대기 자동 감지 예시입니다:

DECLARE
  CURSOR c IS
    SELECT sid, request FROM v$lock_allocated WHERE request = 2;
BEGIN
  FOR rec IN c LOOP
    DBMS_OUTPUT.PUT_LINE('대기 중인 세션: ' || rec.sid);
  END LOOP;
END;

이와 함께 모니터링 대시보드에서 알림을 트리거하여 즉각 대응할 수 있습니다.

6. 장점과 한계 비교

항목	장점	한계
세밀한 락 상태 파악	세션별 상세 대기 및 보유 상태 분석 가능	시스템 락 이외 사용자 락만 해당
성능 병목 조기 발견	락 경쟁 상황을 빠르게 인지	실행계획 분석 병행 필요
자동화 연계	스크립트 및 경고 시스템 적용 용이	동시 사용자 수 많을 경우 리소스 부담

7. 보안 및 접근 권한 주의사항

• v$lock_allocated 조회는 일반적으로 DBA 권한 필요
• LOCKHANDLE 값은 내부 식별자이므로 외부 노출 금지
• 감사 또는 모니터링 로그는 주기적으로 클리어

8. 운영 전략 팁

• 높은 동시성 시스템에선 정기적으로 락 할당 상태 점검
• 트랜잭션 설계 시 커밋 타이밍 최적화
• 병렬 DML 또는 배치 작업 전후 모니터링 루틴 삽입
• 락 리포트 자동화 및 시각화 도구 연계

출처

• Oracle Database Reference 21c – Oracle Corporation
• Oracle Distributed Systems and Locking – 내부 DBA 기술 노트
• Oracle Performance Tuning Guide – Oracle Docs