25^th SEP 2014

STATSPACK ADMINISTRATION

         Detail about statspack can be read from

             $ORACLE_HOME/rdbms/admin/spdoc.txt

1. Taking performance snapshot

             SQLPLUS PERFSTAT/PERFSTAT
             EXEC STATSPACK.SNAP;
             EXEC PERFSTAT.STATSPACK.SNAP(I_SNAP_LEVEL=>10);

2. Get list of snapshots

            sql > select snap_id,snap_time from stats$snapshot;

    3. Generate statspack report

sql> @$ORACLE_HOME/rdbms/admin/spreport.sql
– Provide info as require

   4. Additional statspack scripts

a) sppurge.sql – Purge(delete) a range of snapshot id's between the given snap id's
b) spauto.sql – Schedule a dbms_job to automate the collection of statspack statistics
c) spdrop.sql – Deinstall statspack from database (Run as SYS)
d) sptrunc.sql – Truncate all data in statspack tables

5. Statspack Level
   
   SQL> SELECT * FROM stats$level_description ORDER BY snap_level;
   Level 0 captures general statistics, rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information.
   Level 5 captures high resource usage SQL Statements, along with all data captured by lower levels.
   Level 6 captures SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels.
   Level 7 captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels.
   Level 10 captures Child Latch statistics, along with all data captured by lower levels.