Generating multiple AWR reports using single command

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

Categories: Scripts

Tagged as:

14 replies

  1. I had to change gv$instance to v$instance because I got the error below. Other than that it worked great for me.
    Thanks a lot!

    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at “SYS.CREATE_MULTIPLE_AWR”, line 9

  2. You really make it seem so easy with your presentation but I find this matter to be actually something which I think I would never understand. It seems too complicated and extremely broad for me. I am looking forward for your next post, I will try to get the hang of it!

  3. fantastic post, very informative. I wonder why the other experts of this sector do not notice this. You must continue your writing. I’m sure, you’ve a huge readers’ base already!

  4. Hello there! Do you use Twitter? I’d like to follow you if that would be okay. I’m undoubtedly enjoying your blog and look forward to new posts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s