The Statspack 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.

How Statspack Works?

When you run the Statspack installation script, the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package.Statspack users become familiar with the concept of a snapshot, a single collection of performance data.Each time a new collection is taken, a new SNAP_ID is generated.

Installing Statspack..

1.) Creating tablespace s_perfstat:

SQL>create tablespace S_PERFSTAT datafile ‘D:\oracle\product\10.2.0\oradata\s_PERFSTAT.dbf’ size 200M autoextend on next 20M maxsize 1024M;

2.) Now run spcreate.sql script placed at :   D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\

3.) Take the snapshots now.

Snapshots must be collected to evaluate database performance. Snapshots are moment-in-time collections of all of the database statistics that the Oracle database continuously collects. Once two snapshots are collected, they can be compared to identify the activity that occurred during the interval between the two snapshots.

Snapshots can be collected a various levels, each increasing level collecting a greater amount of information about the database. As the levels go higher, each level is inclusive of the information collected at the levels below it.

c:\> sqlplus perfstat/perfstat

SQL> execute statspack.snap(i_snap_level=>7);

Generating Reports:

Oracle Statspack comes with a comprehensive reporting script called spreport.sql. When this script is run, it outputs a list of available snapshots, asks the user for two snapshot IDs and a name for the report, and then outputs a text report of the results.

1.) Goto D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\ and run spreport.sql

2.) Enter a beginning snapshot ID.

3.) Enter an ending snapshot ID.

4.) Enter a name for the report or accept the default.

Enjoy:-)