Configure STATSPACK to monitor performance on Oracle Database

Actually Oracle includes a set of internal tools to capture and maintain history of database performance. This group of tools called “Automatic Workload Repository” (AWR from now) exists in any database from 10g. The big issue with AWR is the requirement of “Database Diagnostics Pack” license to access the information (this implies that this information only is accessible on Enterprise Edition databases).

In fact, in any Standard/Enterprise Edition Database all this information is gathered continuously, but we not are allowed to view or use it without the necessary database version and optional licenses.

Before appearance of AWR (on Oracle 9i or 8i era) exists another tool used to store runtime statistics of the database called STATSPACK, this tool continues to be present in all versions and releases of the database from they first appearance (on Oracle 8i). Also this tool have received improvements on every release (as you can view on the “changelog” of statspack installation scritps).

Basically consists on a schema containing some tables and packages, one of these packages must be executed periodically to capture information from different areas of the database (Waits, SQL, IO, memory management, ..) and store in statspack tables. After that we can execute some included scripts to generate reports comparing the values of the data capture between two different points in time (called snapshots).

Installation

You can install STATSPACK on any  existent tablespace, but I prefer to create a tablespace only for it (it makes easy to control data grow and remove it if necessary).

SQL> create tablespace PERFSTAT datafile '+DATA' size 2G autoextend on next 100M;

Tablespace created.

The size depends on the activity of our database and the retention period you want to use, more on it later.

After that and connected with any user with SYSDBA role (sys for example) we must execute the script to create the statspack user and components.

The script spcreate.sql located on $ORACLE_HOME/rdbms/admin of our database software installation must ask for the password of the new created schema, the tablespace for the data (the previous created one in our case) and the temporary tablespace.

SQL> @?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: PERFSTAT
PERFSTAT

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME 	       CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
PERFSTAT		       PERMANENT
SYSAUX			       PERMANENT *
USERS			       PERMANENT

Pressing return will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME 	       CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP			       TEMPORARY *

Pressing return will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

...

SQL> set echo off;
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

Now, if all goes as expected, we have a working installation of STATSPACK, to capture information data manually we must execute this scrip:


SQL> connect PERFSTAT/PERFSTAT
Connected.
SQL> exec STATSPACK.SNAP;

PL/SQL procedure successfully completed.

The “snap” function capture information about the actual status of the database and store on the statspack tables, this function accepts parameters, for example the “level” of the snapshot. The different levels capture more or less information about the database, and the default one are level 5.

Levels >= 0 General Performance Statistics
Levels >= 5 Additional Data: SQL Statements
Levels >= 6 Additional Data: SQL Plans and SQL Plan Usage
Levels >= 7 Additional data: Segment Level Statistics

Also is possible to configure some other parameters (thresholds for example). The documentation for statspack was removedwhen AWR appeared on the database, thus the release 9.2 of the documentation is the last who have it.

Programming

Take snapshots manually is a good practice to debug “online” performance problems, or to check resource usage for a controlled procedure or task, but must be a good idea to leave task taking snapshots every hour for example to allot review performance changes in the past.

The statspack scripts have one used to programe a DBA_JOB to do this, is the $ORACLE_HOME/rdbms/admin/spauto.sql

I recommend to use a more advanced version using DBMS_SCHEDULER and include some “purge of old data” in the same job of capture.

NOTE: In case of a RAC Database is necessary to program the job in every instance (the snap procedure only captures data from our local instance). In this case is also recommended to “switch” the execution time of the snapshot on every instance some minutes to avoid launch all data capture jobs at same time.

This is an example for a two node RAC, note the usage of the “instance_id” attribute to decide in witch instance execute every job. If your database is a non RAC one, you must use the first job only.

I decided to take a snapshot hourly (you can change the snap frequency to meet your needs) and after the capture of data I execute the STATSPACK.PURGE to remove information older than 90 days (also you can change retention period).

Is also important to use “exteded purge” (the default is not to use it) this implies execution of some resource intensive deletes, but allow purge of dat from all tables (by default some tables, as the one containing SQL sentences, is not purged).


PROCEDURE PURGE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 I_NUM_DAYS			NUMBER			IN
 I_EXTENDED_PURGE		BOOLEAN 		IN     DEFAULT
 I_DBID 			NUMBER			IN     DEFAULT
 I_INSTANCE_NUMBER		NUMBER			IN     DEFAULT

 


BEGIN
sys.dbms_scheduler.create_job(
job_name => 'PERFSTAT.STATSPACK_ORACLERAC1',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
-- REMOVE OLD DATA
statspack.purge(sysdate-90,true,null,null);
-- CAPTURE NEW DATA
statspack.snap();
commit;
end;',
repeat_interval => 'FREQ=HOURLY',
start_date => to_timestamp_tz('2015-06-12 11:00:00 Europe/Vienna', 'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Statspack data maintenance',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute('PERFSTAT.STATSPACK_ORACLERAC1','max_failures',10);
sys.dbms_scheduler.set_attribute('PERFSTAT.STATSPACK_ORACLERAC1','max_run_duration','+000 00:15:00');
sys.dbms_scheduler.set_attribute('PERFSTAT.STATSPACK_ORACLERAC1','instance_id',1);
sys.dbms_scheduler.enable('PERFSTAT.STATSPACK_ORACLERAC1');
COMMIT;
END;
/

BEGIN
sys.dbms_scheduler.create_job(
job_name => 'PERFSTAT.STATSPACK_ORACLERAC2',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
-- REMOVE OLD DATA
statspack.purge(sysdate-90,true,null,null);
-- CAPTURE NEW DATA
statspack.snap();
commit;
end;',
repeat_interval => 'FREQ=HOURLY',
start_date => to_timestamp_tz('2015-06-12 11:05:00 Europe/Vienna', 'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Statspack data maintenance',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute('PERFSTAT.STATSPACK_ORACLERAC2','max_failures',10);
sys.dbms_scheduler.set_attribute('PERFSTAT.STATSPACK_ORACLERAC2','max_run_duration','+000 00:15:00');
sys.dbms_scheduler.set_attribute('PERFSTAT.STATSPACK_ORACLERAC2','instance_id',2);
sys.dbms_scheduler.enable('PERFSTAT.STATSPACK_ORACLERAC2');
COMMIT;
END;
/

 

Reporting

Finally to obtain reports from the captured data we must use the $ORACLE_HOME/rdbms/admin/spreport.sql script, this script must ask for two snaps to compare and shows de data captured between them. The interpretation of this data must be covered on future posts, but a good start point is the “Top 5 Timed Events” paragraph, showing in wat type of tasks (CPU or/and waits) our database spends time.

Also, and depending on the level used on the capture we can use  $ORACLE_HOME/rdbms/admin/sprepsql.sql to view plan details of SQL sentences captured on the report.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s