STATSPACK

STATSPACK

Below are the steps for generating a statspack Report:

1. User must have SYSDBA privs

set ORACLE_SID=orcl
sqlplus sys/pwd as sysdba

2.You should create SEPARATE TABLESPACE for statspack report.

create tablespace PERFSTAT
datafile 'c:\perf01.dbf' size 100m
autoextend on
segment space management auto;

We should generate STATSPACK report DURING PEAK TIME
run SPCREATE.SQL script
Located : $ORACLE_HOME\rdbms\admin\spcreate.sql

conn sys as sysdba
@$ORACLE_HOME\rdbms\admin\spcreate.sql

4.connect with PERFSTAT user
perfstat user create automatically when we run SPCREATE.SQL script

conn perfstat/password

5.run STATSPACK.SNAP procedure

exec statspack.snap;

Note: After 15 minutes run again STATSPACK.SNAP procedure

6.run SPREPORT for generate statspack report in text format.

@$ORACLE_HOME\rdbms\admin\spreport.sql

7.connect with SYSDBA privs user ( sys user)
and execute SPDROP.SQL script for drop PERFSTAT user and all related tables

conn sys as sysdba
@$ORACLE_HOME\rdbms\admin\spdrop.sql

8.DROP TABLESPACE use for STATSPACK REPORT

conn sys as sysdba
drop tablespace [TABLESPACE NAME]
including contents and datafiles

Note: analyze your report

http://www.statspackanalyzer.com/analyze.asp
http://www.oraperf.com