Simon Krenger

Since most of our databases are not licensed with the Oracle Enterprise Manager Diagnostic Pack, we cannot use AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor). So we have to use the good old Oracle STATSPACK.

The goal of this article is to provide a quick reference for installing and maintaining STATSPACK for an Oracle database. It is based on the excellent “STATSPACK Survival Guide” of Akadia AG. If you need more information on STATSPACK, please refer to the Survival Guide or the Oracle documentation, since this article is only a really quick summary on how to install, configure and use STATSPACK.

Installing STATSPACK

It is heavily recommended that you create a new tablespace for STATSPACK itself:

SQL> show user
USER is "SYS"
SQL> CREATE TABLESPACE perfstat DATAFILE '/u02/oradata/mydb/perfstat01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Then, you can perform the actual installation:

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

Then, enter a password for the new perfstat user, set perfstat as the default tablespace and choose an appropriate temporary tablespace. After the installation, check spcpkg.lis for any errors.

Create a job

Currently, STATSPACK will only take a snapshot if you do it manually (using “exec statspack.snap“). To automate the process, use the SQL script provided by Oracle to create a database job. That job will take a snapshot in a specified interval (default is 1 hour).

To do this, execute the following command as user PERFSTAT:

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

This will create a new job in the database that will take a snapshot every hour. Another possibility is to use the DBMS_SCHEDULER package, which is the recommended way to set up jobs in the database in 11g. You can find an article on how to do it here.

Create a report

To create a report, execute the following command as user perfstat:

SQL>  @?/rdbms/admin/spreport