Oracle Statistics Package (STATSPACK): A Free Performance Analysis Tool

Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data.  A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package.  This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables.

For all 10g versions Statspack is available but Beginning Oracle 10g, Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) were offered as a replacement for Statspack. It is Oracle’s recommendation that customers utilize the AWR and ADDM instead of Statspack as a diagnostic tool for database performance tuning issues. However, you can use AWR and ADDM only with Enterprise Edition and also should have licensed the Diagnostic Pack, an extra cost option.

I prefer to use Statspack, which is free and powerful. I mine the Statspack data for trend analysis to know the good and bad times; Oracle has made good enhancements to Statspack after 9i. To understand it, you will have to refer to Oracle Database 9i documentation as it is no longer documented in the manuals starting 10g. Also, StatsPack README files include more specific and up to date information and history about this tool as well as platform and release specific information that helps on installing and using the product. These files are located at:

Oracle8i 8.1.6:  $ORACLE_HOME/rdbms/admin/statspack.doc
Oracle8i 8.1.7, Oracle9i 9.x, and Oracle10g 10.x: $ORACLE_HOME/rdbms/admin/spdoc.txt

Table of Contents from Statistics Package (STATSPACK) README (spdoc.txt)

0.  Introduction and Terminology
1.  Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2.  Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3.  Gathering data – taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4.  Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5.  Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels – details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6.  Time Units used for Performance Statistics
7.  Event Timings
8.  Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9.  New and Changed Features
9.1. Changes between 10.1  and 10.2
9.2. Changes between 9.2   and 10.1
9.3. Changes between 9.0   and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1  to 10.2
10.2.2. Upgrading the Statspack schema from 9.2   to 10.1
10.2.3. Upgrading the Statspack schema from 9.0   to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications

Leave a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.