Database Reference
In-Depth Information
The additional information, which is useful for identifying expensive row sources in the
execution plan, depends on the DBMS release and is summarized in Table 24-9.
Table 24-9.
STAT Execution Statistics
Parameter
Meaning
cr
Consistent reads
pr
Physical reads
pw
Physical writes
time
Estimated elapsed time in microseconds
cost
Cost of the execution plan calculated by CBO (requires Oracle11
g
)
size
Estimated data volume in bytes (requires Oracle11
g
); the estimate is based on
object statistics (
DBA_TABLES
, etc.)—information from the segment header is
used if object statistics are not available
card
Estimated cardinality, i.e., number of rows processed (requires Oracle11
g
); the
estimate is based on object statistics
Oracle11
g
STAT
entries have the most verbose format.
The following example (trace file excerpt) shows how the execution plan of a two-way join
is represented by
STAT
entries in an Oracle10
g
trace file:
PARSING IN CURSOR #4 len=140 dep=0 uid=5 oct=3 lid=5 tim=105385553438 hv=782962817
ad='670e3cf4'
SELECT e.last_name, e.first_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id
ORDER BY 1, 2
END OF STMT
…
STAT #4 id=1 cnt=106 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=115 pr=0 pw=0 time=5720
us)'
STAT #4 id=2 cnt=106 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=115 pr=0 pw=0 time=7302
us)'
STAT #4 id=3 cnt=107 pid=2 pos=1 obj=16496 op='TABLE ACCESS FULL EMPLOYEES (cr=7 pr=
0 pw=0 time=725 us)'
STAT #4 id=4 cnt=106 pid=2 pos=2 obj=16491 op='TABLE ACCESS BY INDEX ROWID DEPARTMEN
TS (cr=108 pr=0 pw=0 time=4336 us)'
STAT #4 id=5 cnt=106 pid=4 pos=1 obj=16492 op='INDEX UNIQUE SCAN DEPT_ID_PK (cr=2 pr
=0 pw=0 time=1687 us)'
When formatting execution plans, TKPROF preserves the additional information provided
by the parameter
op
.
