Script to Generate Statspack report for a day

– generateReportScript.sql

spool DayReports.sql

set heading off pages 9999

select ‘define begin_snap=’||snap_id||’;'||chr(10)||
‘define end_snap=’||to_number(snap_id + 1)||’;'||chr(10)||
‘define report_name=sp_’||to_char(snap_time,’ddmmhh24mi’)||’.txt’||chr(10)||
‘@?/rdbms/admin/spreport;’||chr(10)
from stats$snapshot
where trunc(snap_time) = trunc(sysdate -1)
order by snap_time asc;

spool off

I don’t have an understanding of analytic functions yet so the above script crudely just adds on 1 to the snap_id to get the end_snap for a report but if you’ve set up your site to take snapshots every 15mins then the script works fine producing the following script:

– DayReports.sql

define begin_snap=1621;
define end_snap=1622;
define report_name=sp_03060600.txt
@?/rdbms/admin/spreport;

etc…

which when run as the PERFSTAT user produces the required files

sp_03060600.txt
sp_03060615.txt
sp_03060630.txt
sp_03060645.txt
sp_03060700.txt
sp_03060715.txt
sp_03060730.txt
sp_03060745.txt
etc…

which can then be grepped as required!