Database Reference
In-Depth Information
CHAPTER 11
■ ■ ■
X$KSLED and Enhanced
Session Wait Data
T
he X$ fixed table
X$KSLED
is an undocumented X$ fixed table. The dynamic performance view
V$SESSION_WAIT
is based on
X$KSLED
and
X$KSUSECST
.
X$KSLED
contributes the wait event name
to
V$SESSION_WAIT
whereas
X$KSUSECST
holds timing information. Neither Oracle9
i
nor Oracle10
g
have a V$ view that provides more than centisecond resolution for a single wait event at session
level.
1
There is also no view that integrates information on operating system processes found
in
V$PROCESS
with wait information. It's a bit cumbersome to correctly interpret the columns
WAIT_TIME
and
SECONDS_IN_WAIT
of the view
V$SESSION_WAIT
depending on the value of the
column
STATE
.
Direct access to X$ fixed tables makes it possible to get microsecond resolution for wait
events without the need to enable SQL trace. Furthermore, an enhanced version of
V$SESSION_
WAIT
, which combines information from
V$SESSION
,
V$SESSION_WAIT
and
V$PROCESS
and is easier to
interpret, may be built. Note that
V$SESSION
in Oracle9
i
does not include information on wait
events, whereas wait event information has been incorporated into
V$SESSION
in Oracle10
g
.
The enhanced session wait view presented in this chapter is compatible with Oracle9
i
, Oracle10
g
,
and Oracle11
g
.
Drilling Down from V$SESSION_WAIT
By drilling down from
V$SESSION_WAIT
, as presented in Chapter 9, it becomes apparent that this
view is based on
X$KSLED
and
X$KSUSECST
. Adding column alias names, which correspond to the
column names of
GV$SESSION
, to the view definition retrieved from
V$FIXED_VIEW_DEFINITION
,
yields the following
SELECT
statement:
SELECT s.inst_id AS inst_id,
s.indx AS sid,
s.ksussseq AS seq#,
e.kslednam AS event,
e.ksledp1 AS p1text,
s.ksussp1 AS p1,
s.ksussp1r AS p1raw,
In Oracle11
g
, the dynamic performance view
V$SESSION_WAIT
has the following new columns:
WAIT_TIME_MICRO, TIME_REMAINING_MICRO
, and
TIME_SINCE_LAST_WAIT_MICRO
.
1.
113
