Performance Tuning : How to configure Statspack Performance Diagnostic Tool

In order to tune the database, oracle provides a diagnostic tool by which you can find out the health of your database. This guide provides beginners and novices how to install configure and use statspack tool and its reports.

1. Installation

 Installing the statspack is an easy step. The only two things that need to be done is to execute the statspack creation script that is located in %OracleHome%\rdbms\admin folder. The script is named as “spcreate.sql” for creating the statspack objects and “spdrop.sql” to drop the statspack user and its associated objects. The second thing though not necessary that you can do is to dedicate a seperate tablespace for statspack, i’ve created a seperate tablespace named STATSPACKTBS for it.

In oder to install the statspack, use the following comand replacing the location where the script is located:

SQL> @c:\oracle\rdbms\admin\spcreate.sql;

During the execution of the script, it will ask three things from the user (1) the password for the username of statspack which is “perfstat”.  (2) Default tablespace for statspack (mine is dedicated STATSPACKTBS), and (3) Temporary tablespace for use.

2.  Generating Snapshots

In order to generate statspack report, first a snapshot of the database needs to be taken so that the analysis can be done. The report is always generated between any two snapshots. To generate a snapshot use the following command:

SQL> execute statspack.snap;

3.  Generating Statspack Report

In order to generate statspack report, we only have to execute another script supplied by oracle located in rdbms\admin folder named as “spreport.sql”. To generate a report, it is necessary that you must have atleast two snapshots of the database otherwise lots of portions of the report would not be analysed or calculated by the statspack tool. To generate a report, use the following command :

SQL> @C:\oracle\rdbms\admin\spreport.sql;

At the start of the report, it will show the list of snapshots currently present and would onwards ask for the beginning and ending snapshot ID to compare and analyse.