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.

References

Source: Statspack - Oracle FAQ

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