Modifying sprepins.sql for Custom Statspack Reports I

I use STATSPACK extensively for comparing two time periods, comparing a time period to a baseline, monitoring trends and diagnosing performance problems. It provides important data about the database performance. The code producing statspack reports is open and you can modify it to your needs.

You run $ORACLE_HOME/rdbms/admin/spreport.sql to produce statspack reports. spreport.sql calls sprepins.sql which is the script that produces the report. You can easily modify sprepins.sql to add, remove and change sections in the report. By sections I mean parts in the report that start with headers like "SQL ordered by Gets", "Instance Activity Stats", "Load Profile", etc... I use a modified sprepins.sql to produce statspack reports that suit my needs. I want to share those modifications in a few posts here. You can use these modifications, modify them to your needs or even introduce new changes like these. Note that these changes are tested on 9.2 only.

The first two modifications I made are; entering number of days to list snapshots for and using a standard name for report outputs.

Number of Days

When spreport.sql is run it prints all the available statspack snapshots and asks for a begin snapshot id and an end snapshot id to produce a report that outputs the delta values between these snapshots. If you keeps days, weeks or even months of data in the PERFSTAT schema the listing of all snapshots can take time and you need to wait for a lot of pages to flow. To prevent this I add a new prompt that asks the number of days to list snapshots for (awrrpt.sql which produces the AWR report in 10G does this also). To make this change find the line with this sentence in sprepins.sql.

-- Ask for the snapshots Id's which are to be compared

This is the part where it lists the snapshots. Above this line put these lines.

set termout on
prompt
prompt Specify Number of Days
prompt ~~~~~~~~~~~~~~~~~~~~~~
accept num_days number default 1 prompt 'Enter number of days:'

This will ask you to enter the number of days to list and put your input into the variable num_days. If you do not enter something the default is 1 day. Now change the select statement after these lines to select only the snapshots for num_days number of days. The bold line below is the line you need to add.

select to_char(s.startup_time,' dd Mon "at" HH24:mi:ss') instart_fmt
    , di.instance_name                                  inst_name
    , di.db_name                                        db_name
    , s.snap_id                                         snap_id
    , to_char(s.snap_time,'dd Mon YYYY HH24:mi')        snapdat
    , s.snap_level                                      lvl
    , substr(s.ucomment, 1,60)                          commnt
 from stats$snapshot s
    , stats$database_instance di
where s.dbid              = :dbid
  and di.dbid             = :dbid
  and s.instance_number   = :inst_num
  and di.instance_number  = :inst_num
  and di.dbid             = s.dbid
  and di.instance_number  = s.instance_number
  and di.startup_time     = s.startup_time
  and s.snap_time>=trunc(sysdate-&&num_days+1)
order by db_name, instance_name, snap_id;

Now if you save sprepins.sql and run spreport.sql it will ask the number of days:

Specify Number of Days
~~~~~~~~~~~~~~~~~~~~~~
Enter number of days:

After that it will print the snapshots for that number of days and you can continue as usual by entering snapshot ids.

Report Names

After entering the begin snapshot id and the end snapshot id sprepins.sql asks you for a report name and it will use a default report name like sp_1_2.lst if you do not enter a name (where 1 is the begin snapshot id and 2 is the end snapshot id).

To produce a standard report name I remove the report name prompt and use a default name. With this modification I have report names like PROD_22_05_2007_13_30__13_45.LST where PROD is the instance name. This way I can quickly understand which instance and which time period the report is produced for. This name tells that this report is for the instance PROD, reporting the period between 22-MAY-2007 13:30 and 22-MAY-2007 13:45.

To make this change remove or comment the following lines in sprepins.sql.

prompt
prompt Specify the Report Name
prompt ~~~~~~~~~~~~~~~~~~~~~~~
prompt The default report file name is &dflt_name..  To use this name,
prompt press  to continue, otherwise enter an alternative.

set heading off;
column report_name new_value report_name noprint;
select 'Using the report name ' || nvl('&&report_name','&dflt_name')
  , nvl('&&report_name','&dflt_name') report_name
from sys.dual;
spool &report_name;

In place of these lines enter the following.

column begin_time new_value begin_time noprint;
column end_time new_value end_time noprint;

select to_char(snap_time,'DD_MM_YYYY_HH24_MI') begin_time
from stats$snapshot
where snap_id=&begin_snap;

select to_char(snap_time,'_HH24_MI') end_time
from stats$snapshot
where snap_id=&end_snap;

set escape on
spool &&inst_name\_&begin_time\_&end_time;

Be aware that if you are taking a report for a period spanning two days the report name will be misleading as I only take the hour-minute part for the end snapshot. This name standardization makes keeping and referencing old reports easier.

The next post will be about changing the "Top 10 Timed Events" section of the statspack report.