The v$session_wait view displays information about waits events for which active sessions are currently waiting.It provides detailed information about the event or resource that each session is waiting for. This view displays session-level wait information in real time. So i would like to describe columns of this view because many times i have heard question being asked “what does this column and values for it mean?”
To start with, lets first see what all columns does this view have :-
SQL> desc v$session_wait Name Null? Type --------------------------- -------- ------ SID NUMBER SEQ# NUMBER EVENT VARCHAR2(64) P1TEXT VARCHAR2(64) P1 NUMBER P1RAW RAW(4) P2TEXT VARCHAR2(64) P2 NUMBER P2RAW RAW(4) P3TEXT VARCHAR2(64) P3 NUMBER P3RAW RAW(4) WAIT_TIME NUMBER SECONDS_IN_WAIT NUMBER STATE VARCHAR2(19)
This description is from 9i database.In 10g you can see the 3 more columns (WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS) added to it.
SID :- Its the session identifier
EVENT :- Contains the name of the event.To get all the event names in the database you can query V$EVENT_NAME view.
P1,P2,P3 :- Contains specific information about the event and identify a specific resource the session is waiting for.
P1RAW,P2RAW,P3RAW :- Contains hexadecimal representation of the values in P1,P2,P3, respectively.
P1TEXT,P2TEXT,P3TEXT :-These columns provide description for the columns P1,P2,P3, respectively.
SEQ# :- it is an internal sequence number for the event related to the session.It increments each time the session waits on the event.
WAIT_TIME :- Records the amount of time the session has waited for in centiseconds.It can be categorized into three
1. < 0 ( negative value) :- A negative value denotes Unknown wait time.
2. = 0 (Equal to zero) :- A value equal to zero means that the session is still waiting.
3. > 0 (Greater than zero) :- A value greater than zero is the actual time wait.
If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT – WAIT_TIME / 100 is the active seconds since the last wait ended.
SECOND_IN_WAIT :- Shows wait time in seconds while the session is waiting on the event.
STATE :- Contains the current wait status.It has four possible values
1. WAITED UNKNOWN TIME :- Means that the TIMES_STATISTICS initialization parameter is set to FALSE and Oracle is unable to determine the wait time.In this case, the WAIT_TIME column shows -2
2. WAITED SHORT TIME :- Means the previous wait was less than one centisecond.In this case, the WAIT_TIME column shows -1
3. WAITING :- Means that the session is currently waiting and the WAIT_TIME column shows 0, but the time spent on this current wait can be determined from SECOND_IN_WAIT column. (Again note SECOND_IN_WAIT is in seconds, but WAIT_TIME is in centiseconds.)
4. WAITED KNOWN TIME :- Means Oracle is able to determine the duration of the last wait and the time posted in the WAIT_TIME column.
Hope this helps some of you.