Oracle Statspack spauto for Automated Reporting without AWR

Oracle Statspack spauto for Automated Reporting without AWR

n recent weeks I was called upon to do a performance evaluation of an Oracle 11g database.  Due to the fact that the customer does not have access to AWR reports due to licensing, I was planning on using StatsPack for gathering performance data.  As usual I started out by creating the user and schema objects using spcreate.sql.  I have run statspack many times but I was unaware of the spauto.sql script.  This script will automatically create a statspack snapshot every hour just as with AWR but does not use AWR data.  This allows for regular collection of performance data using stats pack spauto with no additional licensing fees (Performance Pack, Diagnostics Pack).

I have tested this and it works well.  The spauto.sql script creates a job called statspack.snap that is run as the perfstat user.  This job is visible via dba_jobs, or user_jobs (as perfstat).  The job runs every hour and creates a new snapshot.  Unlike AWR reports, the statspack snapshots are not automatically purged.  In order to remove old snapshots you can use sppurge.sql or create your own script to purge old snapshot data.

AWR is the preferred method of obtaining Oracle performance data but if you cannot use AWR, statpack with spauto is the next best thing.

By |2017-09-20T08:09:55+00:00June 3rd, 2016|Performance Wire|Comments Off on Oracle Statspack spauto for Automated Reporting without AWR