커넥션 풀과 트랜잭션 경계가 DB에 남기는 흔적

SELECT username,
       program,
       status,
       COUNT(*)                 AS session_cnt,
       ROUND(AVG(last_call_et)) AS avg_idle_sec,
       MAX(last_call_et)        AS max_idle_sec
FROM   v$session
WHERE  username IS NOT NULL
GROUP BY username, program, status
ORDER BY session_cnt DESC;

INACTIVE 세션 수가 풀 최대치에 가깝고 idle 시간이 길면 커넥션이 반납되지 않고 있는 것입니다. 정상이라면 풀의 커넥션은 짧게 쓰이고 바로 돌아옵니다.

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et AS idle_sec,
       s.machine,
       s.module,
       t.start_time   AS tx_start,
       t.used_ublk    AS undo_blocks
FROM   v$session s
JOIN   v$transaction t ON t.ses_addr = s.saddr
WHERE  s.status = 'INACTIVE'
ORDER BY s.last_call_et DESC;

여기 잡히는 세션이 가장 위험합니다. 아무 일도 안 하면서 트랜잭션을 열어 두고 있어서 락과 UNDO를 계속 붙들고 있습니다. Part 11의 락 문제와 Part 10의 ORA-01555가 여기서 시작됩니다.

-- 세션 상한
SHOW PARAMETER sessions
SHOW PARAMETER processes

-- 현재 사용량과 최고치
SELECT resource_name,
       current_utilization,
       max_utilization,
       limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes', 'sessions');

max_utilization 이 limit_value 에 근접했다면 상한을 늘릴지, 애플리케이션 쪽 풀을 줄일지 판단해야 합니다. 인스턴스 수 × 인스턴스당 풀 크기가 상한 안에 들어오는지 계산하는 것이 먼저입니다.

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.module,
       s.machine,
       t.start_time,
       ROUND((SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS'))
             * 24 * 60, 1) AS tx_minutes,
       t.used_ublk         AS undo_blocks
FROM   v$transaction t
JOIN   v$session s ON s.saddr = t.ses_addr
ORDER BY t.start_time;

수십 분 이상 살아 있는 트랜잭션이 상시로 보인다면 설계를 봐야 합니다. 배치라면 커밋 단위를 쪼개고, 온라인이라면 트랜잭션 경계가 너무 넓게 잡혀 있는 것입니다.