Oracle/Database/Oracle Statspack

Oracle Statspack
Statspack - Oracle FAQ

Statspack is a set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Note: Although AWR and ADDM (introduced in Oracle/Database/Oracle 10g) provide better statistics than STATSPACK, users that are not licensed to use the Enterprise Manager Diagnostic Pack should continue to use statspack.

Install statspack
cd $ORACLE_HOME/rdbms/admin sqlplus "/ as sysdba" @spdrop.sql      -- Drop and install statspack sqlplus "/ as sysdba" @spcreate.sql    -- Enter tablespace names when prompted

References:
 * Oracle STATSPACK installation steps

Take performance snapshots of the database
sqlplus perfstat/perfstat exec statspack.snap;                   -- Take a performance snapshots -- or : exec perfstat.statspack.snap(i_snap_level=>10); -- or instruct statspack to do gather more details in the snapshot -- (look up which oracle version supports which level).

The spauto.sql script can be customized and executed to schedule the collection of STATPACK snapshots.

Statspack reporting
-- Get a list of snapshots select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT; @spreport.sql                          -- Enter two snapshot id's for difference report

Other statspack scripts
Some of the other statspack scripts are:
 * sppurge.sql - Purge (delete) a range of Snapshot Id's between the specified begin and end Snap Id's
 * spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics
 * spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
 * spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
 * spreport.sql - Report on differences between values recorded in two snapshots
 * sptrunc.sql - Truncates all data in Statspack tables

Potential problems
Statpack reporting suffers from the following problems:


 * Some statistics may only be reported on COMPLETION of a query. For example, if a query runs for 12 hours, its processing won't be reported during any of the snapshots taken while the query was busy executing.


 * If queries are aged out of the shared pool, the stats from V$SQL are reset. This can throw off the delta calculations and even make it negative. For example, query A has 10,000 buffer_gets at snapshot 1, but at snapshot #2, it has been aged out of the pool and reloaded and now shows only 1,000 buffer_gets. So, when you run spreport.sql from snapshot 1 to 2, you'll get 1,000-10,000 = -9,000 for this query.

Resources

 * OraPerf.com, upload your STATSPACK file to get performance recommendations.
 * www.spviewer.com, website with software tools for Oracle tuning based on statspack and AWR.
 * statspackanalyzer.com - website that can analyze statspack reports.

keywords
Oracle Statspack