Database Reference
In-Depth Information
CHAPTER 26
■ ■ ■
Integrating Extended SQL
Trace and AWR
T
he Active Workload Repository (AWR) and SQL trace files both capture performance-relevant
data on SQL and PL/SQL execution. It is undocumented that both data sources may be linked
to answer questions that frequently arise during investigations of performance problems. By
integrating SQL trace data with AWR, it is possible to find out whether different execution plans
were used for a particular statement and at what time. Furthermore, contrary to
EXPLAIN
PLAN
,
the AWR is a reliable source for execution plans when plans for certain statements are absent
from SQL trace files.
Retrieving Execution Plans
Unlike Statspack release 10.2, which captures the
SQL_ID
as well as the old (
V$SQL.OLD_HASH_VALUE
)
and new hash value (
V$SQL.HASH_VALUE
) for SQL statement texts, AWR captures merely the
SQL_ID
from
V$SQL
. In Oracle10
g
, this poses a slight problem for the retrieval of past execution
plans and statistics for statements captured by SQL trace files. The reason is that Oracle10
g
trace files do not include the
SQL_ID
from
V$SQL
. Oracle11
g
SQL trace files include the new
parameter
sqlid
(see “PARSING IN CURSOR Entry Format” in Chapter 24), which corresponds
to the
SQL_ID
from
V$SQL
. Thus, the issue of mapping a SQL statement text to the
SQL_ID
has
been resolved in Oracle11
g
.
Note that execution plans are only emitted to SQL trace files when cursors are closed, such
that it is possible to encounter trace files that do not contain execution plans for certain state-
ments. If such statements have been aged out of the shared pool by the time the absence of an
execution plan becomes evident, then AWR or Statspack (see “Execution Plans for Statements
Captured with SQL Trace” in Chapter 25) are the only options for retrieving the plan. Occasion-
ally the optimizer chooses different plans for the same statement over time. One execution
plan might result in a very poor response time while another may cause an appropriate response
time. The procedure presented next shows how to retrieve all plans for a SQL statement captured
by SQL trace and AWR. The five-way join of tables in the sample schema HR depicted in
“Retrieving the Text of Captured SQL Statements” in Chapter 25 is used as an example. In releases
prior to Oracle11
g
, we need to start by determining the
SQL_ID
for a statement, since those
releases do not emit it to the SQL trace file. Since AWR captures the SQL statement text as a
character large object (
CLOB
) from
V$SQL.SQL_FULLTEXT
, this is accomplished by searching for
the statement text or portions of it with
DBMS_LOB
. Statements captured by AWR are stored in
345
