Installing Statspack

Create a tablespace which will hold the STATSPACK objects:

SQL> create tablespace SPTBS datafile '/u01/oradata/pavc2data/sptbs01.dbf' size 500M autoextend off

Create the STATSPACK user PERFSTAT:
SQL> create user perfstat identified by perfstat 2 default tablespace sptbs;
User created.

Run $ORACLE_HOME/rdbms/admin/spcheck.sql....spcpkg.sql from SYS user.
This script will prompt from PERFSTAT user password (enter perfstat), The default tablespace (enter SPTBS), And the default temporary tablespace (TEMP).

Check the $ORACLE_HOME/rdbms/admin/spcpkg.lis for errors.

Taking the SNAPSHOT:
connect to PERFSTAT user and run the statspack.snap package:

SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
Run the following anonymous block to get the snap_id: (Begin Snap_id)

SQL> variable snap number;SQL> begin :snap :=statspack.snap; end;
PL/SQL procedure successfully completed.

SQL> print snap
SNAP
----------
2

Execute the statspack.snap package to get the second snap_id (end snap_id)
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.

SQL> variable snap number;SQL> begin :snap :=statspack.snap; end;
PL/SQL procedure successfully completed.

SQL> print snap
SNAP
----------
4
Generating the snapshot report:

Run the $ORACLE_HOME/rdbms/admin/spreport.sql as PERFSTAT user:
Enter the begin snap_id and the end snap_id followed by the report name.

The report will be created in $ORACLE_HOME/rdbms/admin/ directory