Statspack Configuration

Step 1: Login as sysdba and execute spcreate.sql ($ sqlplus "/as sysdba")
Step 2: SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Step 3: Enter value for perfstat_password
Step 4: Choose the Default tablespace for the PERFSTAT user
Step 5: Choose the Temporary tablespace for the PERFSTAT user

To Take Snapshots:

Step 1: Login to perfstat user (sqlplus perfstat/perfstat@dbname)
Step 2: Execute statspack.snap (ie. exec statspack.snap)
Step 3: Execute statspack.snap with 15 minutes interval.
Step 4: Check the list of snapshot using
SELECT snap_id, snap_time
FROM stats$snapshot;
Step 5: Execute spreport.sql to take report(SQL>@rdbms/admin/spreport.sql)
It Prompts for two snapshot Id and report location. You can use the above query to find snap_id.

Steps to Drop Statspack Configuration:
Step 1: Login as sysdba ($sqlplus "/as sysdba")
Step 2: Execute spdrop.sql (i.e SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql)