Installing Oracle STATSPACK

• Creating the perfstat tablespace

• Creating the perfstat user

• Creating the tables

• Installing the packages

• Collecting data

• Scheduling data collection snapshots with dbms_job

• Testing your installation

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.