AWR reports are very essential part of performance tuning in Oracle databases. Many a times we require multiple copies of AWR reports so that we can compare it with each other.

Traditional method of generating AWR reports (@?/rdbms/admin/awrrpt.sql) just generate single report at a time. Also generating AWR reports for many hours with scheduled interval will make process very time consuming.

So here u can use below procedure which will create several AWR reports in HTML format in a given directory, based on a begin/end snapshot id parameters.

CREATE OR REPLACE PROCEDURE create_multiple_awr (begin_snap number,end_snap number, directory varchar2 )
as
 v_Instance_number v$instance.instance_number%TYPE;
 v_Instance_name v$instance.instance_name%TYPE;
 v_dbid V$database.dbid%TYPE;
 v_file UTL_FILE.file_type;
BEGIN
/* Collecting instance information: Instance_number, Instance_name and Dbid */
 SELECT instance_number, instance_name
 into v_Instance_number,v_Instance_name
 FROM gv$instance
 ORDER BY 1;
 SELECT dbid
 INTO v_dbid
 FROM v$database;
/* Creating a database directory which will point to the acual wanted report directory in the OS */
 EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY TEMP_DIR AS '''||directory||'''');
/*
 Looping on all the snapshots from the begin_snap input parameter to the end_snap input parameter
 On each snapshot pair we will create a file in the given directory which will contain the AWR report.
 We use DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML build in procedure to create the HTML report.
*/
 FOR i IN begin_snap..end_snap-1 LOOP
 BEGIN
 --Creating and Naming the file:
 v_file := UTL_FILE.fopen('TEMP_DIR','awr_' || v_Instance_name ||'_'|| v_Instance_number || '_' || i || '_' || (i+1) || '.html', 'w', 32767);
 FOR c_AWRReport IN (
 SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( v_dbid, v_Instance_number, i, i+1))
 ) LOOP
 --Writing the AWR HTML report content to the file:
 UTL_FILE.PUT_LINE(v_file, c_AWRReport.output);
 END LOOP;
 --Closing the file:
 UTL_FILE.fclose(v_file);
 END;
 END LOOP;
/* Dropping the database directory which we've created earlier. */
 EXECUTE IMMEDIATE('DROP DIRECTORY TEMP_DIR');
END;

U have to create this procedure under SYS schema.

Usage : exec create_multiple_awr (<begin_snap_id>,<end_snap_id>,'<location_to_store_awr_reports>’);

Please find below the example:

SQL> select SNAP_ID,BEGIN_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME >sysdate-1/4 order by 1;
SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
100 30-MAY-14 07.00.41.288 PM
101 30-MAY-14 08.00.47.856 PM
102 30-MAY-14 09.00.54.191 PM
103 30-MAY-14 10.00.58.109 PM
104 30-MAY-14 11.00.01.497 PM
SQL>
SQL> !pwd
/home/tibcomft/awr
SQL> !ls -l
total 0
SQL> exec create_multiple_awr (100,104,'/home/tibcomft/awr');
PL/SQL procedure successfully completed.
SQL> !ls -l
total 1596
-rw-r--r-- 1 tibcomft oinstall 412119 May 31 00:06 awr_TIBCOMFT_1_100_101.html
-rw-r--r-- 1 tibcomft oinstall 405498 May 31 00:06 awr_TIBCOMFT_1_101_102.html
-rw-r--r-- 1 tibcomft oinstall 403072 May 31 00:06 awr_TIBCOMFT_1_102_103.html
-rw-r--r-- 1 tibcomft oinstall 392942 May 31 00:07 awr_TIBCOMFT_1_103_104.html
SQL>

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath