How to know if STATSPACK is installed

 

Statspack is important tool for dba to investigate performance issue in oracle database, it makes DBA's life easy in performance issues cases. Most of the time, statspack is not installed automatically unless it is specified in custom script prepared from database creation.

The question is how to check if statspack is installed or not? There are number of ways to detect that, we will discuss few here

 

1.  PERFSTAT user

statspack uses PERFSTAT user to perform its operation, so check in DBA_USERS whether user is present of not using select * from dba_users where USERNAME='PERFSTAT';

2. Presence of table STATS$DATABASE_INSTANCE

statspack generates snap id's and save information in table STATS$DATABASE_INSTANCE, and table is created during installation of statspack, check using desc STATS$DATABASE_INSTANCE if table is present, if present then statspack is installed otherwise not

3. Running report using spreport.sql

For DBA's convenience, there are number of scripts provided by oracle in admin folder under oracle home directory, spreport.sql is such script used to generate statspack report , try running this sql file, if it asks for begin snap id and end snap id then assume statspack is installed otherwise it will throw error.