PostgreSQL, Oracle DB 모니터링

POSTGRESQL · ORACLE · DB MONITORING

운영 중인 시스템에서 저장 버튼이 눌렸는데 반응이 없거나, 특정 화면만 유독 느려지고, 배치 작업이 끝나지 않은 것처럼 보이는 상황을 한 번쯤은 겪게 됩니다. 이런 문제는 애플리케이션 코드만의 문제가 아니라 데이터베이스 세션 대기, 장시간 실행 쿼리, Commit 누락, Lock 충돌 때문에 발생하는 경우가 많습니다. 이번 글에서는 PostgreSQL과 Oracle 환경에서 어떤 기준으로 DB를 모니터링해야 하는지, 현재 Lock 상태를 어떻게 확인해야 하는지, 문제가 되는 세션은 어떤 방식으로 해제해야 하는지를 실무 흐름 중심으로 정리하겠습니다.

목차

• DB 모니터링이 왜 중요한가
• PostgreSQL에서 확인해야 하는 모니터링 포인트
• Oracle에서 확인해야 하는 모니터링 포인트
• PostgreSQL에서 Lock 확인과 해제 방법
• Oracle에서 Lock 확인과 해제 방법
• 운영 중 조치할 때 주의할 점

DB 모니터링이 왜 중요한가

데이터베이스는 서비스의 가장 안쪽에서 실제 데이터를 처리하는 핵심 계층입니다. 사용자는 웹 화면이나 프로그램 화면에서 단순히 저장, 조회, 삭제만 수행하는 것처럼 보이지만, 그 뒤에서는 세션 연결, 트랜잭션 시작, SQL 실행, Lock 획득, Commit 또는 Rollback과 같은 과정이 순차적으로 이뤄지고 있습니다.

따라서 화면이 느려지는 현상이나 저장이 멈춘 것처럼 보이는 증상은 실제로는 DB 내부에서 누군가가 자원을 오래 점유하고 있거나, 특정 세션이 다른 세션들을 대기시키고 있는 경우가 많습니다. 이런 상황을 빠르게 파악하려면 평소부터 모니터링 기준을 정리해 두고, 이상 징후가 발생했을 때 어디부터 확인할지를 알고 있어야 합니다.

운영 장애는 대부분 갑자기 발생한 것처럼 보이지만, 실제로는 이미 세션 증가, 장시간 실행 쿼리, idle in transaction, Lock 대기 같은 징후가 누적되고 있었던 경우가 많습니다.

실무에서 자주 보는 대표적인 징후는 아래와 같습니다.

• 특정 사용자만 저장이 안 되는 것이 아니라 여러 사용자가 동시에 느리다고 말하는 경우
• 같은 화면에서만 반복적으로 응답 지연이 발생하는 경우
• 배치 시간대와 온라인 업무 시간이 겹칠 때 성능 저하가 심해지는 경우
• 트랜잭션이 끝나지 않은 세션이 오랫동안 살아 있는 경우
• 한 세션이 잡은 Lock 때문에 다른 작업들이 연쇄적으로 대기하는 경우

결국 DB 모니터링은 단순히 수치를 보는 것이 아니라, 현재 서비스 흐름이 정상적으로 돌고 있는지 확인하는 운영 기술이라고 볼 수 있습니다.

PostgreSQL에서 확인해야 하는 모니터링 포인트

PostgreSQL에서는 기본적으로 제공되는 시스템 뷰만 잘 활용해도 많은 문제를 추적할 수 있습니다. 가장 대표적인 대상은 pg_stat_activity와 pg_locks입니다. pg_stat_activity는 현재 살아 있는 세션과 실행 중인 쿼리 상태를 보여주고, pg_locks는 어떤 Lock이 어떤 세션에 의해 점유되고 있는지를 보여줍니다.

현재 실행 중인 세션과 SQL 확인

문제 상황이 발생하면 가장 먼저 현재 어떤 세션이 살아 있는지를 확인해야 합니다. 특히 state가 idle이 아닌 세션, query_start 이후 오래 실행되고 있는 세션, wait_event가 표시되는 세션은 우선적으로 볼 필요가 있습니다.

PostgreSQL · active session 확인

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

 

위 결과에서 특히 확인할 것은 아래와 같습니다.

• duration 값이 비정상적으로 긴 세션이 있는지
• state가 active인지, idle in transaction인지
• wait_event_type과 wait_event가 무엇인지
• 어떤 application_name으로 접속했는지
• 어떤 SQL이 반복적으로 오래 수행되고 있는지

Lock 상태 상세 확인

세션 상태만 보는 것으로 부족할 때는 pg_locks와 pg_stat_activity를 같이 조회하여 어떤 Lock 타입이 걸려 있는지, 실제로 granted 상태인지, 기다리는 중인지까지 확인할 수 있습니다.

PostgreSQL · lock 상세 조회

SELECT
    a.pid,
    a.usename,
    a.application_name,
    a.state,
    a.wait_event_type,
    a.wait_event,
    l.locktype,
    l.mode,
    l.granted,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a
  ON l.pid = a.pid
ORDER BY a.pid, l.granted, l.locktype;

granted 값이 false인 경우는 해당 Lock을 아직 획득하지 못하고 대기 중이라는 의미입니다. 이 결과를 통해 단순히 느린 쿼리인지, 아니면 자원 대기 때문인지 구분하는 데 도움이 됩니다.

Oracle에서 확인해야 하는 모니터링 포인트

Oracle은 PostgreSQL과 유사한 문제를 다른 뷰를 통해 확인합니다. 대표적으로 V$SESSION, V$SQL, V$LOCK가 자주 사용됩니다. 현재 어떤 세션이 살아 있는지, 어떤 SQL을 수행 중인지, 어떤 세션이 다른 세션을 막고 있는지를 순서대로 보는 것이 일반적입니다.

현재 세션과 실행 SQL 확인

Oracle에서는 현재 접속 세션, 프로그램 이름, 접속 장비, SQL_ID 등을 함께 보는 것이 좋습니다. 이 정보가 있어야 실제 사용자 작업인지, 특정 인터페이스 프로그램인지, 배치 작업인지 구분할 수 있습니다.

Oracle · session 및 SQL 확인

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    s.sql_id,
    q.sql_text
FROM v$session s
LEFT JOIN v$sql q
  ON s.sql_id = q.sql_id
WHERE s.username IS NOT NULL
ORDER BY s.logon_time DESC;

결과를 볼 때는 아래 항목을 같이 확인하면 좋습니다.

• 동일 machine 또는 program에서 세션이 과도하게 많은지
• 같은 SQL_ID가 반복적으로 오래 남아 있는지
• 특정 사용자나 특정 인터페이스에서만 문제가 집중되는지

대기 세션과 blocking session 확인

Oracle에서는 blocking_session 컬럼을 통해 현재 어떤 세션이 다른 세션을 막고 있는지 확인할 수 있습니다. seconds_in_wait가 길다면 이미 사용자는 저장 지연이나 화면 멈춤을 체감하고 있을 가능성이 큽니다.

Oracle · blocking session 확인

SELECT
    sid,
    serial#,
    username,
    blocking_session,
    event,
    wait_class,
    seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

PostgreSQL에서 Lock 확인과 해제 방법

PostgreSQL에서 Lock 문제를 볼 때 핵심은 누가 기다리고 있는지가 아니라, 실제로 누가 Lock을 잡고 있는지를 찾는 것입니다. blocked 세션보다 blocking 세션이 원인인 경우가 대부분이기 때문입니다.

현재 막히고 있는 세션 확인

pg_blocking_pids() 함수는 특정 세션을 막고 있는 PID 목록을 반환합니다. 이를 이용하면 현재 대기 상태에 있는 세션을 빠르게 골라낼 수 있습니다.

PostgreSQL · blocking PID 확인

SELECT
    pid,
    usename,
    application_name,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

blocked 세션과 blocking 세션을 함께 확인

운영에서는 단순히 PID 하나만 보는 것보다, 누가 대기 중이고 누가 막고 있는지를 한 화면에서 같이 보는 것이 훨씬 편합니다. 아래 쿼리는 blocked 세션과 blocking 세션을 함께 보여주는 예시입니다.

PostgreSQL · blocked / blocking 관계 확인

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

이 결과를 보면 실제 원인이 되는 세션이 어떤 SQL을 수행 중인지 비교적 쉽게 파악할 수 있습니다. 저장 버튼이 안 눌리는 사용자 쪽 세션보다, 먼저 Lock을 잡고 오래 유지한 세션이 더 중요합니다.

먼저 쿼리만 취소하는 방법

PostgreSQL에서는 바로 세션을 죽이기 전에 현재 실행 중인 쿼리만 취소할 수 있습니다. 장시간 조회 SQL이나 단순히 오래 걸리는 작업이라면 이 방법을 먼저 검토하는 편이 안전합니다.

PostgreSQL · 쿼리 취소

SELECT pg_cancel_backend(12345);

위 예시에서 12345는 종료 대상 PID입니다. 이 방법은 현재 실행 중인 SQL만 취소하고 세션 자체는 유지합니다.

세션 자체를 종료하는 방법

Commit 누락, idle in transaction 상태 장기 유지, 비정상 세션 유지처럼 명확히 문제를 일으키는 세션이라면 세션 종료를 검토할 수 있습니다.

PostgreSQL · 세션 종료

SELECT pg_terminate_backend(12345);

조치 방법	설명	권장 상황
pg_cancel_backend	실행 중인 쿼리만 취소합니다.	장시간 조회, 일시적 지연, 취소 가능한 SQL
pg_terminate_backend	세션 자체를 강제로 종료합니다.	Commit 누락, 장기 Lock 점유, 비정상 세션

운영 환경에서는 세션 종료 전에 반드시 해당 세션이 어떤 사용자 작업인지, 어떤 프로그램에서 발생한 것인지, 종료했을 때 다른 후속 작업에 영향은 없는지 확인하는 것이 좋습니다.

Oracle에서 Lock 확인과 해제 방법

Oracle에서도 접근 방식은 같습니다. 기다리는 세션보다 막고 있는 세션을 찾고, 해당 세션이 실제로 어떤 작업을 수행 중인지 확인한 뒤, 필요할 경우 세션 종료를 검토합니다.

Lock 대기 세션 확인

먼저 현재 대기 중인 세션을 확인합니다. blocking_session 값이 있다는 것은 현재 다른 세션 때문에 기다리고 있다는 의미입니다.

Oracle · lock 대기 세션 확인

SELECT
    sid,
    serial#,
    username,
    blocking_session,
    event,
    wait_class,
    seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

Lock 보유 세션 상세 확인

더 자세하게 보고 싶다면 V$LOCK와 V$SESSION을 조합하여 어떤 세션이 어떤 형태의 Lock을 오래 유지하고 있는지 확인할 수 있습니다. 이 단계에서는 단순히 기다리는 쪽이 아니라 실제 Lock을 보유한 쪽의 지속 시간과 상태를 확인하는 것이 중요합니다.

Oracle · lock 보유 세션 상세 조회

SELECT
    s.sid,
    s.serial#,
    s.username,
    l.type,
    l.id1,
    l.id2,
    l.lmode,
    l.request,
    l.ctime
FROM v$lock l
JOIN v$session s
  ON l.sid = s.sid
ORDER BY l.ctime DESC;

ctime 값은 해당 Lock이 얼마나 오래 유지되고 있는지 확인하는 데 도움이 됩니다. 동일 사용자나 동일 프로그램에서 지속적으로 오래 잡히는 패턴이 있다면 트랜잭션 구조 자체를 다시 점검할 필요가 있습니다.

문제 세션 종료 방법

Oracle에서는 일반적으로 문제 세션을 확인한 뒤 ALTER SYSTEM KILL SESSION 명령으로 종료를 수행합니다. 이때는 SID와 SERIAL 값을 함께 사용해야 합니다.

Oracle · 세션 종료

ALTER SYSTEM KILL SESSION '123,4567';

여기서 123은 SID이고 4567은 SERIAL 값입니다. 보통 V$SESSION 조회 결과를 바탕으로 실제 종료 대상을 결정합니다.

Oracle은 kill 명령을 내려도 세션이 즉시 완전히 사라지지 않을 수 있습니다. 롤백이 진행 중이거나 내부 정리 작업이 남아 있는 경우에는 KILLED 상태로 일정 시간 유지될 수 있습니다.

운영 중 조치할 때 주의할 점

Lock 문제를 푸는 것은 단순히 세션을 끊는 작업이 아닙니다. 왜 그 세션이 오래 살아 있었는지, 왜 Commit이 되지 않았는지, 왜 같은 시간대에 반복되는지를 함께 봐야 재발을 줄일 수 있습니다.

특히 운영 환경에서는 사용자의 실제 업무가 진행 중일 수 있고, 배치나 인터페이스와 연결된 세션일 수도 있기 때문에 조치 순서를 명확히 가져가는 것이 중요합니다.

• 먼저 blocked 세션보다 blocking 세션을 정확히 찾습니다.
• 해당 세션이 화면 작업인지, 배치인지, 인터페이스인지 구분합니다.
• PostgreSQL은 가능하면 cancel부터 검토하고, 필요 시 terminate로 넘어갑니다.
• Oracle은 kill 이후에도 롤백 및 정리 시간이 필요할 수 있다는 점을 고려합니다.
• 반복되는 경우 SQL 구조, 인덱스, 트랜잭션 범위, Commit 시점을 점검해야 합니다.
• idle in transaction 상태가 자주 발생한다면 애플리케이션 예외 처리 흐름도 함께 확인해야 합니다.

가장 위험한 조치는 원인 파악 없이 세션을 무조건 종료하는 것입니다. 일시적으로 증상이 사라질 수는 있지만, 데이터 정합성 문제나 후속 장애를 더 크게 만들 수 있습니다.

마무리

PostgreSQL과 Oracle 모두 기본 제공 시스템 뷰만 잘 활용해도 현재 세션 상태, 장시간 실행 쿼리, Lock 대기 관계를 충분히 파악할 수 있습니다. 운영 환경에서는 장애가 발생한 뒤 당황해서 하나씩 찾기보다, 평소에 자주 보는 조회 SQL과 판단 기준을 정리해 두는 것이 훨씬 중요합니다.

결국 핵심은 단순합니다. 누가 살아 있는지 보고, 누가 막고 있는지 찾고, 어떤 조치가 가장 안전한지 판단하는 흐름을 익혀두는 것입니다. 이 흐름이 정리되어 있으면 DB 장애 대응 속도와 정확도는 확실히 달라집니다.

 

태그

PostgreSQL Oracle DB모니터링 DB Lock 트랜잭션 세션관리 운영장애 SQL운영 개발자실무 데이터베이스