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
Awesome post!
Thanks a lot!!!
Welcm Mayur!!
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
Thanks for visiting and appreciate your feedback. 🙂
I also removed the drop directory command because I was running it concurrently on four RAC nodes.
Very nice and very useful . Thanks !
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
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!
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!
Like!! Great article post.Really thank you! Really Cool.