BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








STATSPACK scripts for post 8.1.6 STATSPACK

Oracle Tips by Burleson Consulting

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

Now, let’s take a closer look at the installation procedures.

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

L 3-2

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

L 3-3

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

L 3-4

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.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter