Statspack

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.

Although AWR and ADDM (introduced in 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

Take performance snapshots of the database

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

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:

• spauto.sql - Schedules 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 - Deinstalls STATSPACK from database (Run as SYS)
• sppurge.sql - Purges (deletes) a range of Snapshot Id's between the specified begin and end Snap Id's
• spreport.sql - Reports differences between values recorded in two snapshots
• sprepsql.sql - Reports execution plans
• 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.

External links

• OraPerf.com, upload your STATSPACK file to get performance recommendations - no more exists.
• www.spviewer.com, website with software tools for Oracle tuning based on statspack and AWR. (70$/db)
• statspackanalyzer - website that can analyze statspack reports - no more exists.
• stori.orapub.com - transform Statspack data into an understandable story; well organized, in paragraph form with charts, specific and ranked recommendations and in plain English. (500$/DBID)