Database Reference
In-Depth Information
The hash value calculated on the optimizer environment and stored in the AWR repository
may be used as evidence that a plan may have changed due to different parameter settings at
instance or session level. Since updated optimizer statistics may cause plans to change, I
recommend saving statistics in a statistics table using the packaged procedure
DBMS_STATS.
EXPORT_SCHEMA_STATS
before overwriting them. The package
DBMS_STATS
includes the procedure
CREATE_STAT_TABLE
for creating statistics tables. In Oracle10
g
, automatic statistics recalculation
for a schema may be prevented with
DBMS_STATS.LOCK_SCHEMA_STATS
.
Source Code Depot
Table 26-1 lists this chapter's source files and their functionality.
Table 26-1.
Extended SQL Trace and AWR Source Code Depot
File Name
Functionality
awr_sqltext.sql
Retrieves the
SQL_ID
and full statement text from
DBA_HIST_SQLTEXT
based on a text subset of a SQL statement. The
SQL_ID
may be passed
to the AWR script
awrsqrpt.sql
as input.
awr_sqlstat.sql
Retrieves AWR snapshots that captured a SQL statement with a certain
SQL_ID
. Execution statistics, such as elapsed time and CPU usage, are
displayed along with hash values for the execution plan and the optimizer
environment.
