Installing and Configuring StatsPack

Statspack is a performance tuning package provided by Oracle. It’s free to use and a reliable tuning tool since Oracle 8i. You can install it on Oracle 10g and 11g if you don’t have license for tuning pack. Oracle recommend installing the statspack package into a different tablespace than system tablespaces. “TOOLS” tablespace of Oracle 9i is  because I’ll install it to . If you use Oracle 10g+, you need to create this tablespace.

During the installation, the user PERFSTAT will be created. This user will own all objects of the STATSPACK package. The installation SQL script will prompt for the PERFSTAT user’s password and default and temporary tablespaces.

You need to check spcpkg.lis for any errors. You can test your statspack install by issuing:

You can create spauto.sql script to automatically schedule an hourly data collection for statspack:

If you want to remove statspack, run spdrop: