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.
Glad I noticed this on google .
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.
Regards,
Adi
We are a gaggle of volunteers and starting a new scheme in our community.
Your web site provided us with helpful info to work on. You’ve done an impressive job and our whole group will likely be thankful to you.
Thanks for sharing your thoughts on website. Regards