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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s