Statspack

The Statspack utility has been available since Oracle 8i (8.1.6) to monitor database performance. Statspack stores the performance statistics in Oracle tables. The data collected can later be used for reporting and performance analysis.

Installing Statspack

When you run the Statspack installation script, it automatically creates a PERFSTAT user. PERFSTAT owns all the objects needed by the Statspack. The installation steps are as follows:

1. 1. Log into SQL*Plus with the SYSDBA privilege.

2. 2. Create a tablespace to hold STATSPACK data (for example, PERFSTAT_TBS).

3. 3. Execute the spcreate.sql file which resides in the ORACLE_HOME/rdbms/admin folder:

   -- script to create STATSPACK package
   SQL> @?\rdbms\admin\spcreate.sql
   

When the above script is run, it will prompt for the following information:

1. 1. Enter a password for the PERFSTAT user.

2. 2. Choose a tablespace (PERFSTAT_TS).

3. 3. Choose a temporary tablespace.

4. 4. Check the spcpkg.lis file for any errors.

   If spcpkg.lis reports any errors then...