Statspack Installation - Steps

Detailed Steps to Configure Statspack!

Definition made simple: -
The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

Space Requirements for Statspack!

Approximately 64MB is required to install Statspack.
If you install the package in a dictionary-managed tablespace, then you should monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
*** If you install the package in a locally managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.


Start SQL*Plus.

Connect as a user with SYSDBA privilege.

For example: SQL> CONNECT / AS SYSDBA

Run the SPCREATE.SQL script.

On UNIX platforms, enter the following:

SQL>@?/rdbms/admin/spcreate
On Windows platforms, enter the following:

SQL>@%ORACLE_HOME%\rdbms\admin\spcreate

Once you run this script it will prompt you enter password for perfstat user.
You need to enter default tablespace “Which Oracle recommends ‘TOOLS’
And its asks you to enter Temporary tablespace and that is your “TEMP” and within few minutes your Statspack ready to use.

*** Once I learn using Statspack I get back with the analysis report.