Database Reference
In-Depth Information
The following section summarizes the wait events for which the SQL statement waited. The following values are
provided for each type of wait event:
•
Times Waited
is the number of times a wait event has occurred.
•
Max. Wait
is the maximum wait time in seconds for a single wait event.
•
Total Waited
is the total wait time in seconds for a wait event.
Elapsed times include waiting on following events:
Event waited on Times Waited Max. Wait Total Waited
----------------------------------- ------------ ---------- ------------
db file sequential read 2 0.00 0.00
db file scattered read 530 0.06 2.79
direct path write temp 11002 0.00 0.51
direct path read temp 24015 0.00 2.41
Ideally, the sum of the wait time for all wait events should be equal to the difference of the elapsed time and CPU
time provided by the execution statistics. The difference, if available, is called
unaccounted-for time
.
UNaCCOUNteD-FOr tIMe
sQl trace provides information on how much time the database spends on each operation it executes. ideally, the
calculation should be very precise. unfortunately, it's uncommon to find a trace file that gives exact information
with the precision of a fraction of a second. Whenever there's a difference between the real elapsed time and the
time accounted for in trace files, you have
unaccounted-for time
:
unaccounted-for time = real elapsed time - accounted for time
the most common reasons for unaccounted-for time are the following:
the most obvious is the absence of timing information or wait events in the trace files. the
•
former happens when the
timed_statistics
initialization parameter is set to
FALSE
. the latter
happens when sQl trace is activated without level 8. in both cases, the unaccounted-for time
is always a positive value. naturally, correctly enabling extended sQl trace will help you avoid
these problems.
Generally speaking, a process may be in three states: running on a Cpu, waiting for the
•
fulfillment of a request (for example, for the fulfillment of a disk i/o operation), or waiting for
a Cpu in the run queue. the instrumentation code is able to calculate the time spent in the first
two states, but has no clue about how much time is spent waiting in the run queue. therefore,
in case of Cpu starvation, the unaccounted-for time, which is always a positive value, could
be quite long. basically, you can avoid this problem in only two ways: either by increasing the
amount of available Cpu or by decreasing the Cpu utilization.
the time measurements performed by the instrumentation code are precise. nevertheless,
•
there's a small quantization error in every measurement because of the implementation of
timers in computer systems. especially when there are a high number of measured events,
these quantization errors could lead to noticeable unaccounted-for time. owing to their nature,
quantization errors could lead to positive as well as negative values for unaccounted-for time.
