V$SESSION_WAIT

V$SESSION_WAIT displays the current or last wait for each session.

Column Datatype Description
SID NUMBER Session identifier; maps to V$SESSION.SID
SEQ# NUMBER A number that uniquely identifies the current or last wait (incremented for each wait)
EVENT VARCHAR2(64) Resource or event for which the session is waiting

See Also: Appendix C, "Oracle Wait Events"

P1TEXT VARCHAR2(64) Description of the first wait event parameter
P1 NUMBER First wait event parameter (in decimal)
P1RAW RAW(8) First wait event parameter (in hexadecimal)Foot 1 
P2TEXT VARCHAR2(64) Description of the second wait event parameter
P2 NUMBER Second wait event parameter (in decimal)
P2RAW RAW(8) Second wait event parameter (in hexadecimal)Footref 1
P3TEXT VARCHAR2(64) Description of the third wait event parameter
P3 NUMBER Third wait event parameter (in decimal)
P3RAW RAW(8) Third wait event parameter (in hexadecimal)Footref 1
WAIT_CLASS_ID NUMBER Identifier of the class of the wait event
WAIT_CLASS# NUMBER Number of the class of the wait event
WAIT_CLASS VARCHAR2(64) Name of the class of the wait event
WAIT_TIME NUMBER If the session is currently waiting, then the value is 0. If the session is not in a wait, then the value is as follows:
  • > 0 - Value is the duration of the last wait in hundredths of a second

  • -1 - Duration of the last wait was less than a hundredth of a second

  • -2 - Parameter TIMED_STATISTICS was set to false

This column has been deprecated in favor of the columns WAIT_TIME_MICRO and STATE.

SECONDS_IN_WAIT NUMBER If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.

This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO.

STATE VARCHAR2(19) Wait state:
  • WAITING - Session is currently waiting

  • WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false

  • WAITED SHORT TIME - Last wait was less than a hundredth of a second

  • WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column

WAIT_TIME_MICRO NUMBER Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait.
TIME_REMAINING_MICRO NUMBER Value is interpreted as follows:
  • > 0 - Amount of time remaining for the current wait (in microseconds)

  • 0 - Current wait has timed out

  • -1 - Session can indefinitely wait in the current wait

  • NULL - Session is not currently waiting

TIME_SINCE_LAST_WAIT_MICRO NUMBER Time elapsed since the end of the last wait (in microseconds). If the session is currently in a wait, then the value is 0.

Footnote 1 The P1RAW, P2RAW, and P3RAW columns display the same values as the P1, P2, and P3 columns, except that the numbers are displayed in hexadecimal.