How to use Statspack to Create Report in Oracle

Introduction

The Statspack is a set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. When you run the Statspack installation script, the PERFSTAT user is created automatically.

PERFSTAT owns all objects needed by the Statspack package
Note: All sql scripts are store in $ORACLE_HOME/rdbms/admin

Let's start to create.

Install statspack

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@spcreate

schedule an hourly snapshot

cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
@spauto.sql

To take a snapshot manually

cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
exec statspack.snap;

Or

exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

To generate statspack report

cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
@spreport.sql

To delete snapshots

cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
@sppurge;

To list all generated snapshot

cd $ORACLE_HOME/rdbms/admin
sqlplus perfstat/perfstat
select   
    snap_id,      
    snap_level,   
    to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Snap_Time"
from   
    stats$snapshot,   
    v$database
order by
    snap_id
/

To uninstall statspack

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@spdrop

That's all.hopes help.