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:
SQL>@?/rdbms/admin/spcreate.sql
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 ):
SQL>@?/rdbms/admin/spauto.sql
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:
SQL>@?/rdbms/admin/spreport.sql
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* ).
Daniel Westermann's Blog 