Database Reference
In-Depth Information
The preceding
SELECT
statement retrieves captured measurements since instance startup.
It does not take the snapshot interval into account, such that it may miss statements that were
slow intermittently, but performed acceptably on average.
Identifying Used Indexes
Statspack snapshots at level 6 or higher capture execution plans in addition to the usual
measurement data. Since index usage monitoring with
ALTER
INDEX
MONITORING
is somewhat
intrusive (see Chapter 4), you might consider examining index usage by accessing the
Statspack repository (script
sp_used_indexes.sql
).
SQL> SELECT DISTINCT o.owner, o.object_name index_name
FROM dba_objects o, stats$sql_plan p
WHERE o.object_id=p.object#
AND o.object_type='INDEX'
AND o.owner='HR';
OWNER INDEX_NAME
------------ -------------
HR JOB_ID_PK
HR LOC_ID_PK
HR EMP_EMP_ID_PK
HR DEPT_ID_PK
Execution Plans for Statements Captured with
SQL Trace
When tracing applications with SQL trace, it may happen that execution plans for certain state-
ments are absent in the trace file and as a consequence also in the TKPROF formatted report.
Under these circumstances, do not use TKPROF with the option
EXPLAIN
or execute
EXPLAIN
PLAN
manually, since this runs the risk of producing an execution plan that differs from the plan
used by the application. Instead, use Statspack or AWR (see Chapter 26) to retrieve the execu-
tion plan, which was actually used, from the repository of the respective tool. The process is
somewhat easier with Statspack since the hash value emitted to the trace file can be used to get
the desired information. In Oracle9
i
, there is a single hash value in
V$SQL
and it is this hash
value that is also emitted to trace files. In Oracle10
g
, matters are more complicated, since there
are now two hash values:
V$SQL.OLD_HASH_VALUE
as well as
V$SQL.HASH_VALUE
. Merely the latter
is written to SQL trace files. Oracle10
g
Statspack uses the old hash value, which is calculated
with the algorithm used by Oracle9
i
, for its SQL report. The translation from the
HASH_VALUE
found in the trace file to the
OLD_HASH_VALUE
needed to run the Statspack SQL report may be
accomplished with the following query (script
sp_translate_hv.sql
):
