Generate statspack report for the most recent interval quickly

Sometime we need to generate most recent statspack report to understand the problem that have just happened. The script to generate statspack report is spreport.sql, usually we execute this script in SQL* Plus as follows:

SQL> @?/rdbms/admin/spreport

We then need to interactively input start and end snap ids and report name. It is not difficult to modify this script to accept these input variables at command line. For example I have a script called spreport_ds.sql located in my SQLPATH, which can be used in the following way:

SQL> @spreport_ds.sql

The relevent modifications in my spreprot_ds.sql script are shown as follows:

prompt
-- prompt Specify the Begin and End Snapshot Ids
-- prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- prompt Begin Snapshot Id specified: &&begin_snap
-- prompt
-- prompt End   Snapshot Id specified: &&end_snap
prompt

define begin_snap = &1
define end_snap = &2

--
--  Set up the snapshot-related binds, and additional instance info

set termout off;

variable bid        number;
variable eid        number;
begin
  :bid       :=  &&begin_snap;
  :eid       :=  &&end_snap;
end;
/

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;

define report_name = &3
spool &report_name;
set heading on;
prompt

With this modified script in place, I created another script called sp_mrti.sql to generate statspack report for the most recent interval:

----- script: sp_mrti.sql -----------------
rem sp_mrti.sql
rem  -- generate a statspack report for the  most recent time interval
rem
rem  dependent:  spreport_ds.sql
rem

col eid new_val eid
col sid new_val sid
select max(snap_id)  eid,
       max(snap_id) - 1 sid
from perfstat.stats$snapshot
;

col stime new_val stime
col etime new_val etime

select  to_char(snap_time, 'YYYYMMDD_HH24MI') stime
from perfstat.stats$snapshot
where snap_id = &sid;


select  to_char(snap_time, 'HH24MI') etime
from perfstat.stats$snapshot
where snap_id = &eid;

col instname new_val instname
select instance_name instname from v$instance;


@spreport_ds &sid &eid  &instname._&stime._&etime..txt

----- end of script: sp_mrti.sql ----------