꿈꾸는 개발자, DBA 커뮤니티 구루비

V$SESSION_WAIT 뷰 & V$SESSION_EVENT 뷰

V$SESSION_WAIT 뷰

V$SESSION_WAIT 뷰는 현재 특정 세션이 대기하고 있는 대기 이벤트 정보를 보여주며, 이 뷰의 모든 컬럼은 V$SESSION 뷰에도 포함되어 있음.


DESC V$SESSION_WAIT
;

Name                  Null?    Type
--------------------- -------- -----------------
SID                            NUMBER
SEQ#                           NUMBER
EVENT                          VARCHAR2(64)
P1TEXT                         VARCHAR2(64)
P1                             NUMBER
P1RAW                          RAW(8)
P2TEXT                         VARCHAR2(64)
P2                             NUMBER
P2RAW                          RAW(8)
P3TEXT                         VARCHAR2(64)
P3                             NUMBER
P3RAW                          RAW(8)
WAIT_CLASS_ID                  NUMBER
WAIT_CLASS#                    NUMBER
WAIT_CLASS                     VARCHAR2(64)
WAIT_TIME                      NUMBER
SECONDS_IN_WAIT                NUMBER
STATE                          VARCHAR2(19)
WAIT_TIME_MICRO                NUMBER
TIME_REMANING_MICRO            NUMBER
TIME_SINCE_LAST_WAIT_MICRO     NUMBER

EVENT	대기 이벤트 ex) db file sequeitial read
P1, P1RAW	PARAMETER1의 값
P2, P2RAW	PARAMETER2의 값
P3, P3RAW	PARAMETER3의 값
P1TEXT, P2TEXT, P3TEXT	V$EVENT_NAME 뷰의 PARAMETER1, PARAMETER2, PARAMETER3 값과 동일
WAIT_CLASS	대기 이벤트의 종류 ex) User I/O
WAIT_TIME	가장 최근의 대기 시간. STATE 컬럼의 값이 WAITED KNOWN TIME 일 경우에만 의미를 가짐. 단위는 1/100초이며, 11g부터 WAIT_TIME_MICRO 컬럼으로 대체됨
SECONDS_IN_WAIT	현재 대기시간. STATE 컬럼의 값이 WAITING일 경우에만 의미를 가짐. 단위는 1초이며, 11g부터 WAIT_TIME_MICRO 컬럼과 TIME_SINCE_LAST_WAIT_MICRO 컬럼으로 대체됨
STATE	대기 상태. 이 컬럼의 값이 WAITING일 경우에만 실제 대기하고 있는 중임
WAIT_TIME_MICRO	STATE 컬럼의 값이 WAITING일 경우에는 현재 대기 시간을, 그렇지 않을 경우에는 가장 최근의 대기 시간을 의미
TIME_SINCE_LAST_WAIT_MICRO	가장 최근의 대기 이후 지난 시간을 의미함. STATE 컬럼의 값이 WAITING이 아닐 경우에만 의미가 있으며, 단위는 1/1,000,000 초임

STATE 컬럼 의미

WAITING	현재 실제로 대기하고 있는 중이라는 것을 의미함. 이 경우 SECOND_IN_WAIT 컬럼이나 WAIT_TIME_MICRO 컬럼의 값을 통해 현재 얼마나 대기하고 있는지 알 수 있음.
WAITED UNKNOWN TIME	알수 없는 시간만큼 대기한 후 현재는 CPU를 점유하고 어떤 작업을 하고 있는 중이라는 것을 의미함. 최근 버전의 오라클에서 이 값은 관찰되지 않음
WAITED SHORT TIME	1/100초 이하의 짧은 시간을 대기한 후 현재는 CPU를 점유하고 어떤 작업을 하고 있는 중이라는 것을 의미함. 이 경우 WAIT_TIME_MICRO 컬럼 값을 통해 대기했던 시간을 알 수 있으며, 컬럼 값은 -1로 표시
WAITED KNOWN TIME	1/100초 이상의 시간을 대기한 후 현재는 CPU를 점유하고 어떤 작업을 하고 있는 중이라는 것을 의미함 WAIT_TIME 컬럼과 WAIT_TIME_MICRO 컬럼의 값을 통해 대기했던 시간을 알 수 있음


원칙은 STATE 컬럼의 값이 WAITING 일 경우, 즉 실제로 현재 대기하고 있는 중일 경우에만 V$SESSION_WAIT 뷰를 사용하고, 대기 시간은 SECONDS_IN_WAIT 컬럼이나 WAIT_TIME_MICRO 컬럼을 통해 얻는다.

V$SESSION_EVENT 뷰

V$SESSION_EVENT 뷰의 누적 대기 회수와 누적 대기 시간을 알고 싶을 경우 사용.


DESC V$SESSION_EVENT
;

Name                  Null?    Type
--------------------  -------- ----------------
SID                           NUMBER
EVENT                         VARCHAR2(64)
TOTAL_WAITS                   NUMBER
TOTAL_TIMEOUTS                NUMBER
TIME_WAITED                   NUMBER
AVERAGE_WAIT                  NUMBER
MAX_WAIT                      NUMBER
TIME_WAITED_MICRO             NUMBER
EVENT_ID                      NUMBER
WAIT_CLASS_ID                 NUMBER
WAIT_CLASS#                   NUMBER
WAIT_CLASS                    VARCHAR2(64)

EVENT	대기 이벤트 이름 ex) db file sequential read
TOTAL_WAITS	전체 대기 회수
TOTAL_TIMEOUTS	전체 타임 아웃 회수
TIME_WAITED	전체 대기 시간. 1/100초 단위
AVERAGE_WAIT	평균 대기 시간. 1/100초 단위
MAX_WAIT	최대 대기 시간. 1/100초 단위
TIME_WAITED_MICRO	전체 대기 시간. 1/1,000,00초 단위
WAIT_CLASS	대기 이벤트의 종류 ex) User I/O

TOTAL_WAITS 컬럼과 TIME_WAITED 컬럼의 값을 통해 전반적인 대기 상황을 파악할 수 있음.
V$SESSION_EVENT 뷰의 한가지 제약은 파라미터 정보(P1, P2, P3)가 제공되지 않음. 파라미터 정보까지 포함하면 조합의 수가 많아져서 데이터 량이 지나치게 커질 우려가 있기 때문.

V$SESSION_WAIT 뷰 & V$SESSION_EVENT 뷰 비교_1

Session #1

소스코드


DROP TABLE T1 PURGE;

CREATE TABLE T1(C1 NUMBER);

-- 2. 데이터 생성
INSERT INTO T1 VALUES(1);

COMMIT;

-- 3. TX Lock Holder
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('session_1');

UPDATE T1 SET C1 = 1;

수행내역

 
KENZO:xsoftdb:SYSTEM > 
  1  CREATE TABLE T1(C1 NUMBER);

테이블이 생성되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  INSERT INTO T1 VALUES(1);

1 개의 행이 만들어졌습니다.

KENZO:xsoftdb:SYSTEM > 
  1  COMMIT;

커밋이 완료되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('session_1');

PL/SQL 처리가 정상적으로 완료되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  UPDATE T1 SET C1 = 1;

1 행이 갱신되었습니다.

Session #2

소스코드


-- 1. TX Lock Wait
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('session_2');

UPDATE T1 SET C1 = 1;

수행내역

 

KENZO:xsoftdb:SYSTEM > 
  1  EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('session_2');

PL/SQL 처리가 정상적으로 완료되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  UPDATE T1 SET C1 = 1;

Session #3 V$SESSION_WAIT 확인

소스코드


-- 1. Session #2 sid 획득
col sid new_value sid2

SELECT SID
FROM   V$SESSION
WHERE  CLIENT_INFO = 'session_2';

-- 2. TX Lock 확인
SET serveroutput ON

BEGIN
    FOR idx IN 1..100 LOOP
    
        DBMS_LOCK.SLEEP(0.1);
        
        PRINT_TABLE('SELECT ' || idx || ' AS NTH,
                            EVENT,
                            SEQ#,
                            P1,
                            P2,
                            P3,
                            TRUNC(WAIT_TIME / 100, 2) AS OLD_LAST_WAIT_TIME,
                            SECONDS_IN_WAIT OLD_CUR_WAIT_TIME,
                            STATE,
                            TRUNC(WAIT_TIME_MICRO            / 1000000, 2) AS NEW_CUR_WAIT_TIME,
                            TRUNC(TIME_REMAINING_MICRO       / 1000000, 2) AS NEW_REMAND_TIME,
                            TRUNC(TIME_SINCE_LAST_WAIT_MICRO / 1000000, 2) AS NEW_LAST_WAIT_TIME
                     FROM   V$SESSION_WAIT
                     WHERE  SID = &SID2');
        
    END LOOP;
END;
/

수행내역

 
KENZO:xsoftdb:SYSTEM > 
  1  col sid new_value sid2

KENZO:xsoftdb:SYSTEM > 
  1  SELECT SID
  2  FROM   V$SESSION
  3  WHERE  CLIENT_INFO = 'session_2';
                    

1 개의 행이 선택되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  SET serveroutput ON

KENZO:xsoftdb:SYSTEM > 
  1  BEGIN
  2      FOR idx IN 1..100 LOOP
  3  
  4          DBMS_LOCK.SLEEP(0.1);
  5  
  6          PRINT_TABLE('SELECT ' || idx || ' AS NTH,
  7                              EVENT,
  8                              SEQ#,
  9                              P1,
 10                              P2,
 11                              P3,
 12                              TRUNC(WAIT_TIME / 100, 2) AS OLD_LAST_WAIT_TIME,
 13                              SECONDS_IN_WAIT OLD_CUR_WAIT_TIME,
 14                              STATE,
 15                              TRUNC(WAIT_TIME_MICRO            / 1000000, 2) AS NEW_CUR_WAIT_TIME,
 16                              TRUNC(TIME_REMAINING_MICRO       / 1000000, 2) AS NEW_REMAND_TIME,
 17                              TRUNC(TIME_SINCE_LAST_WAIT_MICRO / 1000000, 2) AS NEW_LAST_WAIT_TIME
 18                       FROM   V$SESSION_WAIT
 19                       WHERE  SID = &SID2');
 20  
 21      END LOOP;
 22  END;
 23  /


PL/SQL 처리가 정상적으로 완료되었습니다.

Session #3 V$SESSION_EVENT 확인

소스코드


SELECT EVENT,
       TOTAL_WAITS,
       TRUNC(TIME_WAITED / 100, 2) AS OLD_TIME_WAITED,
       TRUNC(TIME_WAITED_MICRO / 1000000, 2) AS NEW_TIME_WAITED
FROM   V$SESSION_EVENT
WHERE  SID = &SID2
ORDER  BY 2 DESC,
          3 DESC
;

수행내역

 
KENZO:xsoftdb:SYSTEM > 
  1  SELECT EVENT,
  2         TOTAL_WAITS,
  3         TRUNC(TIME_WAITED / 100, 2) AS OLD_TIME_WAITED,
  4         TRUNC(TIME_WAITED_MICRO / 1000000, 2) AS NEW_TIME_WAITED
  5  FROM   V$SESSION_EVENT
  6  WHERE  SID = &SID2
  7  ORDER  BY 2 DESC,
  8            3 DESC
  9  ;

EVENT                           TOTAL_WAITS OLD_TIME_WAITED NEW_TIME_WAITED      
------------------------------- ----------- --------------- ---------------      
SQL*Net message from client              35          173.96          173.95      
SQL*Net message to client                35               0               0      
enq: TX - row lock contention             1          458.98          458.97      
Disk file operations I/O                  1               0               0      

4 개의 행이 선택되었습니다.

V$SESSION_WAIT 뷰의 100번째에 해당하는 값이 V$SESSION_EVENT 뷰의 집계값과 거의 일치하는 것이 확인 가능함.

V$SESSION_WAIT 뷰 & V$SESSION_EVENT 뷰 비교_2

Session #1 TEMP.SQL 파일 호출

소스코드


-- TEMP_T.SQL 파일 생성
BEGIN
    FOR idx IN 1..50 LOOP
    
        UPDATE T1 
        SET    C1 = 1;
        
        FOR REC_VAL IN (SELECT *
                        FROM   USER_OBJECTS) LOOP
                        
            NULL;
        
        END LOOP;
        
        COMMIT;
        DBMS_LOCK.SLEEP(0.1);
    
    END LOOP;
END;

-- SESSION_ID 확인
SELECT S.SID
FROM   V$PROCESS   P,
       V$SESSION   S,
       V$PARAMETER PARA
WHERE  P.ADDR = S.PADDR
AND    S.AUDSID = USERENV('SESSIONID')
AND    PARA.NAME = 'db_name'
;

-- TEMP_T.SQL 파일 호출
@TEMP_T;

수행내역

 
KENZO:xsoftdb:SYSTEM > 
  1  SELECT S.SID
  2  FROM   V$PROCESS   P,
  3         V$SESSION   S,
  4         V$PARAMETER PARA
  5  WHERE  P.ADDR = S.PADDR
  6  AND    S.AUDSID = USERENV('SESSIONID')
  7  AND    PARA.NAME = 'db_name'
  8  ;

       SID                                   
----------                                   
        15                                   

1 개의 행이 선택되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  @TEMP_T;
 19  /

PL/SQL 처리가 정상적으로 완료되었습니다.

Session #2 TEMP.SQL 파일 호출

세션_2에서 TEMP 파일 호출하여 row_level lock을 하도록 유도

소스코드


-- CLIENT_INFO 설정
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('session_2');

-- TEMP_T.SQL 파일 호출
@TEMP_T;

수행내역

 
KENZO:xsoftdb:SYSTEM > 
  1  EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('session_2');

PL/SQL 처리가 정상적으로 완료되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  @TEMP_T;
 19  /

PL/SQL 처리가 정상적으로 완료되었습니다.

Session #3

세션_1과 세션_2에서 작업을 수행하는 동안, 세션_3에서 세션_2의 상태 체크

소스코드


SET serveroutput ON

-- 1. Session #2 sid 획득
col sid new_value sid2

SELECT SID
FROM   V$SESSION
WHERE  CLIENT_INFO = 'session_2';

-- 2. Session #2 모니터링
BEGIN
    FOR idx IN 1..100 LOOP
    
        DBMS_LOCK.SLEEP(0.1);
        
        PRINT_TABLE('SELECT ' || idx || ' AS NTH,
                            EVENT,
                            SEQ#,
                            P1,
                            P2,
                            P3,
                            TRUNC(WAIT_TIME / 100, 2) AS OLD_LAST_WAIT_TIME,
                            SECONDS_IN_WAIT OLD_CUR_WAIT_TIME,
                            STATE,
                            TRUNC(WAIT_TIME_MICRO            / 1000000, 2) AS NEW_CUR_WAIT_TIME,
                            TRUNC(TIME_REMAINING_MICRO       / 1000000, 2) AS NEW_REMAND_TIME,
                            TRUNC(TIME_SINCE_LAST_WAIT_MICRO / 1000000, 2) AS NEW_LAST_WAIT_TIME
                     FROM   V$SESSION_WAIT
                     WHERE  SID = &SID2');
        
    END LOOP;
END;

수행내역

 
KENZO:xsoftdb:SYSTEM > 
  1  SET serveroutput ON

KENZO:xsoftdb:SYSTEM > 
  1  col sid new_value sid2

KENZO:xsoftdb:SYSTEM > 
  1  SELECT SID
  2  FROM   V$SESSION
  3  WHERE  CLIENT_INFO = 'session_2';

1 개의 행이 선택되었습니다.

KENZO:xsoftdb:SYSTEM > 
  1  BEGIN
  2      FOR idx IN 1..100 LOOP
  3  
  4          DBMS_LOCK.SLEEP(0.1);
  5  
  6          PRINT_TABLE('SELECT ' || idx || ' AS NTH,
  7                              EVENT,
  8                              SEQ#,
  9                              P1,
 10                              P2,
 11                              P3,
 12                              TRUNC(WAIT_TIME / 100, 2) AS OLD_LAST_WAIT_TIME,
 13                              SECONDS_IN_WAIT OLD_CUR_WAIT_TIME,
 14                              STATE,
 15                              TRUNC(WAIT_TIME_MICRO            / 1000000, 2) AS NEW_CUR_WAIT_TIME,
 16                              TRUNC(TIME_REMAINING_MICRO       / 1000000, 2) AS NEW_REMAND_TIME,
 17                              TRUNC(TIME_SINCE_LAST_WAIT_MICRO / 1000000, 2) AS NEW_LAST_WAIT_TIME
 18                       FROM   V$SESSION_WAIT
 19                       WHERE  SID = &SID2');
 20  
 21      END LOOP;
 22  END;
 23  /

-- 1) enq: TX - row lock contention
-- 아쉽지만 V$SESSION_WAIT에서는 enq: TX - row lock contention 이벤트는 확인되지 않음
-- 하지만, V$SESSION_EVENT에서는 확인됨
-- 책의 내용을 옮기자면, SECOND_IN_WAIT 컬럼(OLD_CUR_WAIT_TIME) 값은 항상 0인데,
-- 대기 시간이 매우 짧기 때문에 초 단위의 값에는 반영이 안되고 있음
-- 하지만, TIME_WAIT_MICRO 컬럼(NEW_CUR_WAIT_TIME) 값은 약 0.1초 단위로 계속해서 증가하고 있으며,
-- TIME_WAIT_MICRO 컬럼이 정확도가 훨씬 높은 것을 알 수 있음



-- 2) direct path read
-- direct path read 이벤트 또한 확인이 되지 않음 -_-;
-- 책의 내용을 옮기자면, oracle 11g부터는 버퍼 캐시에 비해 크기가 크다고 판단되는 테이블을 읽을 때,
-- 버퍼 캐시를 경유하지 않고 프로세스가 데이터파일에 직접 읽는 방식을 사용할 경우가 있으며, 
-- 이것을 흔히 'Serial Direct Path Read'라고 한다. 
-- 이 때, 대기 이벤트는 'direct path read'이며, 기존 direct path read로 동일 하지만 SEQ#, Pl, P2 의 값이 변경됨                                                                                                                                                                                  



-- 3) PL/SQL lock timer
-- DBMS.LOCK_SLEEP 프로시저를 이용해서 잠을 자는 동안 'PL/SQL lock timer' 이벤트 대기함
-- 이 이벤트 또한 V$SESSION_WAIT에서는 확인되지 않으며, V$SESSION_EVENT에서 확인됨

Session #3 V$SESSION_EVENT 확인

소스코드


SELECT EVENT,
       TOTAL_WAITS,
       TRUNC(TIME_WAITED / 100, 2) AS OLD_TIME_WAITED,
       TRUNC(TIME_WAITED_MICRO / 1000000, 2) AS NEW_TIME_WAITED
FROM   V$SESSION_EVENT
WHERE  SID = &SID2
ORDER  BY 2 DESC,
          3 DESC
;

수행내역

 
KENZO:xsoftdb:SYSTEM > 
  1  SELECT EVENT,
  2         TOTAL_WAITS,
  3         TRUNC(TIME_WAITED / 100, 2) AS OLD_TIME_WAITED,
  4         TRUNC(TIME_WAITED_MICRO / 1000000, 2) AS NEW_TIME_WAITED
  5  FROM   V$SESSION_EVENT
  6  WHERE  SID = &SID2
  7  ORDER  BY 2 DESC,
  8            3 DESC
  9  ;

EVENT                            TOTAL_WAITS OLD_TIME_WAITED NEW_TIME_WAITED
-------------------------------- ----------- --------------- ---------------
PL/SQL lock timer                         50            5.32            5.32
SQL*Net message to client                 43               0               0
SQL*Net message from client               42            24.5            24.5
enq: TX - row lock contention              2             .02             .02
log file sync                              1               0               0
Disk file operations I/O                   1               0               0
latch: In memory undo latch                1               0               0

7 개의 행이 선택되었습니다.