V$session 컬럼의 의미들 (wait_time, last_call_et,status)

출처: 과거에 수집된 자료라... 어딘지 모르겟네요....일단 공유하기 좋은 내용이라 공유합니다.

v$session모니터 하면서 궁금했을거 같은 상태를 과 이벤트를 DML을 수행하면서 테스트된 내용입니다.

[session /as sysdba]                                                              [session /test]
select username, status, wait_time, event,last_call_et
from v$session where username='TEST'


USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       INACTIVE          0 SQL*Net message from client              24
                                                                                select count(*) from test;

USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       INACTIVE          0 SQL*Net message from client               3==> 다시 증가

                                                                                delete from test where rownum <10000;

USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       ACTIVE            0 db file sequential read                   9

.
.
. ==========================================================================>   delete는 진행중
.                                                                               이때는 active


USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       ACTIVE            0 db file sequential read                  28
                                                                                9999 행이 삭제되었습니다.

                                                                                경   과: 00:00:28.09

USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       INACTIVE          0 SQL*Net message from client               3==>   commit/rollback을
                                                                                하지 않아도 inactive

                                                                                rollback 수행

USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       ACTIVE            0 db file sequential read                   3

.
. ==========================================================================>   rollback 시에도 delete와
.                                                                               마찬가지 결과를 보여준다.
.

USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       ACTIVE            0 db file sequential read                   9
                                                                                경   과: 00:00:08.62


USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       INACTIVE          0 SQL*Net message from client               0==>   다시 0 부터 시작한다.

.

USERNAME   STATUS    WAIT_TIME EVENT                          LAST_CALL_ET
---------- -------- ---------- ------------------------------ ------------
TEST       INACTIVE          0 SQL*Net message from client              88



================================================================================
결론

1. DML/ROLLBACK 시 진행중
   STATUS   :  ACTIVE
   WAIT_TIME:  0
   EVENT    :  db file sequential read
   LAST_CALL:  증가함

   즉 wait.sql에서 wait_time=0 and event not like '%message% 라면 IO 수행중

2. DML 끝나고 COMMIT/ROLLBACK 하지 않고 대기상태
   STATUS   :  INACTIVE로 변경
   WAIT_TIME:  변화 없이 0
   EVENT    :  SQL*Net message from client 로 변경
   LAST_CALL:  다시 0 부터 증가

3. COMMIT시
   STATUS   :  변화 없이 INACTIVE
   WAIT_TIME:  변화 없이 0
   EVENT    :  변화 없이 SQL*Net message from client
   LAST_CALL:  다시 0 부터 증가

즉 wait.sql에서 wait_time=0 이면서, event not like '%messte%는 IO를 하고 있어야 한다.
   만약 하지 않으면 이상한 것이라고 추정할 수 있다.

   wiat_time=0 이면서 event가 message라면 commit이나 rollback을 하지 않고
   대기 하는 상태임, 그러나 flush하면 sqlarea에서 없어진다. 즉 commit/rollback 하지
   않은 세션의 sql도 flush 될 수 있다.

결국 이상하다고 판단 할 수 있는 세션은 wait_time=0 and event not like '%message% 이며
io를 하지 않는 세션이라고 할 수 있다.

+++++++++++++++++++++++++++
DBMS_SCHEDULER
+++++++++++++++++++++++++++
참고: DBMS_SCHEDULER 가 v$session 에서 moduel인 경우 v$session의 action에 작업 이름이 있다.
      last_call_et는 증가 하며, event가 io 관련이며, v$sess_io에서 io도 증가한다.
Elapsed: 00:00:00.00
2007-03-19 22:25:02 : SYS@OEBEXDB1 > select action from v$session where sid=7588;

ACTION
--------------------------------
AUTO_SPACE_ADVISOR_JOB

       SID  WAIT_TIME EVENT                       LAST_CALL_ET
---------- ---------- --------------------------- ------------
      7588          0 db file scattered read              1499



++++++++++++++++++++++++
log file parallel write
++++++++++++++++++++++++

background의 경우 IO 없이 계속 active 이며, last_call_et도 증가할 수 있다.
아래 경우가 wait_time이 -1과 0을 오락가락 하면 event는 그대로인 상태에서 last_call_et는
wait_time에 상관없이 계속 증가한다.

SQL> select sid, event, status, wait_time, last_call_et, PROGRAM from v$session where sid=9896;

       SID EVENT                    STATUS    WAIT_TIME LAST_CALL_ET  PROGRAM
---------- ------------------------ -------- ---------- ------------  ------------------------------------------------
      9896 log file parallel write  ACTIVE           -1         2291  oracle@mebexdb1 (LGWR)

Elapsed: 00:00:00.02
SQL>  select * from v$sess_io where sid=9896;

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
      9896          0               0            316             0                  0