Q: what is STATSPACK ?
A: STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK can be considered BSTAT/ESTAT's successor, incorporating many new features. STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem.

Remember to set timed_statistics to true for your instance. Setting this parameter provides timing data which is invaluable for performance tuning.

1.Installing and Configuring STATSPACK
Create PERFSTAT Tablespace
Run the Create Scripts
Adjusting the STATSPACK Collection Level-snap_level

Create, View and Delete Snapshots
 sqlplus perfstat/perfstat
 SQL> exec statspack.snap;
 SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
      "Date/Time" from stats$snapshot,v$database;
 
 NAME         SNAP_ID Date/Time
 --------- ---------- -------------------
 AKI1               4 14.11.2004:10:56:01
 AKI1               1 13.11.2004:08:48:47
 AKI1               2 13.11.2004:09:00:01
 AKI1               3 13.11.2004:09:01:48
 
 SQL> @?/rdbms/admin/sppurge;
 Enter the Lower and Upper Snapshot ID

Create the Report

 sqlplus perfstat/perfstat
 SQL> @?/rdbms/admin/spreport.sql