Installing the Statspack And Generating Reports

Installing the Statspack

Installation of the Oracle Statspack tool is a relatively simple process. The following is a step-by-step guide to the process of installing Oracle Statspack on a UNIX system.

1. Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:

# cd $ORACLE_HOME/rdbms/admin/

2. Start the Statspack install script, spcreate.sql, as follows:

# sqlplus "/ as sysdba" @spcreate.sql

3. Enter a password for the PERFSTAT user when prompted.
4. Enter the default tablespace (tools) for the PERFSTAT user when prompted.
5. Enter the temporary tablespace (temp) for the PERFSTAT user when prompted.
6. Exit sqlplus as follows:

SQL> exit

Collecting Snapshots

Once the Oracle Statspack tool is installed, snapshots must be collected to evaluate database performance. Snapshots are moment-in-time collections of all of the database statistics that the Oracle database continuously collects. Once two snapshots are collected, they can be compared to identify the activity that occurred during the interval between the two snapshots.

Snapshots can be collected a various levels, each increasing level collecting a greater amount of information about the database. As the levels go higher, each level is inclusive of the information collected at the levels below it.

Table A-1 Levels of Statistics
Level	Information Collected
0	General Performance Statistics
5	Addition Data: SQL Statements
6	Addition Data: SQL Plans and SQL Plan Usage
7	Addition Data: Segment Level Statistics
10	Addition Data: Parent and Child Latches

To collect statistics

1. Connect to the database as the PERFSTAT user as follows:

sqlplus perfstat/<password>

2. Create a snapshot with the statspack package as follows:

SQL> execute statspack.snap(i_snap_level=>7);

3. Exit SQLPLUS as follows:

SQL> exit

Generating Reports

Oracle Statspack comes with a comprehensive reporting script called spreport.sql. When this script is run, it outputs a list of available snapshots, asks the user for two snapshot IDs and a name for the report, and then outputs a text report of the results.

To run a Statspack report.

1. Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:

# cd $ORACLE_HOME/rdbms/admin/

2. Run the standard Statspack report as follows:

# sqlplus perfstat/<password> @spreport

• Enter a beginning snapshot ID.
• Enter an ending snapshot ID.
• Enter a name for the report or accept the default.
• Exit SQLPLUS as follows:

SQL> exit