Using Statspack Report

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

To Install statspack
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@spcreate


To 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 snapshots
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