How to set up statspack on Oracle 9i, 10g or even 11g

Why use statspack, when you have AWR with version 10g and 11g ? Well contrary with AWR, statspack is free, and also easy to implement. You can use it without having OEM Grid Control $$$. Maybe your site needs to have 9i version for a specific application, and migration is not (yet) possible. So you have to implement database performance monitoring, and historic capabilities, without the bells and whistles of OEM Database/Grid Control. Statspack is also available on Oracle 10g and 11g.

We can then use this information for performance analysis, and maybe even implement dashboard capabilities with graphics using rrdtools. First, let’s set this up. In this case, we’ll assume we’re doing a clean install. I’ll discuss in another article how to clean up a messy statspack install. We will start by creating a tablespace just for database performance stats ( not the table stats used by the optimizer ). We want to avoid using the SYSTEM tablespace.

Let’s say we keep about a month’s worth of history, 200M should be enough. Allow for growth in your filesystem. See with your sysadmin for the directories, mountpoints,  etc… ( replace ${ORACLE_SID} with the correct instance identifier or use it in a shell script. )

create tablespace TSD_PERFSTAT datafile
‘/u02/oradata/${ORACLE_SID}/TSD_PERFSTAT.dbf’
size 200M autoextend on next 20M maxsize 1024M;

create temporary tablespace TSD_PERFSTAT_TMP tempfile
‘/u02/oradata/${ORACLE_SID}/TSD_PERFSTAT_TMP.dbf’
size 200M autoextend on next 20M maxsize 1024M;

Now we create a user PERFSTAT using Oracle supplied packages. In sqlplus, log in as a dba or sysdba, and use the tablespaces created earlier to use as default and temporary tablespace.

@?/rdbms/admin/spcreate
Specify PERFSTAT user’s default tablespace
Enter value for default_tablespace: tsd_perfstat
Specify PERFSTAT user’s temporary tablespace.
Enter value for temporary_tablespace: tsd_perfstat_tmp

Now, let’s use it. Once it is set up, you have to take a snaphot from the beginning of the period you want to monitor, and another one at the end of this period. Usually, you will want to have an hourly snapshot taken. Use it in a shell script you can call using crontab.

sqlplus perfstat/perfstat

EXECUTE statspack.snap;

Now, anytime you want to see the results, just use spreport and find out which period you want, and a report is generated. Consider having a directory just for statspack reports, and protect it with proper permissions.

sqlplus /nolog
connect perfstat/perfstat
@?/rdbms/admin/spreport

DB Id       DB Name      Inst Num Instance
----------- ------------ -------- ------------
2618106428  PRD1                1 prd1
Completed Snapshots
                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------
prd1         PRD1             1 11 May 2000 12:07     5
                              2 11 May 2000 12:08     5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2 To use this name, press to
continue, otherwise enter an alternative. Enter value for report_name:

Using the report name sp_1_2

You can also script this and call it using crontab. Next step is understanding and interpreting this report, and there are excellent books on this subject.