1. What is a statspack?
Statspack is the abbreviation for statistics package. The statspack consists of a collection of SQL, PL/SQL and SQL*Plus scripts. The statspack helps you to collect, store, compare, format and analyze performance data for an Oracle instance.
With the statspack, you can, for example, determine the cause of performance problems or determine and quantify the effects of profile parameter changes. Refer to the section about typical usage scenarios for more information.
2. Where are the statspack scripts located?
The statspack scripts are installed automatically in the $ORACLE_HOME/rdbms/admin directory (Unix) or the %ORACLE_HOME%rdbmsadmin directory (Windows) when you install the Oracle9i database software.
The most important scripts are:
- spcreate.sql: Installs the statspack;
- spdrop.sql : Uninstalls the statspack;
- spauto.sql : Automates the collection of statistics;
- spreport.sql: Creates a performance report;
- spdoc.txt : Provides documentation for the statspack;
spcreate.sql and spdrop.sql must be executed with the SYSDBA privilege.
spauto.sql and spreport.sql should be executed under the PERFSTAT user.
3. Where can I find more information about the statspack?
- Oracle9i Database Performance Tuning Guide and Reference
Chapter 21: Using Statspack
- Statistics Package (Statspack) README: spdoc.txt
This readme is located under:
$ORACLE_HOME/rdbms/adminspdoc.txt (UNIX) or
4. For which Oracle releases is the statspack available?
The statspack is availabe as of Oracle 8i (8.1.6) and was enhanced with Oracle9i. This note only refers to Oracle9i Release 2 (9.2.0).
5. What are the advantages of the statspack compared with UTLBSTAT/UTLESTAT?
The data collected by the statspack is more extensive and is saved in tables in the Oracle database so that you can use it later to diagnose performance problems or to create trend analyses. The overall diagnosis generated by the statspack includes an overview over the instance status (instance health), a load profile, the main SQL statements with the highest resource consumption and information about queues, events and profile parameters.
6. Should I install the statspack in its own tablespace?
Yes, we recommend that you install the statspack in a separate tablespace called PERFSTAT. You should create the tablespace as follows:
SQL>CREATE TABLESPACE "PERFSTAT"
DATAFILE ' <sapdata>/perfstat_1/PERFSTAT.data1'
SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
brspace only supports the creation of data tablespaces for SAP data, but not for non-SAP data. Therefore, you should use sqlplus, not brspace, to create the PERFSTAT tablespace.
7. What user do I use to execute the statspack?
When you install the statspack, the PERFSTAT statspack database user is created automatically with the minimum access authorizations for the Oracle dictionary (V$-Views). The statspack and all required tables, indexes, and so on belong to the PERFSTAT user. You collect the statistics, write the collected data to the database and evaluate the data during the creation of performance reports under this user.
8. How much memory is required for the statspack?
At the beginning, approximately 100 MB are required for the installation.
The future space requirements of the statspack depend on how often you are generating snapshots, the size of the database and the size of the instance. In addition, they are also influenced by the snapshot level which determines the amount of data that is collected in a snapshot. Therefore, we cannot give you any general recommendations about the size of the statspack. We only recommend that you observe the expansion of the statspack, especially if you are generating snapshots automatically (see below), so that you can make an estimate about future space requirements.
9. How do I install the statspack?
The background installation proceeds as follows:
SQL>connect / as sysdba
The PERFSTAT tablespace must already have been created.
SYSTEM is not allowed as a default tablespace.
SQL>connect / as sysdba
The interactive installation requires that you enter the password, the default tablespace as well as the temporary tablespace of the PERFSTAT database user.
The execution of the spcreate.sql script executes three scripts one after the other (spcusr.sql, spctab.sql, spcpkg.sql). The system creates three log files (spcusr.lis, spctab.lis, spcpkg.lis) in the current directory. You should check these for the occurrence of errors during the installation. After that, you can delete them.
After you install the statspack, we recommend that you change the password for the PERFSTAT user.
SQL>alter user perfstat identified by <my_perfstat_password>
10. How do I uninstall the statspack?
Step 1: Delete the entered statspack jobs -
Log on as the user under which the statspack jobs run (usually PERFSTAT):
SQL>select job, what from user_jobs;
This displays a list of the jobs that are currently entered under the user PERFSTAT.
You can delete these jobs using DBMS_JOB.REMOVE
Execute the following SQL statement for all jobs you want to delete that have the print job number <jobnr>:
SQL>execute dbms_job.remove( <jobnr> );
Step 2: Delete the PERFSTAT objects and the PERFSTAT schema -
SQL>connect / as sysdba
As a result, the tables of the PERFSTAT user and, after that, the user PERFSTAT are deleted.
Step 3: Delete the PERFSTAT tablespace -
Since the PERFSTAT tablespace was created specifically for the PERFSTAT user and the statspack data, it should be empty once you have deleted the PERFSTAT user. You can check this using
SQL>select * from dba_segments where tablespace_name = 'PERFSTAT';
If the tablespace is empty, you can delete it.
11. Will the performance of the SAP system be impeded if I install the statspack or generate snapshots?
Installing the statspack in a separate tablespace using a dedicated database user means that the statspack is kept completely separate from the SAP schema. You can install, configure or uninstall the statspack during SAP operation without affecting the SAP system. The amount of statspack data should generally by small in relation to the overall size of the database. The collection and analysis of statistical data has only a minimal effect on system performance, since only those statistics that are contained in the memory (SGA) are being accessed.
The installation and use of the statspack is therefore largely independent of and transparent for the SAP System.
12. Are there any special issues that I need to be aware of when I install the statspack in the SAP environment?
Apart from the tablespace naming convention during creation with brspace, there are no SAP specifications.
13. Is the statspack integrated in any form in SAP tools?
No, not up to now.
14. Do I have to set any Oracle profile parameters?
Yes, you must set the following parameters to the values specified to ensure that you receive useful statistics:
SQL>show parameter statistics
To automate the generation of statspack snapshots, you must set the parameter job_queue_processes > 0.
15. What is a snapshot?
The use of the statspack to perform a once-off collection of performance data is called a snapshot, that is, a snapshot of the current system status. You can use two snapshots to create a performance report.
16. How do I generate a snapshot?
17. Can I generate snapshots automatically?
Check the files for errors during the installation.
Prerequisite: job_queue_processes > 0
This sets up an automatic job in the database that generates a snapshot every hour.
18. What are snapshot levels?
There are different types of snapshot levels. A snapshot level determines which performance data is collected. The higher the level, the more data is collected. The default value of 5 is generally sufficient. For RAC, you should generate snapshots with at least level 7.
Temporarily changing the snapshot level (for one snapshot only):
Changing the snapshot level and saving it as a new default:
For simultaneous generation of a snapshot:
For details regarding the individual snapshot levels, see spdoc.txt.
19. How do I create a performance report?
The performance report calculates differences in the statistical numbers of two snapshots (final snapshot minus the start snapshot). To ensure that these calculated values can be usefully interpreted, the instance between the two snapshots must not have been stopped. The reason for this is that a snapshot determines its figures from the V$ tables, which are reset to 0 each time an instance starts.
20. Are there any special features in Oracle Real Application Clusters (RAC)?
In general, we recommend that you set the snapshot level to the value 7 in the case of RAC. This means that the most heavily loaded segments are recognized at segment level. These statistics help you to localize hot spots.
To generate a snapshot in an RAC environment, you must log on to the RAC instance for which you want to generate statistics.
To set up automatic generation of snapshots in an RAC environment with the spauto.sql script for each RAC instance, you must run this script once on each instance.
To create an performance report in an RAC environment, you must log on to the RAC instance for which you want to generate a performance report.
21. What are the typical scenarios for using the statspack?
a) To check whether tuning measures have the expected effect
b) To check the effects of changes in the configuration ( profile parameters, tablespace layout).
c) To check whether a higher workload requires an adjustment of profile parameters.
d) To obtain comparison metrics for the performance behavior before a scheduled change in the system (new platform, new storage, software upgrade, new Oracle release).
e) To check system user complaints about poor response times and to determine the causes for the poor performance.
f) To recognize trends, that is, slow changes over a longer period, for example, an increasing workload, an increasing I/O load or an increasing CPU workload
g) To determine load peaks, bottlenecks and their causes.
h) To determine the SQL statements with the highest resource consumption (top SQL).