Dear Readers,

Yesterday one of my friend pinged with the requirement of generating muliple AWR reports for every 1 hour using single command. This particular database has SNAP_INTERVAL set to every 15 minutes.

He had already referred one of my old post given below, which helps us generating muliple AWR reports but it does generate awr report for every subsequent snaps.

Generating multiple AWR reports using single command: Part 1

So he asked me whether we can modify above procedure to generate AWR report for every 4 snaps.

Below procedure can be used for this requirement:

Code:

CREATE OR REPLACE PROCEDURE SYS.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;
i NUMBER (5);
start_step NUMBER (5);
end_step NUMBER (5);
BEGIN
/* Collecting instance information: Instance_number, Instance_name and Dbid */
SELECT instance_number, instance_name
INTO v_Instance_number, v_Instance_name
FROM v$instance
ORDER BY 1;
SELECT dbid INTO v_dbid FROM v$database;
/* Creating a database directory which will point to the actual 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.
*/
i := 2;
start_step := 0;
end_step := 2;
BEGIN
LOOP
v_file :=
UTL_FILE.fopen ('TEMP_DIR','awr_'|| v_Instance_name|| '_'|| v_Instance_number|| '_'|| (( begin_snap ) + ( i * start_step ))
|| '_'
|| (( begin_snap )+ ( i * end_step ))
|| '.html',
'w',
32767);
FOR c_AWRReport IN (SELECT output
FROM TABLE (
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (
v_dbid,
v_Instance_number,
(( begin_snap ) +( i * start_step )),
(( begin_snap ) +( i * end_step )))))
LOOP
UTL_FILE.PUT_LINE (v_file, c_AWRReport.output);
END LOOP;
EXIT WHEN ( ( begin_snap )+( i * end_step )) >= end_snap;
start_step := start_step + 2;
end_step := end_step + 2;
--Closing the file:
UTL_FILE.fclose (v_file);
END LOOP;
END;
/* Dropping the database directory which we've created earlier. */
EXECUTE IMMEDIATE ('DROP DIRECTORY TEMP_DIR');
END;
/

You have to create this procedure under SYS schema.

Usage:

exec create_multiple_awr (begin_snap,end_snap,'<path>’);

Example:

exec create_multiple_awr (15700,15720,’/home/oracle’);

You can also download code from below link:

create_multiple_awr

Hope u will find this post very useful.

Cheers

Regards,
Adityanath