Database Reference
In-Depth Information
Categorizing Wait Events
The ultimate goal is to automatically create a resource profile from an extended SQL trace file.
Even though the file format has been discussed in Chapter 24, some more preparations are
necessary to achieve this goal. An issue that has not yet been discussed is the categorization
of wait events into intra database call wait events and inter database call wait events. This is
necessary for correct response time accounting. Intra database call wait events occur within
the context of a database call. The code path executed to complete a database call consists not
only of CPU consumption, but may also engender waiting for resources such as disks, latches,
or enqueues. Time spent waiting within a database call is accounted for by intra database call
wait events. Examples of such wait events are
latch free,
enqueue
,
db file sequential read
,
db file
scattered read
, and
buffer busy waits
. In fact, most wait events are intra database call wait events.
Inter database call wait events occur when the DBMS server is waiting to receive the next data-
base call. In other words, the DBMS server is idle, since the client does not send a request.
According to Millsap and Holt ([MiHo 2003], page 88), the following wait events are inter
(or between) database call wait events:
SQL*Net message from client
SQL*Net message to client
pmon timer
smon timer
rdbms ipc message
Of these wait events
pmon timer
,
smon timer
, and
rdbms ipc message
solely occur in back-
ground processes. Thus, the only inter database call wait events, which are relevant to tuning
an application, are
SQL*Net message from client
and
SQL*Net message to client.
1
Figure 27-1 is a graphical representation of database calls, wait events, and transaction
entries (
XCTEND
) from an extended SQL trace file. The X axis represents time (t), while the Y axis
represents the recursive call depth (
dep
). Inter database call wait events are depicted in white
font against a dark background. Note that all of these wait events are either associated with a
cursor number
n
(
n
> 0) at recursive call depth 0 or the default cursor 0.
1.
The list in [MiHo 2003] also contains
pipe get
and
single-task message
. I have omitted
pipe get
, since
my testing showed that this wait event occurs when an execute database call on the PL/SQL package
DBMS_PIPE
is made. When
DBMS_PIPE.RECEIVE_MESSAGE
is called with a non-zero timeout, time spent
waiting for a message is accounted for with the wait event
pipe get
and is rolled up into the parameter
e
of the associated
EXEC
entry. Thus,
pipe get
is an intra database call wait event.
All implementations of the ORACLE DBMS since Oracle9
i
are two-task implementations only, i.e., server
process and client process are separate tasks, each running in its own address space. Hence the wait
event
single-task message
is no longer used (see also Metalink note 62227.1).