Generate Multiple AWR Reports Quickly

Occasionally there is a need to generate multiple AWR reports for database analysis. In my case, a storage vendor will use a tool to extract data from all time periods from the AWR reports to find IO related specific information. Here is how I generated these reports.

set serveroutput on
set lines 300;
spool awr_all.sql
declare
cursor c is
select to_char(s.startup_time,’dd Mon “at” HH24:mi:ss’) instart_fmt,
di.instance_name inst_name, di.instance_number instance_number,
di.db_name db_name, di.dbid dbid,
lag (s.snap_id,1,0) over (partition by di.instance_number
order by s.snap_id) begin_snap_id,
s.snap_id end_snap_id, to_char(s.begin_interval_time,’yyyymmddhh24mi’) beginsnapdat,
to_char(s.end_interval_time,’yyyymmddhh24mi’) endsnapdat, s.snap_level lvl
from dba_hist_snapshot s, dba_hist_database_instance di, gv$instance i, v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -30)
order by di.db_name, i.instance_name, s.snap_id;
begin
for c1 in c loop
if c1.begin_snap_id > 0 then
dbms_output.put_line(‘spool ‘||c1.inst_name||’_’
||c1.begin_snap_id||’_’||c1.end_snap_id||’_’||c1.beginsnapdat||’_’||c1.endsnapdat||’.txt’);
dbms_output.put_line(‘select output from table(dbms_workload_repository.awr_report_text( ‘||c1.dbid||’,’||
c1.instance_number||’,’||c1.begin_snap_id||’,’||c1.end_snap_id||’,0 ));’);
dbms_output.put_line(‘spool off’);
dbms_output.put_line(‘execute dbms_lock.sleep(15);’);
end if;
end loop;
end;
/

spool off;
set heading off
set pages 50000
set linesize 1500
set trimspool on
set trimout on
set term off
set verify off;
set feedback off;
@awr_all.sql
exit

Leave a Reply

Your email address will not be published.