Oracle STATSPACK is a tool that allows you to analyze the performance of your Oracle RDBMS over a specific period of time. Normally the analysis of how your database is performing should be based over a period of the highest usage, but over a length of time that would avoid short term peaks to skew your results. STATSPACK has been in use since Oracle 8i with continual enhancements with every new version release. In this article, we will show how to install and prepare your database for the use of STATSPACK.

Also see:
Taking STATSPACK snap shots and producing reports.
Setup of STATSPACK scheduled jobs.

1. Logon to Oracle as sys dba

MyLinux:mydb:/mydb:>sqlplus ‘/ as sysdba’

SQL>

2. The STATSPACK tool is owned by the user PERFSTAT and specific storage should be created to avoid interference with your database. In this step will create specific storage for PERFSTAT user.

SQL> create tablespace perfstat datafile ‘/mydb/ordata/perfstat01.dbf’ size 2096m;

Tablespace altered.

3. The user PERFSTAT and all components of the STATSPACK tool are created with the ORACLE provided script SPCREATE.SQL. Execute the script $ORACLE_HOME/rdbms/admin/spcreate.sql to create the perfstat user and data structures.

SQL> @?/rdbms/admin/spcreate.sql

4. The SPCREATE.SQL script accept three parameters before execution: Password; Default Tablespace; and Temp Tablespace. Enter the following when prompted:
PERFSTAT Password
Default Tablespace
Temp Tablespace

5. After execution has occurred, check the following list files to determine that no errors occurred.

spcusr.lis – Creates the perfstat user account.
spctab.lis – Creates all data structure under the perfstat user account.
spcpkg.lis – Creates the perfstat packages.

6. This completes the build of Oracle’s STATSPACK diagnostic tool in your database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com