How to install Oracle STATSPACK

To install STATSPACK utility, I created a new tablespace to associate later to a new user, PERFSTAT created using spcreate.sql script






This script is located at:
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql

Log in as SYS user and then run the sql script. It will ask for PERFSTAT password and his default and temporary tablespaces. Try to choose a good password because that user will have many privileges.


If no error is reported STATSPACK tool is available to use.


To simply create a new snap, connect as PERFSTAT user and then run:
SQL> exec statspack.snap;

Get another snapshot after 15-20 minutes.
To view the available snapshot use the following query:
SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;

NAME SNAP_ID Date/Time
--------- ---------- -------------------
BILLING 21 17.02.2010:17:44:09
BILLING 22 17.02.2010:17:59:46
BILLING 1 16.02.2010:12:52:04
BILLING 11 16.02.2010:13:04:14

To create a new report run:
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql

It will ask for two SNAP_ID, a report file name... and then your report will be created.