Analyzing AWR (Automated Workload Repository) is a common operation while doing a performance and tuning sessions on a database instance.

If your database contains the Oracle Database Diagnostic Pack, then by default, the database generates an AWR snapshot in an hourly interval (it of course can be changed by using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure).

Once taken, each two snapshots can be compared in an AWR report and greatly assist in the performance and tuning job.

There’re several methods of generating these AWR report, however, most of the methods are manual methods – creating one report at the time.

The following procedure will create several AWR reports in HTML format in a given directory, based on a begin/end snapshot id parameters.

Procedure’s Parameters:

• Begin_snap – the first AWR snapshot to be compared.
• End_snap – the last AWR snapshot to be compared.
• Directory – the directory in which it will generate the reports

The required begin/end snapshot values can be retrieved from the following query on DBA_HIST_SNAPSHOT:

SELECT     	SNAP_ID,
                TO_CHAR(BEGIN_INTERVAL_TIME,'DD/MM/YYYY HH24:MI') ,
                TO_CHAR(END_INTERVAL_TIME,'DD/MM/YYYY HH24:MI')</pre>
FROM DBA_HIST_SNAPSHOT 
--The following WHERE clause can be used in order to limit the snapshot details.
--WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'DD/MM/YYYY') > TO_CHAR(SYSDATE -2,'DD/MM/YYYY'); 

Once Created, the procedure can be executed as the following example:

EXEC CreateAwrReports (465, 475, ‘C:\TEMP’);

Below is the procedure’s script:

CREATE OR REPLACE PROCEDURE CreateAwrReports (begin_snap number,end_snap number, directory varchar2 )
as
/*---------------------------------------------------------------------------
 Name: CreateAwrReports Procedure
 Purpose : 	Create several AWR reported based on begin_snap and end_snap input parameters.
			The AWR reports will be created in the directory input parameter.
 Date    : 06.21.2010.
 Author  : Roni Vered
 Website : WWW.DBSNAPS.COM
 Execution :	exec CreateAwrReports (462, 472, 'c:\temp');

 Remarks : Run as privileged user
 --------------------------------------------------------------------------- */
	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;