Changing spreport to run StatsPacks from the command line...

Statspack Reports can be useful for diagnosing performance problems, but they are only useful if they are generated and available!

I wanted to set up an operating system job to generate statspacks. First I wrote a cron job to execute statspack.snap() at intervals during our peak usage times of the day. Then I wrote a quick Perl script to log in as a PerfStat user and determine the beginning and ending snaps. Once the 'pairs' of snap Ids were determined I have the Perl script kick off the generation of the statspacks by shelling to SQLPLUS, running spreport.sql with the parameters for each snap.

Here are the changes I had to make to spreport.sqlto support passing parameters to create StatsPack Reports from the command line.

1) Update spreport.sql

Right now spreport.sql's job is to find information on the Instance and save it into a few variables, then call sprepins.sql to do the heavy lifting of generating the report. We're going to add a little code so that spreport.sql now accepts 3 new parameters and makes them available to sprepins.sql.

Wrap the call to @@sprepins like this:

define begin_snap   = &1;
define end_snap     = &2;
define report_name = &3;

@@sprepins

exit

Why does this work?

Remember the difference between single '&' and double '&&' in SQLPLUS? They are used to prompt for substitution variables. The single '&' will prompt for a value every time. The double '&&' will prompt for a value if it hasn't been specified before. Since we are specifying the value of begin_snap, end_snap, and report_name in spreport.sql, and they already have &&'s in sprepins.sql, they are not prompted for again.

2) Run it!
Now you can run:

sqlplus perfstat/perfstat@tnsname @spreport 123 124 c:test.txt

And SQLPLUS will run using 123 for the begin_snap, 124 for the end_snap, and c:\test.txt for the report name.

3) Query Snap Info from Perfstat user
I'll leave the scripting up to you but once you have the begin/end snap ids and a filename you can generate the report. Incorporate this into a script to automatically generate StatsPack reports.

Here is a query to return data about today's snaps:

select snap_id, to_char(snap_time, 'YYYY-MM-DD HH24MI') as snap_datetime
from stats$snapshot
where trunc(snap_time) = trunc(sysdate)
order by snap_id

Comments

I was very happy to find this web page at the end of 2013. Oracle 9i databases are history in 2013 but some shops (like in my case) are still running 9.2.0.6 and getting STATSPACK reports auto-generated from the crontab execution of the .sql script (with your help here) is very beneficial for our application support.
Thank you for this post.
ora10g@gmail.com

Thanks for the comment. I fired up Statspack for an 8i database last night. First time in a long time but it gave me enough info so I was able to solve the problem. Normally I'm not a "Ratio Tuner" but the Buffer Cache Hit was a miserable 2% so we were definitely starved with 8mb of DB Buffer Cache for a 1000mb database. I had to read the instructions on how to install, I am so spoiled with AWR these days!

Add new comment