Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle STATSPACK installation steps

Don Burleson


Click here for STATSPACK Script Descriptions

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 - Production on Tue Dec 12 14:08:11 2000

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

Connected to:
Oracle8i Enterprise Edition Release - 64bit Production
With the Partitioning option
JServer Release - 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:


>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













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;
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:


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:

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




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.