Oracle: V$SESSION

Columns

The columns P1, P2 and P3 are parameters that are dependant on the event. With Oracle 10g, v$session_wait's information will be exposed within v$session as well.

`SID`	Identifier that identifies the session which waits for the event described in the record.
`SEQ#`	A unique number, incremented for each wait
`EVENT`	A textual description of the event that the session is waiting for (if `STATE = 'WAITING`) or the most recent event waited for (if `STATE <> 'WAITING'`) - See MOS Note 2171986.1
`P1TEXT`, `P2TEXT`, `P3TEXT`	A textual description of the 1st, 2nd, 3rd parameter of the wait.
`P1`, `P2`, `P3`	Numerical value of the 1st, 2nd, 3rd parameter.
`P1RAW`, `P2RAW`, `P3RAW`	RAW-value of the 1st, 2nd, 3rd parameter.
`WAIT_CLASS_ID`	Identifier of wait class (Compare with `v$session_wait_class` and `v$system_wait_class`).
`WAIT_CLASS#`	Identifier of wait class (Range 1 … 10)
`WAIT_CLASS`	Textual description of wait class
`WAIT_TIME`	0: session is waiting, -1 Last wait duration was less than 1/100 of a second, -2: parameter `timed_statistics` is `false`, otherwise: duration of last wait in 1/100 of a second.
`SECONDS_IN_WAIT`	deprecated in favor of `WAIT_TIME_MICRO` and `TIME_SINCE_LAST_WAIT_MICRO`
`STATE`	`WAITING`, `WAITED UNKNOWN TIME` (only if parameter `timed_statistics` is false), `WAITED SHORT TIME` (wait time less than 1/100 s), `WAITED KNOWN TIME` (Actual wait time is displayed in `WAIT_TIME`). Compare with column `STATUS` in `v$session`.
`WAIT_TIME_MICRO`	Time waited in microseconds of last wait (if not waiting) or duration of current wait (if waiting)
`TIME_REMAINING_MICRO`	`null`: Session is not waiting. `-1`: Session can indefinitely wait in the current wait. `>0`: Amount of microseconds remaining. `0`: Wait has timed out.
`HEUR_TIME_WAITED_MICRO`	New column with Oracle 21c
`TIME_SINCE_LAST_WAIT_MICRO`	0: session is waiting, else time elapsed since last wait
`CON_ID`

Columns replicated in V$SESSION

Since 10g, Oracle displays the information found in v$session_wait also in v$session.

Query

select
  sesw.sid,
  sesw.seq#,
  sesw.event,
  -----------------------------------------------
  case sesw.p1text 
       when 'file#' then dbfi.name
       else  sesw.p1text || ': ' || sesw.p1
       end                                    p1,
  -----------------------------------------------
  case sesw.p2text
       when 'block#' then 'block ' || sesw.p2
       else  sesw.p2text || ': '|| sesw.p2
       end                                    p2,
  -----------------------------------------------
  case sesw.p3text
       when 'blocks' then sesw.p3 || ' blocks'
       else  sesw.p3text || ': '|| sesw.p3
       end                                    p3
  -----------------------------------------------       
from
  v$session_wait  sesw                                    left join
  v$dbfile  dbfi on sesw.p1 = dbfi.file#

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/session/wait/show-waits.sql

Oracle: V$SESSION_WAIT

Columns

Columns replicated in V$SESSION

Query

See also