My Oracle Support Banner

Statistics Package (STATSPACK) Guide (Doc ID 394937.1)

Last updated on MARCH 12, 2021

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Standard Edition - Version 8.1.7.0 and later
Oracle Communications Network Charging and Control - Version 5.0.0 to 5.0.0 [Release 5.0]
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

 

RDBMS version 10g offers a new and improved tool for diagnosing Database Perfromance issues. It is the Automated WorkLoad Repository (AWR).
However, there are still a number of customers using statistics package (statspack) intially introduced in RDBMS version 8.1.
The goal of this document is to further assist customers/engineers when installing and using the database performance tool Statspack.

During install of the RDBMS product, Oracle stores a document entitled spdoc.txt.
The spdoc.txt file will be located in the following directory upon successful install of the RDBMS product 8.1.7 or higher: $ORACLE_HOME/rdbms/admin/.
The StatsPack README files (spdoc.txt) include specific updated information, and history on this tool as well as platform and release specific information that will help when installing and using this product.

A number of cutomers do not realize spdoc.txt is available on their systems, or would like to have it available through Oracle's Knowledge Repository for easy access.
Therefore, the latest version, 10.2, spdoc.txt is published in this note.
Please find below spdoc.txt for version 10.2 in it's entirety to help guide you through installation, and the most common issues you may encounter while running statspack.

Information in this document will help you with all versions of RDBMS statspack product. However, Oracle still suggests you go to your $ORACLE_HOME/rdbms/admin/spdoc.txt to reference your statspack platform and version specific information on running statspack reports (i.e section 4 below).

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 Introduction and Terminology
 How does Statspack work?
 Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
 Enterprise Manager
 Automatic Workload Repository and Statspack
 Statspack Configuration
 Database Space Requirements
 Installing the Tool
 Errors during installation
 Gathering data - taking a snapshot
 Automating Statspack statistics gathering
 Using DBMS_JOB
 Running the Performance reports
 Configuring the amount of data captured
 Snapshot Level
 Snapshot SQL thresholds
 Changing the default values for Snapshot Level and SQL Thresholds
 Snapshot Levels - details
 Specifying a Session Id
 Input Parameters for the SNAP and MODIFY_STATSPACK_PARAMETERS procedures
 Time Units used for Performance Statistics
 Event Timings
 Managing and Sharing performance data
 Baselining performance data
 Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE procedure and function which accept Begin and End Snap Ids
 Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE procedure and function which accept Begin and End Dates
 Purging/removing unnecessary data
 Input Parameters for the PURGE procedure and function which accept Begin Snap Id and End Snap Id
 Input Parameters for the PURGE procedures and functions which accept Begin Date and End Date
 Input Parameters for the PURGE procedure and function which accept a single Purge Before Date
 Input Parameters for the PURGE procedure and function which accept the Number of Days of data to keep
 Using sppurge.sql
 Removing all data
 Sharing data via export
 New and Changed Features
 Changes between 11.1  and 12.1
 Changes between 10.2  and 11.1
 Changes between 10.1  and 10.2
 Changes between 9.2 and 10.1
 Changes below 9.2
 Compatibility and Upgrading from previous releases
 Compatibility
 Upgrading an existing Statspack schema to a newer release
 Upgrading the Statspack schema from 12.1 to 12.2
 Upgrading the Statspack schema from 11.2 to 12.2
 Upgrading the Statspack schema from 11.2 to 12.1
 Upgrading the Statspack schema from 10.2   to 11.1
 Upgrading the Statspack schema from 10.1 to 10.2
 Upgrading the Statspack schema from 9.2 to 10.1
 Upgrading the Statspack schema to versions below 10.1
 Oracle Real Application Clusters specific considerations
 Changing Instance Numbers
 Real Application Clusters Specific Reports
 Real Application Clusters Specific Data
 Conflicts and differences compared to UTLBSTAT/UTLESTAT
 Removing the package
 Supplied Scripts Overview
 Limitations and Modifications
 Limitations
 Modifications
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.