Oracle’s STATSPACK performance diagnostic tool allows you to
analyze the performance of your Oracle RDBMS. This Analysis can be used to adjust parameters, determine high wait time events, and determine the SQL which causes the highest load on your system.
1. Logon to your Oracle database as sysdba.
mylunix:mydb:/mydb:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 2 07:38:57 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. The STATSPACK package is used to take snapshots, by calling the procedure snap. Take your first snapshot at the beginning of your peak usage and at evenly spaced intervals in between. IE: If your peak usage is between 8AM and 4PM, start taking your snapshots at 8AM and every 2 hours ( 10AM, 12PM, 2PM, and 4PM). This will allow you to take reports during the entire span of your peak usage and at intervals in between.
SQL> exec statspack.snap
PL/SQL procedure successfully completed.
SQL>
3. Once you are ready to take a report from you snapshots, you will have to find the snapshot ids which identify a specific snapshot range. This can be done by querying the table PERFSTAT.STATS$SNAPSHOT.
SQL> select snap_id, to_char(snap_time, ‘DD-MON-YY HH:MI:SS’) from
perfstat.stats$snapshot
where snap_time between to_date(’02-AUG-09 07:00:00′,’DD-MON-YY HH24:MI:SS’)
and to_date(’02-AUG-09 17:00:00′,’DD-MON-YY HH24:MI:SS’); 2 3
SNAP_ID TO_CHAR(SNAP_TIME,
———- ——————
1 02-AUG-09 08:22:29
11 02-AUG-09 10:00:07
21 02-AUG-09 12:00:03
31 02-AUG-09 14:00:12
41 02-AUG-09 15:00:48
42 02-AUG-09 14:00:47
6 rows selected.
SQL>
4. Once you have your high and low snap ids, call the Oracle script
$ORACLE_HOME/rdbms/admin/spreport.sql to generate an external trace file of your database performance. This script requires three input variables:
beginning snap_id, ending snap_id, and OS file name.
SQL> @?/rdbms/admin/spreport.sql
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
———— ———— ——— —————– —– ——————–
mydb mydb 1 02 Aug 2009 08:22 5
11 02 Aug 2009 10:00 5
21 02 Aug 2009 12:00 5
31 02 Aug 2009 14:00 5
41 02 Aug 2009 15:00 5
42 02 Aug 2009 16:00 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 42
5. Your report will be generated in the current working directory.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Dart:mylinux:/mydb>ls -lrt
-rw-r–r– 1 oracle dba 197698 Aug 2 16:38 sp_1_42.lst
This completes execution of STATSPACK SNAPSHOTS and generation of
STATSPACK reports.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com