Home
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
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Overview of STATSPACK Scripts

Don Burleson

Click here for STATSPACK Install directions

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.

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

��