there is still an alternative to awr

February 17, 2013 — Leave a comment

most people know that they require a license for using all that nice reports which can be generated out of the statistical data stored in the advanced workload repository ( AWR ). probably less people know that there still is an alternative which is free to use: statspack. although statspack is not that much automated as awr and therefore requires a bit more work to do it still can be used to generate reports which can point you to the right direction in case you face some performance issues.

so how do you work with it ?
as usual, the scripts are located in the rdbms/admin directory of your $ORACLE_HOME. statspack needs an initial setup to create all the objects required:


the script will ask a few questions:

  • the password for the perfstat user
  • the tablespace to store the statistical data ( you should probably create a separate tablespace for this )
  • the temporary tablespace to use

that’s it. statspack is installed and ready to use, almost. in awr snapshots are created automatically ( usually every hour if you didn’t adjust the interval ). with statspack the snapshots need to be created manually:

SQL> exec perfstat.statspack.snap;

there is even a little script which creates a job for you if you want to automate the snapshots ( hourly, by default ):


of course you might want to adjust the script if hourly snapshots do not fit in your case.

so, once you have at least two snapshots available you are ready to create a report:


similar to the awr report the script will ask for the begin and end snapshots as well as a name for the report and then will create the report for you. you’ll notice the similarities to awr immediately, e.g. the top 5 timed events:

Top 5 Timed Events						      Avg %Total
~~~~~~~~~~~~~~~~~~						     wait   Call
Event						 Waits	  Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time							24	    42.3
db file async I/O submit			   364		11     30   19.1
control file parallel write			   232		 8     36   15.0
db file sequential read 			   577		 4	6    6.6
log file parallel write 			    78		 3     41    5.7

ok, the output is not as nice the html reports of awr, but this is still a lot of information to work with, and it’s free.

there are some other scripts around which assist in maintaining the statspack repository:

  • spdrop.sql: for droppping statspack
  • sppurge.sql: for dropping a range a snapshots
  • sprepins.sql: for reporting differences between snapshots
  • sptrunc.sql: for truncating the statspack repository

… to name a few of them. there are some more which you might look at ( they all start with sp* ).

No Comments

Be the first to start the conversation!

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.