V$SESSION

V$SESSION_WAIT

V$SESSION_WAIT displays the resources or events for which active sessions are waiting.

The following are tuning considerations:

P1RAW, P2RAW, and P3RAW display the same values as the P1, P2, and P3 columns, except that the numbers are displayed in hexadecimal.
The WAIT_TIME column contains a value of -2 on platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you want this column to reflect true wait times, then you must set the TIMED_STATISTICS initialization parameter to true. Remember that doing this has a small negative effect on system performance.

In previous releases, the WAIT_TIME column contained an arbitrarily large value instead of a negative value to indicate the platform did not have a fast timing mechanism.
The STATE column interprets the value of WAIT_TIME and describes the state of the current or most recent wait.

Column	Datatype	Description
`SID`	`NUMBER`	Session identifier
`SEQ#`	`NUMBER`	Sequence number that uniquely identifies this 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 additional parameter
`P1`	`NUMBER`	First additional parameter
`P1RAW`	`RAW(4)`	First additional parameter
`P2TEXT`	`VARCHAR2(64)`	Description of the second additional parameter
`P2`	`NUMBER`	Second additional parameter
`P2RAW`	`RAW(4)`	Second additional parameter
`P3TEXT`	`VARCHAR2(64)`	Description of the third additional parameter
`P3`	`NUMBER`	Third additional parameter
`P3RAW`	`RAW(4)`	Third additional parameter
`WAIT_CLASS_ID`	`NUMBER`	Identifier of the wait class
`WAIT_CLASS#`	`NUMBER`	Number of the wait class
`WAIT_CLASS`	`VARCHAR2(64)`	Name of the wait class
`WAIT_TIME`	`NUMBER`	A nonzero value is the session's last wait time. A zero value means the session is currently waiting.
`SECONDS_IN_WAIT`	`NUMBER`	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.
`STATE`	`VARCHAR2(19)`	Wait state: `0 - WAITING` (the session is currently waiting) `-2 - WAITED UNKNOWN TIME` (duration of last wait is unknown) `-1 - WAITED SHORT TIME` (last wait <1/100th of a second) `>0 - WAITED KNOWN TIME` (`WAIT_TIME` = duration of last wait)