Database Reference
In-Depth Information
Table 24-8.
STAT Parameters
Parameter
Meaning
id
Identifier that denotes the order of row sources in the execution plan; normally
id=1
on the first
STAT
line of an execution plan.
cnt
Number of rows processed.
pid
Parent identifier; normally
pid=0
on the first
STAT
line of an execution plan.
TKPROF and ESQLTRCPROF use
id
and
pid
to generate properly indented
execution plans with dependent steps of a plan indented by one more level
than their parent steps.
pos
Position of a step within the parent step.
obj
Object identifier; corresponds to
ALL_OBJECTS.OBJECT_ID
and
V$SQL_PLAN.OBJECT#
.
op
Row source operation performed, such as table access, index scan, sort, union,
and so on; corresponds to
V$SQL_PLAN.OPERATION
. In Oracle10
g
,
op
contains actual
statement execution metrics in parentheses after the row source information.
STAT Entry Format in Oracle9
i
The only difference between Oracle9
i
and Oracle10
g
STAT
entries is the amount of information
conveyed with the parameter
op
. The inclusion of actual execution metrics in
op
is not imple-
mented in Oracle9
i
. Following is an example of a hash join, which is the parent row source of
two full table scans:
STAT #3 id=1 cnt=106 pid=0 pos=1 obj=0 op='SORT ORDER BY '
STAT #3 id=2 cnt=106 pid=1 pos=1 obj=0 op='HASH JOIN '
STAT #3 id=3 cnt=27 pid=2 pos=1 obj=6764 op='TABLE ACCESS FULL DEPARTMENTS '
STAT #3 id=4 cnt=107 pid=2 pos=2 obj=6769 op='TABLE ACCESS FULL EMPLOYEES '
The preceding
STAT
entries would be formatted by TKPROF as follows:
Rows Row Source Operation
------- -------------------------------
106 SORT ORDER BY
106 HASH JOIN
27 TABLE ACCESS FULL DEPARTMENTS
107 TABLE ACCESS FULL EMPLOYEES
STAT Entry Format in Oracle10
g
and Oracle11
g
In Oracle10
g
and Oracle11
g
, as opposed to Oracle9
i
,
STAT
entries are only written when
TIMED_
STATISTICS=TRUE
in addition to a SQL trace level of at least 1. Note that setting
STATISTICS_LEVEL=
BASIC
(default is
TYPICAL
) in Oracle10
g
and Oracle11
g
implicitly sets
TIMED_STATISTICS=FALSE
. This
behavior may be overridden by explicitly setting
TIMED_STATISTICS=TRUE
. Except for some
additional statistics in parentheses at the end of the operation (bold in the following code
example),
STAT
entries in Oracle9
i
and subsequent releases have the same format:
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)'
