Wednesday, February 4, 2009

Statspack

I have worked in databases including IBM's DB2 (infact I am at IBM certified DB2 DBA for version 8.1), Open source MYSQL, Oracle (From Version 8). As a performance tester, I used to wonder how Oracle along can provide such wide range of monitoring facility that no other vendor is able to match. From System wide monitoring using statspack/AWR to session level trace, Oracle's monitoring capability is amazing. This article I am going to cover couple of basic things about Oracle’s very own monitoring utility Statspack. Statspack is the build in tool and installation script comes along with the database itself. (No need to pay even an extra penny from your pocket). All you need is just to install the same and start using the same.

Quote from Oracle Database Documentation about statspack:
"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."

Installation:
To invoke the Statspack setup, all you need to do is call the spcreate.sql script which is available in ORACLE_HOME/rdbms/admin. PERFSTAT user own all PL/SQL code, database objects including tables, sequence, constrains etc., In windows, you can login into Oracle user (using SQL* Plus) which has enough privilege to install and run the following 
%ORACLE_HOME%\rdbms\admin\spcreate
In Linux/Unix OS, run the following
$ORACLE_HOME/rdbms/admin/spcreate

During installation it will ask for PERFSTAT schema’s password (usually people use perfstat as password), permanent tables and temporary tablespace. SPCREATE.SQL install script in turn automatically calls the following scripts.
SPCUSR.SQL: Creates the PERFSTAT user and grants privileges required to collect the performance data from V$ tables.
SPCTAB.SQL: Creates the tables which are going to stores performance data.
SPCPKG.SQL: Creates the package required for monitoring, data purging, reporting

Installation script dumps errors (if any) into SPCUSR.LIS, SPCTAB.LIS, and SPCPKG.LIS output files. 

How it works:
Snapshot of database’s performance was taken, stored in the PERFSTAT tables and will be assigned a unique SNAP_ID for the INSTANCE. Typically we can take snapshots for every pre-defined interval and between snapshots, we can generate performance report. If instance is restarted between snapshots, then reports will be meaning less. Snapshot can be taken at various levels depending up level of monitoring data required. Snapshot level can range from 1 to 10. Default level will be 5. More the level, more performance data (consumes bit high resource also).

Taking snapshot
Login as PERFSTAT user or user which can has execute privilege on statspack package and call statspack.snap function. Few examples.

exec statspack.snap
exec statspack.snap(I_SNAP_LEVEL=>7)

Statspack report
Statspack report will give instance-wide statistics between two snapshots. Between snapshots, we can generate reports (but if instance is restarted between snapshot, report will not be meaningful). Just call SPREPORT.SQL and provide begin SNAP_ID, end SNAP_ID and report file name. 

Oracle 10g and above has come up with something (feature which combines Statspack & ADDM) called Automatic Workload Repository(AWR). But statspack is still supported.

No comments: