Oracle Database Statspack Report Generation

Overview:

This article is a comprehensive guide for the generation of STATSPACK Report for Oracle Database.

STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK can be considered BSTAT/ESTAT's successor, incorporating many new features. 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
• traditional wait events and initialization parameters

Using Statspack we can collect statistics which are put in specific tables. When we need, we can run reports based on these tables (snapshots) to tune the database.

Remember to set "timed_statistics" to "true" for the instance. Setting this parameter provides timing data, which is invaluable for performance tuning.

Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to acceess the official STATSPACK Report Generation guide for Oracle Database.

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

Objective of the Article:

By the time we finish this article, we should be able to understand the following:

(1)Basic idea of STATSPACK Report.

(2)Installation of STATSPACK Utility.

(3)Generation of STATSPACK Report.

(4)Removal/DeInstall of STATSPACK Utility.

 How STATSPACK Works?

When we run the Statspack installation script(spcreate), the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package and is granted limited query-only privileges on the "V$ views" required for performance tuning.

Each time a new collection is taken, a new SNAP_ID is generated.

After snapshots are taken, we can run the performance report. The report prompts us for start and end snapshot IDs and then calculates activity on the instance between the two snapshots, much like a "BSTAT/ESTAT" report. To compare, the first "SNAP_ID" supplied can be considered the equivalent of running "BSTAT"; the second "SNAP_ID" specified can be considered the equivalent of "ESTAT". 

 For screenshot of the steps please visit :: http://www.sensehaze.com

Installing STATSPACK:

The STATSPACK utility requires an isolated tablespace to obtain all of the objects and data. We are going to create "TOOLS" tablespace for the purpose.

For creating the tablespace, fire the below mentioned SQL command:

SQL> create tablespace TOOLS datafile 'our_data_file_path\tools.dbf' SIZE 100M;

According to your database size/requirement choose the size and options for "TOOLS" tablespace.

Run the Create Scripts:

SQL> @?/rdbms/admin/spcreate;

Set "timed_statistics" to true:

FOr doing so run the below mentioned command:
SQL> alter system set timed_statistics = true; 

 Taking STATSPACK Snapshots:

Connect to the database as "PERFSTAT" user.

For generating statspack report we need two snapshots ( begin snap , end snap ). So , now we are going to take snapshots , as mentioned below:

For taking the snapshot, fire the below mentioned SQL command:
SQL> execute statspack.snap ;

For viewing the snapshots taken, we can query "stats$snapshot,v$database".
SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database;  

For screenshot of the steps please visit :: http://www.sensehaze.com

 Generate STATSPACK Reports:

To generate a report for the database, we have to fire the below mentioned query:
SQL> @?/rdbms/admin/spreport

 

 Removing STATSPACK:

To remove/deinstall "STATSPACK", connect as a user with "SYSDBA" privilege and run the following "SPDROP" script from SQL*Plus.

SQL> CONNECT / AS SYSDBA

SQL> @?/rdbms/admin/spdrop

SQL> drop tablespace TOOLS including contents and datafiles;

For screenshot of the steps please visit :: http://www.sensehaze.com