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