Oracle: 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

o uninstall statspack
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@spdrop

StatsPack Analyser:

http://www.statspackanalyzer.com/analyze090630.asp

References:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm#21793
http://nimishgarg.blogspot.com/2011/02/using-statspack-report.html
http://www.shutdownabort.com/dbaqueries/Performance_Statspack.php
http://snippets.aktagon.com/snippets/26-How-to-generate-an-Oracle-statspack-report-and-an-explain-plan-for-a-query