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).
 
Search WWH ::




Custom Search