25^th SEP 2014

STATSPACK (Definition , Installation & Generation Report)

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters

source :-- http://docs.oracle.com/cd/B10501_01/server.920/a96533/statspac.htm

Installation of statspack

Step 1) login to the database as SYS user

sqlplus / as sysdba

Step 2) Check parameter job_queue_processes

sql> show parameter job_queue_processes

Its value should be > 0

if it is not set > 0 execute the below command

sql > alter system set job_queue_processes=5 scope=both;

Step 3) check if table perfstat exists on the database

SQL> select * from v$tablespace where NAME like '%PERF%';

If it donot exist create perfstat table

sql > create tablespace perfstat datafile '/PATH/perfstat.dbf' size 3G autoextend off;

/u01/app/oracle/oradata/DBPR11G

Step 4) Run the below sql query to deinstall any previous installation

SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql

Step 5) Run the below script to install statspack on the database . This script will create PERFSTAT user on the database

SQL > @$ORACLE_HOME/rdbms/admin/spcreate.sql

-- Enter perfstat as the default tablespace

-- Enter the name of the default tablespace

Step 6) Run the below pl/sql code to schedule the snapshot collection every 30 minutes .Run as sys user

variable jobno number;

variable instno number;

BEGIN

SELECT instance_number INTO :instno from v$instance;

DBMS_JOB.SUBMIT(:jobno,'statspack.snap;',trunc(sysdate,'HH24')

+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60),'trunc(sysdate,"HH24")

+((floor(to_number(to_char(sysdate,"MI"))/30)+1)*30)/(24*60)',TRUE,:instno);

COMMIT;

END;

/

Step 7) Generate statspace report

SQL > @$ORACLE_HOME/rdbms/admin/spreport

Follow the instruction 

If Below error comes

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 3494669306 for database Id
Using          1 for instance number
declare
*
ERROR at line 1:
ORA-20200: Database/Instance 3494669306/1 does not exist in
STATS$DATABASE_INSTANCE
ORA-06512: at line 23

Resolution :--

SQL> execute statspack.snap;