Oracle Statspack usage

Statspack is tool for performance monitoring and reporting.
New versions of Oracle (10 and 11) provides AWR (Automatic Workload Repository) reports with more detailed statistics than statspack.
But sometimes you might still need statspack for example with Oracle SE version databases.

Here is how you can get statspack report out of your database (First create tablespace for statspack before start install):

1. Install statspack (this creates PERFSTAT schema and this asks you to give tablespace name for statspack)(Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spcreate.sql   


2. Take snapshots for statspack report (you need at least 2 snapshots to generate report):
sqlplus perfstat/<password>
exec statspack.snap;   

You can also give detail levels for snapshots (Default level is 5). Levels vary between Oracle versions.
Oracle 11.2 gives following levels:
SQL> select * from stats$level_description;

SNAP_LEVEL
----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
         0
This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information

         5
This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels

         6
This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels

         7
This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels

        10
This level includes capturing Child Latch statistics, along with all data captured by lower levels
----------------------------------

Take snapshot with certain level:
sqlplus perfstat/<password>
exec statspack.snap(i_snap_level=>10);

It is also possible to collect snapshots automatically via dbms_jobs (spauto.sql script) or with your own cron script.


3. Generate statspack report (This list available snapshots and asks you to give begin and end snapshot for report):
sqlplus perfstat/<password>
@?/rdbms/admin/spreport

You can also check available snapshots from here:
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

If you need a help for statspack report analyzing check these:
statspackanalyzer.com
 http://filebank.orapub.com/cgi-bin/quickUOWTBA.cgi


4. Purge statspack snapshots (You can purge old or all statspack snapshots from database):
Purge 10 days older snapshots:
sqlplus perfstat/<password>
exec statspack.purge(sysdate-10);



5. If you want to remove statspack schema and data from your database you can do it this way (Run as oracle user (and SYS)):
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql