The MERITS Performance Optimization Method - Secrets of the Oracle Database - page 372

Begin snapshot: 95
Please enter snapshot interval in seconds (0 to take end snapshot immediately): 300
End snapshot: 96
Begin time: 07.Sep.2007 03:51:33; End time: 07.Sep.2007 03:56:39; Duration (minutes)
: 5.1
ASH Report file: slow-app-SID-143-SERIAL-333-ash.html
AWR Report file: slow-app-SID-143-SERIAL-333-awr.html

Extended SQL Trace and Session Level Statspack Snapshot
The measurement script for Statspack is very similar to the AWR variant. It also asks for a session to
trace and enables level 12 SQL trace for that session. Instead of taking AWR snapshots, this
script takes Statspack snapshots, which include session level data. The script uses ORADEBUG to
retrieve the extended SQL trace file name. The following example illustrates the event-based
approach to determining the capture interval. This means that the capture does not last for a
predetermined interval.

SQL> @sp_capture
Please enter SID (V$SESSION.SID): 139
SPID SID SERIAL# USERNAME MACHINE SERVICE_NAME
------------ ----- ------- ---------- ---------- ----------------
19376 139 41757 NDEBES WORKGROUP\ TEN.oradbpro.com
DBSERVER
PROGRAM MODULE ACTION CLIENT_IDENTIFIER
----------- --------------- ---------- -----------------
perl.exe insert_perf5.pl NULL NULL
Oracle pid: 16, Unix process pid: 19376, image: [e-mail]
Extended SQL trace file:
/opt/oracle/obase/admin/TEN/udump/ten1_ora_19376.trc
Begin snapshot: 291

At this point, the script waits for input. If you wish to end the capture interval as soon as an
event occurs, wait for the event and enter zero.

Please enter snapshot interval in seconds (0 to take end snapshot immediately): 0
End snapshot: 301
Begin time: 07.Sep.2007 06:20:07; End time: 07.Sep.2007 06:24:44; Duration (minutes)
: 4.6

As soon as the capture is over, a Statspack report for the beginning and end snapshot
numbers may be generated by running the script $ORACLE_HOME/rdbms/admin/spreport.sql .
Following is an excerpt of a report that includes session level data captured with the script
sp_capture.sql . The session-specific sections are “Session Wait Events”, “Session Time Model
Stats” (Oracle10 g and later releases only), and “Session Statistics”: 1

1. Per transaction statistics have been omitted in the “Session Statistics” section.