오라클 V$SESSION.STATE가 WAITING인 경우

V$SESSION 뷰에서 STATE 값이 WAITING이면 해당 세션이 현재 어떤 이벤트를 기다리고 있다는 의미입니다.
즉, 실행 중이 아니라 특정 리소스(락, I/O, 네트워크, CPU 등) 대기 상태에 있다는 뜻입니다.

WAITING 상태의 주요 원인

  • V$SESSION에서 WAIT_CLASS와 EVENT 컬럼을 확인하면 대기 원인을 찾을 수 있습니다. 

SELECT sid, serial#, username, event, wait_class, seconds_in_wait 
  FROM v$session 
 WHERE state = 'WAITING';

  • 이 결과에서 WAIT_CLASS 값에 따라 원인을 분석할 수 있습니다.

1. 락(Lock)으로 인한 대기 
   • 예시 : 한 트랜잭션이 특정 테이블을 UPDATE했지만 COMMIT을 하지 않으면, 다른 트랜잭션이 같은 행을 수정하려 할 때 대기하게 됨.
  • 확인 방법

SELECT blocking_session, sid, serial#, event, wait_class 
  FROM v$session 
WHERE state = 'WAITING';

  •  해결방법
    - 락을 건 세션을 찾아 COMMIT 또는 ROLLBACK
    - 필요하면 강제로 세션 종료 (ALTER SYSTEM KILL SESSION 'SID,SERIAL#';)

2. 디스크 I/O로 인한 대기 
   • 예시 : 대량의 데이터를 읽거나 쓸 때 발생.
   • EVENT 예시 
      - db file sequential read : 인덱스 스캔, 테이블 액세스
      - db file scattered read : 풀 테이블 스캔

SELECT sid, serial#, event, p1, p2, p3 
  FROM v$session 
 WHERE wait_class = 'User I/O';

   • 해결 방법 
      - 적절한 인덱스 사용 (EXPLAIN PLAN 분석)
      - DBMS_STATS.GATHER_TABLE_STATS로 통계 업데이트
      - DB_CACHE_SIZE 조정
3. 네트워크 대기
   • 예시 : 클라이언트와의 통신 지연, 데이터 전송 대기.
   • EVENT 예시
     - SQL*Net message from client : 클라이언트 입력 기다리는 중
     - SQL*Net more data to client : 클라이언트로 데이터 전송 중
   • 확인 방법

SELECT sid, serial#, event, seconds_in_wait 
  FROM v$session 
 WHERE wait_class = 'Network';

   • 해결 방법
     - 클라이언트 애플리케이션 확인
     - 네트워크 지연 문제 해결
4) CPU 부족 (Scheduler)
   • 예시 : CPU가 부족해서 프로세스가 실행되지 못하고 대기하는 경우

SELECT sid, serial#, event, seconds_in_wait 
  FROM v$session 
 WHERE wait_class = 'Scheduler';

   • 해결 방법
     - CPU 사용률 확인 (top, vmstat 등)
     - 리소스 관리 정책(RESOURCE_MANAGER) 확인
     - 병렬 쿼리(PARALLEL_DEGREE_POLICY) 조정
5. UNDO/REDO 관련 대기 
   • 예시 : 트랜잭션 롤백 또는 redo log가 가득 찼을 때 발생.
   • EVENT 예시
     - log file sync : 트랜잭션 커밋 시 redo log에 기록하는 중
     - log file switch (checkpoint incomplete) : redo log가 꽉 차서 스위칭 중
     - 확인 방법

SELECT sid, serial#, event, seconds_in_wait 
  FROM v$session 
 WHERE wait_class = 'System I/O';

  • 해결 방법
     - Redo log 크기 증가
     - UNDO 테이블스페이스 크기 조정