Database Reference
In-Depth Information
e.ksledp2 AS p2text,
s.ksussp2 AS p2,
s.ksussp2r AS p2raw,
e.ksledp3 AS p3text,
s.ksussp3 AS p3,
s.ksussp3r AS p3raw,
decode(s.ksusstim,0,0,-1,-1,-2,-2,
decode(round(s.ksusstim/10000),0,- 1,round(s.ksusstim/10000)))
AS wait_time,
s.ksusewtm AS seconds_in_wait,
decode(s.ksusstim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1,
'WAITED SHORT TIME', 'WAITED KNOWN TIME') AS state
FROM x$ksusecst s, x$ksled e
WHERE bitand(s.ksspaflg,1)!=0
and bitand(s.ksuseflg,1)!=0
and s.ksussseq!=0
and s.ksussopc=e.indx
Note how the microsecond resolution in X$KSUSECST is artificially reduced to centisecond
resolution through the division by 10000. At reduced resolution, it is impossible to learn how
long short wait events such as db file sequential read , db file scattered read , or global cache related
wait events in Real Application Clusters (RAC) were. Wait times shorter than 1 centisecond are
displayed as -1 by V$SESSION_WAIT . At this resolution, it is impossible to see disk access times at
session level. Peaks in I/O service time also remain unnoticed, as long as the duration of the
wait events stays below 1 centisecond, which it normally will. Following is an example:
SQL> SELECT event, wait_time, seconds_in_wait, state
FROM v$session_wait
WHERE (state='WAITED KNOWN TIME' or state='WAITED SHORT TIME')
AND event !='null event';
EVENT WAIT_TIME SECONDS_IN_WAIT STATE
--------------------------- --------- --------------- -----------------
db file sequential read -1 0 WAITED KNOWN TIME
SQL*Net message from client -1 0 WAITED KNOWN TIME
SQL*Net message to client -1 0 WAITED KNOWN TIME
An Improved View
Now that the restrictions of V$SESSION_WAIT have become apparent, we may set goals for an
improved view. The goals are to
￿
Provide wait event duration at microsecond resolution
￿
Integrate process, session, and session wait information
￿
Present the wait status and wait time in a readily accessible format without requiring
further decoding by users of the view
 
Search WWH ::




Custom Search