-- 현재 DML_LOCKS 파라미터 값 확인
SHOW PARAMETER DML_LOCKS;

-- 또는
SELECT name, value, description
FROM   v$parameter
WHERE  name = 'dml_locks';

-- 현재 시스템에서 사용 중인 DML Lock(Enqueue) 현황 확인
SELECT inst_id,
       eq_type,
       total_req#,
       total_wait#,
       succ_req#,
       failed_req#
FROM   gv$enqueue_stat
WHERE  eq_type = 'TM'
ORDER BY inst_id;

-- 현재 활성화된 DML Lock 세부 현황
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       l.type,
       l.lmode,
       l.request,
       o.object_name,
       o.object_type
FROM   v$lock l
JOIN   v$session s ON l.sid = s.sid
JOIN   dba_objects o ON l.id1 = o.object_id
WHERE  l.type = 'TM'
ORDER BY s.sid;

-- SPFILE을 사용하는 경우 (재기동 후 적용)
ALTER SYSTEM SET DML_LOCKS = 10000 SCOPE = SPFILE;

-- 변경 후 DB 재기동
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- 변경 확인
SELECT name, value
FROM   v$spparameter
WHERE  name = 'dml_locks';

-- 나쁜 예시: 하나의 트랜잭션에서 수십 개 테이블 처리
BEGIN
    UPDATE table_01 SET col1 = 'A' WHERE condition;
    UPDATE table_02 SET col1 = 'B' WHERE condition;
    -- ... table_03 ~ table_99 반복 ...
    UPDATE table_99 SET col1 = 'Z' WHERE condition;
    COMMIT; -- 모든 DML 잠금이 이 시점까지 점유됨
END;
/

-- 좋은 예시: 논리 단위로 분리하여 중간 커밋
BEGIN
    -- 1단계: 마스터 테이블 처리
    UPDATE table_01 SET col1 = 'A' WHERE condition;
    UPDATE table_02 SET col1 = 'B' WHERE condition;
    COMMIT; -- 잠금 즉시 해제

    -- 2단계: 상세 테이블 처리
    UPDATE table_03 SET col1 = 'C' WHERE condition;
    UPDATE table_04 SET col1 = 'D' WHERE condition;
    COMMIT; -- 잠금 즉시 해제
END;
/

-- 대량 배치 처리 시 BULK COLLECT + FORALL + 중간 커밋 패턴
DECLARE
    TYPE t_id_list IS TABLE OF orders.order_id%TYPE;
    l_ids   t_id_list;
    l_limit NUMBER := 1000; -- 배치 사이즈

    CURSOR c_orders IS
        SELECT order_id FROM orders WHERE status = 'PENDING';
BEGIN
    OPEN c_orders;
    LOOP
        FETCH c_orders BULK COLLECT INTO l_ids LIMIT l_limit;
        EXIT WHEN l_ids.COUNT = 0;

        FORALL i IN 1 .. l_ids.COUNT
            UPDATE orders
            SET    status = 'PROCESSING',
                   updated_dt = SYSDATE
            WHERE  order_id = l_ids(i);

        COMMIT; -- 배치 단위 커밋으로 DML 잠금 슬롯 주기적 해제

    END LOOP;
    CLOSE c_orders;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        CLOSE c_orders;
        RAISE;
END;
/

-- 장시간 활성 상태의 트랜잭션 탐지 (10분 이상)
SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.status,
       s.last_call_et       AS idle_seconds,
       t.start_time,
       ROUND((SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 1440, 2) AS elapsed_min,
       t.used_ublk          AS undo_blocks_used
FROM   v$transaction t
JOIN   v$session s ON t.addr = s.taddr
WHERE  (SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 1440 > 10
ORDER BY elapsed_min DESC;

-- 특정 세션이 보유한 DML Lock 목록 확인
SELECT l.sid,
       s.serial#,
       s.username,
       s.machine,
       o.owner,
       o.object_name,
       o.object_type,
       DECODE(l.lmode,
              0, 'None',
              1, 'Null',
              2, 'Row Share',
              3, 'Row Exclusive',
              4, 'Share',
              5, 'Share Row Exclusive',
              6, 'Exclusive') AS lock_mode
FROM   v$lock l
JOIN   v$session s ON l.sid = s.sid
JOIN   dba_objects o ON l.id1 = o.object_id
WHERE  l.type = 'TM'
  AND  l.sid = :target_sid; -- 조사할 SID 입력

-- 문제 세션 강제 종료 (최후 수단)
-- 반드시 애플리케이션팀과 협의 후 수행할 것
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;

-- RAC 환경에서는 inst_id 추가
ALTER SYSTEM KILL SESSION '&sid,&serial#,@&inst_id' IMMEDIATE;

-- DML_LOCKS 사용률 모니터링 쿼리 (주기적으로 실행)
SELECT p.value                              AS dml_locks_limit,
       COUNT(l.type)                        AS current_tm_locks,
       ROUND(COUNT(l.type) / p.value * 100, 2) AS usage_pct,
       CASE
           WHEN ROUND(COUNT(l.type) / p.value * 100, 2) >= 80
           THEN '⚠️  경고: DML Lock 사용률 80% 초과!'
           ELSE '✅ 정상'
       END AS status
FROM   v$parameter p
LEFT JOIN v$lock l ON l.type = 'TM'
WHERE  p.name = 'dml_locks'
GROUP BY p.value;