Database Reference
In-Depth Information
Both AWR and ASH are included in the extra-cost Diagnostics Pack. The downside of ASH
is that it does not provide a resource profile, since it is built with sampling. A session-level
Statspack report does include a rudimentary resource profile, although the report does not use
the term resource profile. Session level data are based on
V$SESSION_EVENT
and
V$SESSTAT
, which
afford the calculation of a resource profile. Interesting sections from an ASH report, which a
session-level Statspack as well as a TKPROF report lack, are “Top Service/Module”, “Top SQL
using literals”, “Top Blocking Sessions”, and “Top Objects/Files/Latches”.
Extended SQL Trace, AWR, and ASH
The script
awr_capture.sql
temporarily sets the hidden parameter
_ASH_SAMPLE_ALL=TRUE
to
cause ASH to sample idle wait events for improved diagnostic expressiveness. Then the script
takes an AWR snapshot and enables level 12 SQL trace for the session that exhibits a perfor-
mance problem. Next, the script asks the user for how long it should trace the session. There
are two ways of using the script:
Tracing the session for a predetermined interval, such as 300 or 600 seconds. This is
achieved by entering the desired length of the interval. The script calls
DBMS_LOCK.SLEEP
to pause for the specified number of seconds, takes another AWR snapshot, and disables
SQL trace.
Using an event-based approach to control the measurement interval. With this approach,
type 0 in response to the question concerning the length of the interval, but do not yet
hit return. Wait until an event, such as an end user calling to report that the session you
are tracing has returned a result set, occurs, then hit return. Due to the zero-length wait
interval, the script immediately takes another AWR snapshot and disables SQL trace.
At the end of the measurement interval, the script automatically creates both an AWR and
an ASH report in HTML format by calling the package
DBMS_WORKLOAD_REPOSITORY
. The files are
created in the current directory. Note that the documented ASH script does not have the capa-
bility to report solely on a specific session. Following is an example of the script in operation:
SQL> @awr_capture
Please enter SID (V$SESSION.SID): 143
Please enter a comment (optional): slow-app
SPID SID SERIAL# USERNAME MACHINE SERVICE_NAME
----- --- ------- -------- ---------- ----------------
18632 143 333 NDEBES WORKGROUP\ TEN.oradbpro.com
DBSERVER
PROGRAM MODULE ACTION CLIENT_IDENTIFIER
-------- -------- ------ -----------------
perl.exe perl.exe NULL NULL
Oracle pid: 20, Unix process pid: 18632, image:
oracleTEN1@dbserver1.oradbpro.com
Statement processed.
Extended SQL trace file:
/opt/oracle/obase/admin/TEN/udump/ten1_ora_18632.trc
