Database Reference
In-Depth Information
when the
explain
argument is specified. Because it's generated later, it doesn't necessarily match the first one. In any
case, if you see a difference between the two, the first is the correct one.
Chapter 10 describes how to read an execution plan; here I'm describing only the particularities of TKPROF.
Both execution plans provide, for the first execution found in the trace file, the number of rows returned (not
processed—be careful) by each operation in the execution plan. In addition to information about the first execution,
version 11.2.0.2 and higher also provides the average and maximum number of rows returned over all executions. The
number of executions itself is provided by the
Number of plan statistics captured
value.
For each row source operation, the following runtime statistics might also be provided:
•
cr
is the number of blocks read with logical reads in consistent mode.
•
pr
is the number of blocks read with physical reads from the disk.
•
pw
is the number of blocks written with physical writes to the disk.
•
time
is the total elapsed time in microseconds spent processing the operation. Be aware that
the value provided by this statistic isn't always very precise. That's because, to reduce the
overhead, the server process may use sampling to measure it.
•
cost
is the estimated cost of the operation. This value is available only as of version 11.1.
•
size
is the estimated amount of data (in bytes) returned by the operation. This value is
available only as of version 11.1.
•
card
is the estimated number of rows returned by the operation. This value is available only as
of version 11.1.
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1000000 1000000 1000000 SORT ORDER BY (cr=38474 pr=
71499
pw=33035 time=11123996 us
cost=216750 size=264000000 card=1000000)
1000000 1000000 1000000 TABLE ACCESS FULL T (cr=
38474
pr=
38463
pw=0 time=5674541 us
cost=21 size=264000000 card=1000000)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1000000 SORT (ORDER BY)
1000000 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)
Note that the runtime statistics, except for the query optimizer estimations, are cumulative—that is, they include
the values of the child row source operations. For example, the number of blocks read from the temporary tablespace
during the
SORT ORDER BY
operation is 33,036 (71,499 - 38,463). From the previous execution statistics (see the
discussion about the
disk
column), you were able to estimate only that there were at least 33,017. Also note that
although in older versions these values are related to the first execution, as of version 11.2.0.2 they're averages over all
executions; in this case, there's no difference because there was only one execution.
