X$KSLED and Enhanced Session Wait Data - Secrets of the Oracle Database - page 114

-- improved timing information from x$ksusecst
decode(s.ksusstim,
-2, 'WAITED UNKNOWN TIME',
-1,'LAST WAIT < 1 microsecond', -- originally WAITED SHORT TIME
0,'CURRENTLY WAITING SINCE '|| s.ksusewtm || 's',
'LAST WAIT ' || s.ksusstim/1000 || ' milliseconds (' ||
s.ksusewtm || 's ago)') wait_status,
to_number(decode(s.ksusstim,0,NULL,-1,NULL,-2,NULL, s.ksusstim/1000))
AS wait_time_milli
from x$ksusecst s, x$ksled e , x$ksuse se, x$ksupr p
where bitand(s.ksspaflg,1)!=0
and bitand(s.ksuseflg,1)!=0
and s.ksussseq!=0
and s.ksussopc=e.indx
and s.indx=se.indx
and se.ksusepro=p.addr;
The unit of wait time in the column WAIT_TIME_MILLI is 1 millisecond. Fractional millisec-
onds are preserved, yielding microsecond resolution. The column WAIT_STATUS indicates whether
the session is currently waiting or not. In case the session is waiting, it displays for how many
seconds it has already been waiting. For sessions that are not currently waiting, the duration of
the last wait event and the time that has elapsed since the last wait event began, are reported.
The following query confirms that wait events are actually timed with microsecond resolution:
SQL> SELECT sid, serial#, spid, username, event, wait_status, wait_time_milli
FROM x_$session_wait
WHERE wait_time_milli > 0 and wait_time_milli <10;
SID SERIAL# SPID USERNAME EVENT WAIT_STATUS WAIT_TIME_MILLI
--- ------- ------- -------- --------------- ------------------ ---------------
24 58259 1188090 SYS db file LAST WAIT 6.541 ms 6.541
sequential read (0 s ago)
22 48683 966786 SYS SQL*Net message LAST WAIT .003 ms .003
to client (0 s ago)
I have included the operating system process identifier SPID , represented by the column
KSUPRPID from the X$ fixed table X$KSUPR , in the view. This column corresponds to V$PROCESS.
SPID . It is useful for enabling SQL trace with ORADEBUG SETOSPID and ORADEBUG EVENT (see Chapter 37).
In a scenario where operating system tools, such as top , prstat , or nmon are used to identify
resource intensive processes (e.g., high I/O wait percentage), the column SPID provides instant
access to wait information based on the process identifier displayed by these tools. Figure 11-1
shows the result of a query on the view CV_$SESSION_WAIT that includes both the SPID and detailed
information on the last wait event.