V$Session_wait or V$Session 总结

The view V$Session_wait can show useful information about what a session is waiting for.
Multiple selects from this view can indicate if a session is moving or not.
When wait_time=0 the session is waiting , any other value indicates CPU activity.

v$session_wait is often misinterpreted.

Often people will assume we are waiting because see an event and seconds_in_wait is rising. It should be remembered that seconds_in_wait only applies to a current wait if wait_time =0 , otherwise it is actually "seconds since the last wait completed".

 

The other column of use to clear up the misinterpretation is state which will be WAITING if we are waiting and WAITED% if we are no longer waiting.

 

Each wait event has up to 3 additional parameters giving more information about the wait. Pn TEXT tells you what Pn represents.

 

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 when <<Parameter:TIMED_STATISTICS>> is set to FALSE . Set it to TRUE to get meaningful figures.

WAIT_TIME=-2是因为将 Parameter:TIMED_STATISTICS设置为FALSE

 

The WAIT_TIME column should only be 0 if the session is currently waiting for EVENT .

However, due to Bug:2117360 a value of "0" may show in Oracle9i when a session has actually finished waiting.

Use the value of the STATE column to check that the session is actually "WAITING " as this column shows the correct state.

 

 

Column	Vers	Datatype	Description
SID		NUMBER	Session identifier. Join to <<View:V$SESSION>> . SID
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.
P1TEXT		VARCHAR2	Description of first wait-event parameter (P1)
P1		NUMBER	First wait-event parameter in decimal
P1RAW		RAW(4 | 8)	First wait-event parameter in hexadecimal . Column size depends on platform . 32bit = RAW(4), 64bit=RAW(8)
P2TEXT		VARCHAR2	Description of second parameter
P2		NUMBER	Second parameter in decimal
P2RAW		RAW(4 | 8)	Second parameter in hexadecimal
P3TEXT		VARCHAR2	Description of third parameter
P3		NUMBER	Third additional parameter in decimal
P3RAW		RAW(4 | 8)	Third additional parameter in hexadecimal
WAIT_TIME		NUMBER	A nonzero value is the session's last wait time. A zero value means the session is currently waiting (except in Oracle 9.0 where Bug:2117360 occurs). 0 - the session is currently waiting -2 - duration of last wait is unknown -1 - last wait was <1/100th of a second in duration >0 - duration of last wait in 1/100th second units (10mS) <-2 - The internal time has probably wrapped
SECONDS_IN_WAIT	7.3+	NUMBER	When WAIT_TIME = 0 then this shows the number of seconds spent in the current wait (3 second granularity on most platforms / versions). This value should NOT reset on events with timeouts. Eg: The "enqueue" waitevent times out and re-waits (SEQ# increments) but SECONDS_IN_WAIT shows the cumulative time in wait. Unfortunately this column does get reset in versions 9201-9203 inclusive due to Bug:2803772 . When WAIT_TIME != 0 this column shows the number of seconds since the start of the last wait.
STATE	7.3+	VARCHAR2	Wait state: WAITING (the session is currently waiting) WAITED UNKNOWN TIME (duration of last wait is unknown) WAITED SHORT TIME (last wait <1/100th of a second) WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
WAIT_TIME_MICRO	11.1+	NUMBER	A nonzero value is the session's last wait time in microseconds, giving more precision than WAIT_TIME which is in 1/100th seconds