Installing and configuring Statspack

First thing is to isolate the data in a specific tablespace. The installation script of Statspack will ask you for one, so let’s create it! (I usually name it "PERFSTAT", but you can choose whatever name you want)

The best thing to do would be to set its size to its final size if you know it. I prefer to set it to its target size and let it extend a little bit if needed.

For databases with low activity, statspack snapshots every 1 hour and keeping 8 days of history, I usually have statspack data around 1G.

The spcreate.sql will create the PERFSTAT user, packages and tables. This script will call the following subscripts:

You can either install Statspack in interactive mode using the command line bellow which will prompt for:

To install Statspack in batch mode, just assign the variables in sqlplus as show in the example below. Adapt the variables values to fit your environment.

To schedule the automatic collection of statspack statistics, you can use the spauto.sql script. This script creates a job (using DBMS_JOB package), scheduled to run once an hour at 00.

As this script is quite static and uses the old job scheduler of Oracle, you can choose to create a job manually using the DBMS_SCHEDULER package and customize the snapshot capture frequency. In the above example, we create a job as SYS to schedule the statspack statistics collection every 15 minutes. Be careful that thaking frequent snapshots will generate a large amount of data. In this case, you should reduce the history retention.

If you want to create this kind of job directly using the PERFSTAT user (which is, according to me, a cleaner way to work), you will need to grant the create job system privilege. The above example creates a job with the same settings as the previous one, but with the correct privileges:

The last query should output like this:

There is no script like the spauto to automatically purge statspack snapshots.

The best thing to do is to use the PURGE method of the STATSPACK package. This method can be called with various parameters, but the one that is interesting for us now is the `` which specifies how many days you want to keep (setting it to 0 will only raise an error and do nothing. See the manual purge paragraph for more information about truncating tables).

In the example below, we schedule a job that will run every day at 1:56 PM to purge every data older than 8 days.

To list currently scheduled jobs for the given user, use the following query as in the previous paragraph:

To change the retention, you will need to change the job_action attribute like this (here you set it to 10 days):

You can modify the statspack parameters like using the modify_statspack_parameter of the STATSPACK package. This method will be explained in another post.

The most commonly modified parameter is the snapshot detail level which default to the level 5. Setting the level to a higher one is usefull when debugging, but it is not really advisable to have a level 10 for example on a running production server.

To have the detail of what the levels correspond to, use the following query:

Which returns:

This part can be either executed by the PERFSTAT user or any DBA user.

To launch a report on the instance you are currently working on, use the following sql script. It will ask for the snapshot number you want to start your report on, the one you want to stop your report on and the name of your report.

If you want to automate this, you just have to set the begin_snap, end_snap and report_name PL/SQL variables. To get the snapshots available, use the STATS$SNAPSHOT table. the SQL query below retrieves all the snapshots available and their corresponding date. You can then define the 3 variables using the define command or just go to the next example to have a much more automated method.

The above example show how to launch a statspack report over the last 12 hours. This won’t prompt you for any value. The report name will be something like 201311062300-201311071000_MYINSTANCE_hostname.sprpt (201311062300 is the first snapshot date of the report using the YYYYMMDDHH24MI format. 201311071000 is the same but for the last snapshot of the report) and lies in the current directory.

To adapt this to what you want, you only have to change the WHERE clause… easy! :)

To generate a snapshot in statspack, use the SNAP method of the STATSPACK package.

If, for debug purposes, you need to to temporarily use a snapshot with a more detailed level of informations, you can do it using the I_SNAP_LEVEL parameter of the STATSPACK.SNAP. It is advisable to comment this snapshot for later reference (use the I_UCOMMENT parameter of this method to do so).

For further informations about the options available in the SNAP method, see the post about the STATSPACK package (not done at the time I’m writing these lines).

If you want to make some manual cleanup of the snapshots, use the sppurge.sql script which will ask for the first snap id to delete and the last snap of the range to delete.

If you have a huge amount of snapshots to delete, you should really set the transaction to use big_rbs rollback segment as mentionned above:

To automate the purge of the snapshots, you can either use the following kind of script which, in our case, deletes all statspack data associated with snapshots older than 10 days:

Or rather use the PURGE version of the STATSPACK package. The example below also deletes all statspack data associated with snapshots older than 10 days:

To truncate all statspack data, use the sptrunc.sql script.

You can export STATSPACK schema using the `spuexp.par`par file like this: