Statspack in 11g

You need Oracle tuning pack/diagnostic pack licences if you want to use Grid control or Database control performance analyzer.

Statspack is free utility for performance monitoring and reporting and it's there since Oracle 8i. Lets start with Installation of Statspack in 11gR2 DB.

- You can create separate tablespace for Statspack objects or use existing tablespsace too.

PERFSTATS is the owner for Statspack repository and this user will be created automatically during Installation.

SQL> connect / as sysdba
Connected.
SQL> @?/rdbms/admin/spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: 

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
SYSAUX PERMANENT *
TOOLS PERMANENT
USERS PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: 
Using tablespace SYSAUX as PERFSTAT default tablespace.
　
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------

Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: 
....
...
No errors.
NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.
SQL> 
　
It's time to connect to PERFSTAT user and take snapshot. You need snapshot before and the end of the period you want to analyze. You can schedule this to run every hour. That's what AWR does automatically for you.
$ sqlplus perfstat/perfstat
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
　
run you load or wait until period you are monitoring is over and take another snapshot.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed
It's time to run Statspack reports. 
SQL> @?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
961341030 ORCL 1 ORCL
　
　
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
961341030 1 ORCL ORCL server1
Using 961341030 for database Id
Using 1 for instance number
　
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
　
　
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL ORCL 1 21 Dec 2011 15:00 5
2 22 Dec 2011 16:00 5Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
　
　
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: test
...
...
End of Report ( test.lst )
SQL>
This will create OS file "test.lst" and time to do analysis.