Database Reference
In-Depth Information
The execution statistics, aggregated by type of database call, provide data in a tabular form. For each of them, the
following performance figures are given:
•
count
is the number of times database calls were executed.
•
cpu
is the total CPU time, in seconds, spent processing database calls.
•
elapsed
is the total elapsed time, in seconds, spent processing database calls. If this value
is higher than CPU time, the section about wait events found below the execution statistics
provides information about the resources or synchronization points waited for.
•
disk
is the number of blocks read with physical reads. Be careful—this isn't the number of
physical I/O operations. If this value is larger than the number of logical reads (disk > query +
current), it means that blocks spilled into the temporary tablespace. In this case, you can see
that at least 33,017 blocks (71,499-38,474-8) were read from it. This fact is confirmed later by
the statistics of row source operations and wait events.
•
query
is the number of blocks read with logical reads in consistent mode. Usually, this type of
logical read is used by queries.
•
current
is the number of blocks read with logical reads in current mode. Usually this type of
logical read is used by
INSERT
,
DELETE
,
MERGE
, and
UPDATE
statements to modify blocks.
•
rows
is the number of rows processed. For queries, this is the number of fetched rows. For
INSERT
,
DELETE
,
MERGE
, and
UPDATE
statements, this is the number of affected rows. In this
case, it's worth noting that 1,000,000 rows were fetched in 10,001 fetch calls. This means that
on average, each call fetched about 100 rows. Note that 100 is the prefetch size used in
PL/SQL. (Chapter 15 provides detailed information about the prefetch size.)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch
10001
6.49 11.92 71499 38474 8
1000000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10003 6.49 11.92
71499 38474
8 1000000
The following lines summarize basic information about parsing. The first two values (
Misses in library
cache
) provide the number of hard parses that occurred during parse and execute calls. If no hard parse occurred
during execute calls, that specific line is missing. The optimizer mode and the user who parsed the SQL statement are
shown. Note that the name of the user, in this case
chris
, is provided only when the
explain
argument is specified.
Otherwise, only the user identifier (in this case 34) is shown. The last piece of information is the recursive depth. It's
provided only for recursive SQL statements. SQL statements directly executed by an application have a depth of 0.
A depth of
n
(in this case 1) simply means that another SQL statement with depth
n-1
(in this case 0) has executed this
one. In our sample, the SQL statement at depth 0 is a PL/SQL block that was executed by SQL*Plus:
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 34 (CHRIS) (recursive depth: 1)
After the general information about parsing, you might see the execution plan. Actually, if the
explain
argument
is specified, it may be possible to see two of them. The first one, called
Row Source Operation
, is the execution plan
