Oracle's Statspack Report Generation

Oracle STATSPACK installation steps
Step 1: Create the perfstat Tablespace

The STATSPACK utility requires an isolated tablespace to contain all of the objects and data. For uniformity, it is suggested that the tablespace be called perfstat, the same name as the schema owner for the STATSPACK tables. Note that I have deliberately not used the AUTOEXTEND option. It is important for the Oracle DBA to closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space. We will talk about adjusting the STATSPACK thresholds later in this chapter.

Next, we create a tablespace called perfstat with at least 180 megabytes of space in the datafile:

>sqlplus /

SQL*Plus: Release 8.1.6.0.0 - Production on Tue Dec 12 14:08:11 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.1.0 - 64bit Production

SQL> create tablespace perfstat
2 datafile '/u03/oradata/prodb1/perfstat.dbf'
size 500m;

Step 2: Run the create Scripts

Now that the tablespace exists, we can begin the installation process of the STATSPACK software.

Because of the version differences, we will break this section into one for pre-8.1.7 installation and another for post-8.1.7 installs.

Run the pre-8.1.7 install scripts

The statscre.sql script creates a user called PERFSTAT, executes the script to create all of the STATSPACK tables, and installs the STATSPACK PL/SQL package. When you run this script, you will be prompted for the following information:

 Specify PERFSTAT user's default tablespace: perfstat

 Specify PERFSTAT user's temporary tablespace: temp

 Enter tablespace where STATSPACK objects will be created: perfstat

Install Prerequisites

Note that you must have performed the following before attempting to install STATSPACK:

1. Run catdbsyn.sql when connected as SYS.

2. Run dbmspool.sql when connected as SYS.

3. Allocate a tablespace called perfstat with at least 180 megabytes of storage.

NOTE: The STATSPACK scripts are designed to stop whenever an error is encountered. The statsctab.sql script contains the SQL*Plus directive whenever sqlerror exit;. This means that the script will cease execution if any error is encountered. If you encounter an error and you need to restart the script, just comment out the whenever sqlerror exit line and run the script again. Also, note that the STATSPACK install script contains SQL*Plus commands. Hence, be sure you run it from SQL*Plus and do not try to run it in SVRMGRL or SQL*Worksheet.

Once you have completed running the spcreate.sql script, you will need to ensure that you do not have errors. The STATSPACK utility creates a series of files with the .lis extension as shown here:

prodb2-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al *.lis
-rw-r--r-- 1 oracle oinstall 4170 Dec 12 14:28 spctab.lis
-rw-r--r-- 1 oracle oinstall 3417 Dec 12 14:27 spcusr.lis
-rw-r--r-- 1 oracle oinstall 201 Dec 12 14:28 spcpkg.lis

To check for errors, you need to look for any lines that contain “ORA-” or the word “error”, since the presence of these strings indicates an error. If you are using Windows NT, you can check for errors by searching the output file in MS Word. However, most Oracle administrators on NT get a freeware grep for DOS, which is readily available on the Internet.

The code here shows the UNIX grep commands that are used to check for creation errors.

mysid-/u01/app/oracle/product/9.0.2/rdbms/admin> grep ORA- *.lis

mysid-/u01/app/oracle/product/9.0.2/rdbms/admin> grep -i error *.lis

spctab.lis:SPCTAB complete. Please check spctab.lis for any errors.
spcusr.lis:STATSCUSR complete. Please check spcusr.lis for any errors.
spcpkg.lis:No errors.

Now that we have installed the user, tables, indexes, and the package, we are ready to start collecting STATSPACK data. We will begin by testing the STATSPACK functionality and then schedule a regular STATSPACK collection job.

Step 3: Test the STATSPACK Install

To ensure that everything is installed correctly, we can demand two snapshots and then request an elapsed-time report. To execute a STATSPACK snapshot, we enter the statspack.snap procedure. If we do this twice, we will have two snapshots, and we can run the statsrep.sql report to ensure that everything is working properly. Here is the test to ensure that the install works properly. If you get a meaningful report after entering statsrep, then the install was successful. Also, note that the statsrep.sql script has an EXIT statement, so it will return you to the UNIX prompt when it has completed:

SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> @spreport
. . .

Step 4: Schedule Automatic STATSPACK Data Collections

Now that we have verified that STATSPACK is installed and working, we can schedule automatic data collection. By using the statsauto.sql script we can automatically schedule an hourly data collection for STATSPACK. The statsauto.sql script contains the following directive:

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

The important thing to note in this call to dbms_job.submit is the execution interval. The SYSDATE+1/24 is the interval that is stored in the dba_jobs view to produce hourly snapshots. You can change this as follows for different sample times. There are 1,440 minutes in a day, and you can use this figure to adjust the execution times.

Table 1 gives you the divisors for the snapshot intervals.

Minutes per Day	Minutes between Snapshots	Required Divisor
1,440	60	24
1,440	30	48
1,440	10	144
1,440	5	288

Table 1: Determining the Snapshot Interval

Hence, if we want a snapshot every ten minutes we would issue the following command:

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);

In the real world, you may have times where you want to sample the database over short time intervals. For example, if you have noticed that a performance problem happens every day between 4:00 p.m. and 5:00 p.m., you can request more frequent snapshots during this period.

For normal use, you probably want to accept the hourly default and execute a snapshot every hour. Below is the standard output from running the statsauto.sql script:

SQL> connect perfstat/perfstat;
Connected.
SQL> @statsauto
PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

JOBNO
----------
1


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME TYPE VALUE
------------------------------------ ------- -----------------------------
job_queue_processes integer 1

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
---------- --------- --------
1 12-MAY-02 16:00:00

We can now see that a STATSPACK snapshot will automatically be executed every hour. We see that this is scheduled as job number 1, and we can use this job number to cancel this collection at any time using the dbms_job.remove procedure:

SQL> execute dbms_job.remove(1);

PL/SQL procedure successfully completed.

Installation Files Delivered with STATSPACK

Files Delivered with STATSPACK

In Oracle10g, STATSPACK utility consists of 20 SQL scripts located in the $ORACLE_HOME/rdbms/admin directory where the important database scripts are usually located. The following files form STATSPACK as distributed in Oracle10g:

 The spcreate.sql script is a main script for STATSPACK utility installation and should be run by user sys. This script calls some of the other creation scripts described below.

 The spcusr.sql script creates the perfstat schema. perfstat is the owner of all STATSPACK database objects that form the STATSPACK repository. This script also grants all the necessary authorities to user perfstat.

 The spctab.sql script creates all the STATSPACK objects under schema perfstat.

 The spcpkg.sql script creates a special statistics package that is necessary for snapshot and report generation.

 The spdrop.sql script uninstalls the STATSPACK utility from the database. This script calls the scripts: spdtab.sql and spdusr.sql

 The spauto.sql script is used to schedule the STATSPACK procedure called statspack.snap that gathers STATSPACK snapshots. In the AWR, this job performs a new background process called the Manageability Monitor (MMON).

 The sppurge.sql script purges old STATSPACK data from the repository. The snapshot range for the data to be cleared must be specified by the user.

 The sprepcon.sql is a new script in STATSPACK that is used to specify selected parameters that are related to report generation invoked by spreport.sql XE "spreport.sql" script.

 The spreport.sql and sprepins.sql are the scripts used for report generation. The spreport.sql script must be called to produce the STATSPACK report for a specified snapshot range.

 The sprepsql.sql and sprsqins.sql XE "sprsqins.sql" scripts are used to generate the STATSPACK report for SQL statements, statistics and plan usage.

 The sptrunc.sql script can be used to clear all the STATSPACK tables, thereby reclaiming space for the database.

 The spuexp.par is the name of the export parameter file which is used to export the whole STATSPACK user.

 The sp*.sql scripts are the upgrade scripts used to convert existing STATSPACK repository information to the latest Oracle10g version.

There are only two AWR installation scripts, which are also located in the $ORACLE_HOME/rdbms/admin directory:

 catawr.sql script creates data dictionary catalog objects for the AWR.

 dbmsawr.sql script creates the dbms_workload_repository package for database administrators.

By design, the AWR is created at the same time the database is created and is included in the data dictionary. This shows that the AWR is in the kernel part of the database that cannot function properly without it, while STATSPACK is a stand-alone utility that can be installed or removed from the database at any time. By default, the STATSPACK utility is not installed in the Oracle database. STATSPACK must be manually loaded into the database to start monitoring performance and gathering statistic history.

In Oracle10g, STATSPACK is shipped without the spdoc.txt file, which was a guide for working with STATSPACK that was included in previous versions. This is another mechanism by which Oracle Corporation urges the use of the AWR for performance tuning purposes rather than STATSPACK.

A look at the commonalities and differences between STATSPACK and AWR repository structures will be helpful at this point.

Overview of STATSPACK Scripts
Overview of the STATSPACK Scripts

The STATSPACK scripts have completely changed. All of the STATSPACK scripts are located in the $ORACLE_HOME/rdbms/admin directory.

Oracle 8.1.7 and Oracle9i Script Name	Pre Oracle 8.1.7 Script Name	Script Function
spdoc.txt	statspack.doc	Installation documentation
spcreate.sql	statscre.sql	Create user, tables & install packages
spreport.sql	statsrep.sql	Standard STATSPACK report
spauto.sql	statsauto.sql	Schedule automatic data collection
spuexp.par	statsuexp.par	Parameter file for full STATSPACK export
sppurge.sql	- new file -	Purge SQL for removing old snapshots
sptrunc.sql	- new file -	Script to truncate all STATSPACK tables
spup816.sql	- new file -	Upgrade script to moving to 8.1.6
spup817.sql	- new file -	Upgrade script to moving to 8.1.7
spdrop.sql	statsdrp.sql	Script to drop all STATSPACK tables
spcpkg.sql	statspack.sql	Script to create statspack package
spctab.sql	statsctab.sql	Creates STATSPACK tables
spcusr.sql	statscusr.sql	Creates STATSPACK user & assigns grants
spdtab.sql	statsdtab.sql	Drops all STATSPACK tables
spdusr.sql	statsdusr.sql	Drops the statspack user

Next, let’s take a closer look at these scripts and see details on how to install STATSPACK. Because of the differences between versions, we will have two sections: one for pre-8.1.7 and another for Oracle 8.1.7 and Oracle9i STATSPACK.

STATSPACK scripts for Oracle8 and Oracle8i

You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with “stat”:

>cd $ORACLE_HOME/rdbms/admin
server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al stat*
-rw-r--r-- 1 oracle oinstall 1739 Dec 6 1999 statsauto.sql
-rw-r--r-- 1 oracle oinstall 843 Dec 6 1999 statscre.sql
-rw-r--r-- 1 oracle oinstall 27183 Nov 10 1999 statsctab.sql
-rw-r--r-- 1 oracle oinstall 4686 Nov 10 1999 statscusr.sql
-rw-r--r-- 1 oracle oinstall 792 Aug 27 1999 statsdrp.sql
-rw-r--r-- 1 oracle oinstall 3236 Nov 10 1999 statsdtab.sql
-rw-r--r-- 1 oracle oinstall 1081 Nov 10 1999 statsdusr.sql
-rw-r--r-- 1 oracle oinstall 26667 Dec 6 1999 statspack.doc
-rw-r--r-- 1 oracle oinstall 49821 Nov 10 1999 statspack.sql
-rw-r--r-- 1 oracle oinstall 46873 Nov 10 1999 statsrep.sql
-rw-r--r-- 1 oracle oinstall 559 Aug 27 1999 statsuexp.par

Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files in a hierarchy:

 statscre.sql This is the first install script run after you create the tablespace. It calls several subscripts:

statscusr.sql This script creates a user called PERFSTAT with the required permissions.

statsctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

statspack.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

 statsauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.

 statsdrp.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:

 statsdtab.sql This drops all STATSPACK tables and indexes.

 statsdusr.sql This script drops the PERFSTAT user.

 statsuexp.par This is an export parameter file for exporting the STATSPACK objects. This can be useful if you want to consolidate STATSPACK reports for several databases into a single STATSPACK structure.

 statspack.doc This is a generic read-me file explaining the installation and operation of the STATSPACK utility.

 statsrep.sql This is the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.

 statsrep80.sql This is a version of the STATSPACK report for Oracle 8.0.

Now that we understand the functions of each of the files, we are ready to install STATSPACK. Our first step is to review the installation files for the STATSPACK install.

STATSPACK scripts for post 8.1.6 STATSPACK

You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with “sp”:

>cd $ORACLE_HOME/rdbms/admin
server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al sp*

-rw-r--r-- 1 oracle oinstall 1771 May 10 2001 spauto.sql
-rw-r--r-- 1 oracle oinstall 82227 May 10 2001 spcpkg.sql
-rw-r--r-- 1 oracle oinstall 877 May 10 2001 spcreate.sql
-rw-r--r-- 1 oracle oinstall 42294 May 10 2001 spctab.sql
-rw-r--r-- 1 oracle oinstall 7949 May 10 2001 spcusr.sql
-rw-r--r-- 1 oracle oinstall 69074 May 10 2001 spdoc.txt
-rw-r--r-- 1 oracle oinstall 758 May 10 2001 spdrop.sql
-rw-r--r-- 1 oracle oinstall 4342 May 10 2001 spdtab.sql
-rw-r--r-- 1 oracle oinstall 1363 May 10 2001 spdusr.sql
-rw-r--r-- 1 oracle oinstall 7760 May 10 2001 sppurge.sql
-rw-r--r-- 1 oracle oinstall 113753 May 10 2001 sprepins.sql
-rw-r--r-- 1 oracle oinstall 1284 May 10 2001 spreport.sql
-rw-r--r-- 1 oracle oinstall 26556 May 10 2001 sprepsql.sql
-rw-r--r-- 1 oracle oinstall 2726 May 10 2001 sptrunc.sql
-rw-r--r-- 1 oracle oinstall 588 May 10 2001 spuexp.par
-rw-r--r-- 1 oracle oinstall 30462 May 10 2001 spup816.sql
-rw-r--r-- 1 oracle oinstall 23309 May 10 2001 spup817.sql

Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:

 spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:

 spcsr.sql This script creates a user called PERFSTAT with the required permissions.

 spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.

 spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.

 spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.

 spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:

 spdtab.sql This drops all STATSPACK tables and indexes.

 spdusr.sql This script drops the PERFSTAT user.

 spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.

 spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.

 sprepins.sql This is the actual SQL that produces the STATSPACK report.

 sppurge.sql This is a script to delete older unwanted snapshots.

 spuexp.par This is a export parameter file to export all of the STATSPACK data.

 sptrunc.sql This is a script to truncate all STATSPACK tables.

 spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.

 spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6.