Database Reference
In-Depth Information
•
if you can eliminate the other possible reasons listed here, it's likely that the problem is because
the instrumentation code doesn't cover the whole code. for example, the writing of the trace file
itself isn't accounted for. this is usually not a problem. but, if the trace files are written to a poorly
performing device or the generation of trace information is very high, this can lead to a substantial
overhead. in this case, the unaccounted-for time will always be a positive value. to avoid this
problem, you should simply write trace files on a device that's able to sustain the necessary
throughput. in some rare situations, you may be forced to put the trace files on a ram disk.
Because the values of the wait events are highly aggregated, they help you know only which type of resource you
have been waiting for. For example, according to the previous information, virtually the whole wait time was spent
executing physical reads, but due to the aggregation, we can't, for example, see which data files were accessed (this
information is included in the raw trace file). In fact,
db file sequential read
is the wait event related to
single-block reads, and
db file scattered read
is the wait event related to multiblock reads (additional information
about multiblock reads is given in Chapter 9). In addition, the
direct path write temp
and
direct path read
temp
waits are related to the spill into the temporary tablespace.
In the analysis of the wait events, the key is knowing which operation they're related to. Fortunately, even if
there are hundreds of wait event types, the most recurring ones are usually of only a few types. You can find a short
description of most of them in the appendixes of the
Oracle Database Reference
manual.
The analysis continues with the next SQL statement. Because the structure of the information is the same as
before, I comment only when something new or inherently different is present in the output file:
DECLARE
l_count INTEGER;
BEGIN
FOR c IN (SELECT extract(YEAR FROM d), id, pad
FROM t
ORDER BY extract(YEAR FROM d), id)
LOOP
NULL;
END LOOP;
FOR i IN 1..10
LOOP
SELECT count(n) INTO l_count
FROM t
WHERE id < i*123;
END LOOP;
END;
The execution statistics for a PL/SQL block are limited. No information about physical and logical reads is
available. This is because the resources consumed by the recursive SQL statements (for example, the query analyzed
earlier) aren't associated to the parent SQL statement. This means that for each SQL statement (or PL/SQL block),
you'll see only the resources used by the SQL statement (or PL/SQL block) itself:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.44 0.40 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.45 0.41 0 0 0 1
