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.
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.
-- 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
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.
- 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)