AWR Generator: SQL script for generating multiple AWR Reports
The following script is used with Oracle databases for generating multiple AWR reports between two points in time (determined by snapshots in the Automatic Workload Repository – one report per snapshot).
There are already a number of scripts out there that do a similar job, but I found that none of them satisfied my needs. Generally they had one of two problems:
- They were shell scripts, which means they weren’t always portable to all environments (Windows anyone?)
- They were SQL scripts but they created temporary objects such as directories, which immediately rules them out of the running for any production or audited environment
This SQL script does not create any objects and simply produces a second script which can be inspected and then run. The result is a complete set of AWR reports for all snapshots within the specified range. In the case of RAC systems this also means all instances.
To try and keep the look and feel recognisable, I’ve called Oracle’s own awr scripts to setup the snapshot range – the Oracle script $ORACLE_HOME/rdbms/admin/awrinput.sql is in fact the one that is used when you use the standard AWR report generation script $ORACLE_HOME/rdbms/admin/awrrpt.sql, so it ought to look familiar!
This also means you need to run the script from the actual database server rather than remotely via SQL*Net. Since potentially large numbers of AWR Reports will be generated I’m viewing this as a good thing.
Here’s the SQL (the master copy is located at my GitHub repository):
REM AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR Reports
REM
REM Creates an output SQL script which, when run, will generate all AWR Reports
REM between the specificed start and end snapshot IDs, for all instances
REM
REM For educational purposes only - no warranty is provided
REM Test thoroughly - use at your own risk
REM
set feedback off
set echo off
set verify off
set timing off
-- Set AWR_FORMAT to "text" or "html"
define AWR_FORMAT = 'text'
define DEFAULT_OUTPUT_FILENAME = 'awr-generate.sql'
define NO_ADDM = 0
-- Get values for dbid and inst_num before calling awrinput.sql
set echo off heading on
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;
column db_name heading "DB Name" new_value db_name format a12;
column dbid heading "DB Id" new_value dbid format 9999999999 just c;
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;
-- Call the Oracle common input script to setup start and end snap ids
@@?/rdbms/admin/awrinput.sql
-- Ask the user for the name of the output script
prompt
prompt Specify output script name
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt This script produces output in the form of another SQL script
prompt The output script contains the commands to generate the AWR Reports
prompt
prompt The default output file name is &DEFAULT_OUTPUT_FILENAME
prompt To accept this name, press <return> to continue, otherwise enter an alternative
prompt
set heading off
column outfile_name new_value outfile_name noprint;
select 'Using the output file name ' || nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME')
, nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') outfile_name
from sys.dual;
set linesize 800
set serverout on
set termout off
-- spool to outputfile
spool &outfile_name
-- write script header comments
prompt REM Temporary script created by awr-generator.sql
prompt REM Used to create multiple AWR reports between two snapshots
select 'REM Created by user '||user||' on '||sys_context('userenv', 'host')||' at '||to_char(sysdate, 'DD-MON-YYYY HH24:MI') from dual;
set heading on
-- Begin iterating through snapshots and generating reports
DECLARE
c_dbid CONSTANT NUMBER := :dbid;
c_inst_num CONSTANT NUMBER := :inst_num;
c_start_snap_id CONSTANT NUMBER := :bid;
c_end_snap_id CONSTANT NUMBER := :eid;
c_awr_options CONSTANT NUMBER := &&NO_ADDM;
c_report_type CONSTANT CHAR(4):= '&&AWR_FORMAT';
v_awr_reportname VARCHAR2(100);
v_report_suffix CHAR(5);
CURSOR c_snapshots IS
select inst_num, start_snap_id, end_snap_id
from (
select s.instance_number as inst_num,
s.snap_id as start_snap_id,
lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id
from dba_hist_snapshot s
where s.dbid = c_dbid
and s.snap_id >= c_start_snap_id
and s.snap_id <= c_end_snap_id
)
where end_snap_id is not null
order by inst_num, start_snap_id;
BEGIN
dbms_output.put_line('');
dbms_output.put_line('prompt Beginning AWR Generation...');
dbms_output.put_line('set heading off feedback off lines 800 pages 5000 trimspool on trimout on');
-- Determine report type (html or text)
IF c_report_type = 'html' THEN
v_report_suffix := '.html';
ELSE
v_report_suffix := '.txt';
END IF;
-- Iterate through snapshots
FOR cr_snapshot in c_snapshots
LOOP
-- Construct filename for AWR report
v_awr_reportname := 'awrrpt_'||cr_snapshot.inst_num||'_'||cr_snapshot.start_snap_id||'_'||cr_snapshot.end_snap_id||v_report_suffix;
dbms_output.put_line('prompt Creating AWR Report '||v_awr_reportname
||' for instance number '||cr_snapshot.inst_num||' snapshots '||cr_snapshot.start_snap_id||' to '||cr_snapshot.end_snap_id);
dbms_output.put_line('prompt');
-- Disable terminal output to stop AWR text appearing on screen
dbms_output.put_line('set termout off');
-- Set spool to create AWR report file
dbms_output.put_line('spool '||v_awr_reportname);
-- call the table function to generate the report
IF c_report_type = 'html' THEN
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html('
||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));');
ELSE
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text('
||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));');
END IF;
dbms_output.put_line('spool off');
-- Enable terminal output having finished generating AWR report
dbms_output.put_line('set termout on');
END LOOP;
dbms_output.put_line('set heading on feedback 6 lines 100 pages 45');
dbms_output.put_line('prompt AWR Generation Complete');
-- EXCEPTION HANDLER?
END;
/
spool off
set termout on
prompt
prompt Script written to &outfile_name - check and run in order to generate AWR reports...
prompt
--clear columns sql
undefine outfile_name
undefine AWR_FORMAT
undefine DEFAULT_OUTPUT_FILENAME
undefine NO_ADDM
undefine OUTFILE_NAME
set feedback 6 verify on lines 100 pages 45
I’ve tested this on a number of platforms – and one of my good friends at Pythian has kindly tested it on RAC system for me. If you find any issues with it, please let me know using the comments field below. If I had to hold my hands up to one glaring piece of bad coding, it’s that I haven’t put an exception handler around the PL/SQL anonymous block. I know I should, but I can’t think what to actually do in the event of an exception. Don’t hold it against me – it’s better than the classic WHEN OTHERS THEN NULL.
Here’s an example of me running it against a single-instance 11.2.0.3 system (user input is shown in red):
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 27 17:59:49 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @awr-generator.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1334550050 ORCL 1 orcl Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1334550050 1 ORCL orcl half-server4 Using 1334550050 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- orcl ORCL 1389 27 Feb 2013 00:00 1 1390 27 Feb 2013 01:00 1 1391 27 Feb 2013 02:00 1 1392 27 Feb 2013 03:00 1 1393 27 Feb 2013 04:00 1 1394 27 Feb 2013 05:00 1 1395 27 Feb 2013 06:00 1 1396 27 Feb 2013 07:00 1 1397 27 Feb 2013 08:00 1 1398 27 Feb 2013 09:00 1 1399 27 Feb 2013 10:00 1 1400 27 Feb 2013 11:00 1 1401 27 Feb 2013 12:00 1 1402 27 Feb 2013 13:00 1 1403 27 Feb 2013 14:00 1 1404 27 Feb 2013 15:00 1 1405 27 Feb 2013 16:00 1 1406 27 Feb 2013 17:00 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1400 Begin Snapshot Id specified: 1400 Enter value for end_snap: 1404 End Snapshot Id specified: 1404 Specify output script name ~~~~~~~~~~~~~~~~~~~~~~~~~~ This script produces output in the form of another SQL script The output script contains the commands to generate the AWR Reports The default output file name is awr-generate.sql To accept this name, press to continue, otherwise enter an alternative Enter value for outfile_name: Using the output file name awr-generate.sql Script written to awr-generate.sql - check and run in order to generate AWR reports... SQL> @awr-generate.sql Beginning AWR Generation... Creating AWR Report awrrpt_1_1400_1401.txt for instance number 1 snapshots 1400 to 1401 Creating AWR Report awrrpt_1_1401_1402.txt for instance number 1 snapshots 1401 to 1402 Creating AWR Report awrrpt_1_1402_1403.txt for instance number 1 snapshots 1402 to 1403 Creating AWR Report awrrpt_1_1403_1404.txt for instance number 1 snapshots 1403 to 1404 AWR Generation Complete SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
And the output:
[oracle@server4 ~]$ ls -l awrrpt* -rw-r--r--. 1 oracle oinstall 152333 Feb 27 18:00 awrrpt_1_1400_1401.txt -rw-r--r--. 1 oracle oinstall 152676 Feb 27 18:00 awrrpt_1_1401_1402.txt -rw-r--r--. 1 oracle oinstall 153511 Feb 27 18:00 awrrpt_1_1402_1403.txt -rw-r--r--. 1 oracle oinstall 148614 Feb 27 18:00 awrrpt_1_1403_1404.txt
Disclaimer
The tools and scripts found here are supplied for education purposes only and come with no warranty, either expressly or implied. Use at your own risk and please test thoroughly before use in a environment where you care about data and availability.
Neither I nor my employer accept any responsibility for damage caused through the use of these scripts and tools.
flashdba 
Why not just pull the awr snapshots and import them?
I assume you mean the Oracle AWR extract script $ORACLE_HOME/rdbms/admin/awrextr.sql ? Mainly because customers aren’t always so keen to do this. In my role I am talking to potential customers and trying to find an opportunity to have a more in-depth discussion about the benefits of moving their infrastructure to flash memory. This means I need to make the maximum impact with the minimum of effort from them. DBAs are used to generating AWR reports so asking for that sort of data tends to be no problem. Asking for an export of their automatic workload repository will be a more unusual work request and one that may meet resistance, or at least feel like it requires more effort on their behalf.
Indeed, i have a similar script but requiring a directory. So your script is really welcome !
Thanks again for sharing your Stuff 🙂
Olivier
No problem – thanks for commenting!
Beautiful
Hi
thanks for sharing
Possible improvement: you can configure amount of data in AWR reports with awr_set_report_thresholds. It’s nice to see more data than it is there by default. Unfortunately the setting is not persistent and the procedure is not even documented.
Hi Timur – I know the setting you mean, although it’s (bizarrely) not mentioned in the documentation there is a My Oracle Support note (1357637.1) which explains it.
I’m torn. On the one hand I agree with you that it would be nice to have this extra control. On the other hand, I have a hidden agenda for creating this script, which is that I have another set of shell scripts I used to process the wealth of AWR reports I receive and automatically generate CSV files, which are then used to create graphs, reports etc. I need the output files to be in the standard format otherwise the parsing scripts may not work, so I don’t necessarily want to add an option which will break my other scripts! Of course the real answer would be to ensure the other scripts can handle varying amounts of AWR data…
I would also have some concerns about making any chances to settings within the customer’s data (since one of my stated objectives with this script was not to make any changes or modifications) but as you correctly pointed out the changes made by DBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS are session-level only.
OK, understood. But then I don’t understand why do you create AWR reports if you can spool csv by yourself in the SQL script by accessing the DBA_HIST_* directly? Sounds like a hard job to me to generate reports->parse->graph.
It’s all about trust. If I (or one of the sales people I support) go to see a new customer and ask for them to run a set of custom scripts against their database that pull off lots of DBA_HIST_* data, there is a natural resistance or at least caution. Would you trust some random sales guy who wants to take your data? Often these initial meetings are with management rather than technical people, so there is also a potential … shall we say, lack of understanding… about what data is being extracted. Questions always arise about whether there is confidential data in the DBA_HIST_* tables and whether corporate security policies need to be reviewed before the request can be granted.
On the other hand, everyone has heard of an AWR report – even non-technical people have usually got a vague idea of the concept. Asking for AWR reports is not considered contentious or a potential security issue, despite the fact that us technical people all know it’s **exactly the same data**…
So the answer my friend is that extracting AWR reports and then parsing them, while not the best technical solution, is the quickest way forward and is least-likely to cause delays in the sales process. And at the end of the day, my job is to speed the sales process up by bring more clarity, not make it slower by introducing complexity.
Good old HTML format reports not feeling the love. Pretty as they may be, I hate reading them. What would be nice – a batch AWR analyzer 🙂 I know you have one.
Shhhhh don’t tell anyone or they’ll all want a copy…
Hell Yeahhhhhh !!!! I want it ! 🙂
Any chance ? 😛
small correction : v_report_suffix CHAR(5);
thx for sharing
Thanks – good catch!
— call the table function to generate the report
IF c_report_type = ‘html’ THEN
dbms_output.put_line(‘select output from table(dbms_workload_repository.awr_report_html(‘
||c_dbid||’,’||cr_snapshot.inst_num||’,’||cr_snapshot.start_snap_id||’,’||cr_snapshot.end_snap_id||’,’||c_awr_options||’));’);
ELSE
dbms_output.put_line(‘select output from table(dbms_workload_repository.awr_report_text(‘
||c_dbid||’,’||cr_snapshot.inst_num||’,’||cr_snapshot.start_snap_id||’,’||cr_snapshot.end_snap_id||’,’||c_awr_options||’));’);
END IF;
You’re absolutely right. Does it show that I never generate reports in HTML?
Thanks for bug testing and supplying the corrections…
Very, very usefull…. Thanks a lot for this script.
Lovely script.
How would I generate AWR Cluster aggregated statistics from all the instances?
Usually use:
SQL> @?/rdbms/admin/awrgrpt.sql
You should be able to to modify this pretty easily.
Just go to the lines that call dbms_workload_repository.awr_report_html and dbms_workload_repository.awr_report_text, then edit them to call dbms_workload_repository.awr_global_report_html and dbms_workload_repository.awr_global_report_html respectively.
I just tried it on my 11.2 database and it seems to work fine…
I should probably make it a parameter, or at least a variable…
One smells a revision 🙂
Great script, very useful. Thanks…
Really really Fantastic!!!
It was Awesome… script,
Now I am able to generate .html reports by using this script.
But when I click on .html file , I am unable to see proper table structural format in internet browser.
How can I get proper table format..?
I don’t remember this happening for me, but to be completely honest I never use the HTML reports, only TEXT format…
I really like it when folks come together and share ideas.
Great blog, keep it up!
Hi ,
Thanks for the script … how can i use the same for generating multuple ASH reports as well for every hour .
Thanks again …
Sameer
I don’t know for sure – right now I don’t have a test environment handy – but I’m pretty sure if you just change the calls to DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_* (where * is HTML and TEXT, i.e. change both lines) to DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_* it will work.
If it doesn’t, it will be very very close to working.
Hi, have you ever tried to generate an AWR report in PDB level? Thank you!
I confess I haven’t. If anybody has, let us know if it works?
Hi,
I am getting the following error when I am trying to run your script above:
[oravis@ebs122 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 15 11:12:20 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @awr-generator.sql
SP2-0734: unknown command beginning “EM <span c…" – rest of line ignored.
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
———– ———— ——– ————
398637095 VIS 1 VIS
SP2-0310: unable to open file "/u03/VIS/11.2.0/rdbms/admin/<span.sql"
Specify output script name
~~~~~~~~~~~~~~~~~~~~~~~~~~
This script produces output in the form of another SQL script
The output script contains the commands to generate the AWR Reports
The default output file name is awr-generate.sql
To accept this name, press to continue, otherwise enter an alternative
Enter value for outfile_name:
from sys.dual
*
ERROR at line 3:
ORA-00903: invalid table name
Script written to – check and run in order to generate AWR reports…
SQL>
Can you please guide me on what modifications I need to make to it in order to get it going?
I have no idea I’m afraid, it looks like something has gone wrong during the cut and paste:
SP2-0734: unknown command beginning “EM <span c…" – rest of line ignored.
The "<span c…" part looks like HTML. That shouldn't be in there – it isn't in the script shown on my page (unless your browser isn't displaying it properly).
Really useful Script ..could you please help in pulling the reports based on the start time and end time ..and from there generating the hourly reports which your script does within that duration..
Well when you run the script it gives a list of snapshots with their date and time, so isn’t it enough just to pick the correct values from that list? Or have I misunderstood?
It worked for me as well in a RAC environment with 4 instances, Thanx!
This is a great script!!!
Only thing I was looking is, can we give an option for interval between snapshots. For eg: begin snap is 1400 and end snap is 1408, and we want only interval as 4, so that only two reports will be generated as 1400 to 1404 and 1405 to 1408. If we give interval as 1 then it will work as normal (as it is now working).
I am not that good in p/l sql, otherwise I could have suggested primary draft. Can you please help!!!
Thanks,
Kaushal.
Thanks
I’m sorry I just don’t have time to customise the script as it already does everything I need. But you should see this as an opportunity – personally I only really learn stuff when I am forced to do it because I can’t find somebody else to do it for me. They say that necessity is the mother of invention!
HINT: Look in the PL/SQL for the cursor and fiddle with the second value in the LEAD expression:
lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id
Try changing the 1 to a 2 for example…
Fantastic! Thank you very much for sharing this script…
Thank you for sharing this!
Hi,
In case we have some snaps with interval is 30 minutes for example and I want to generate multiple reports with 60 minutes for each one. Can we do that and how to do?
Thanks,
hainvan.
You have some choices. Either way you need to fiddle with the c_snapshots cursor but you could look into changing the offset parameter of the LEAD function, or you could add an extra where clause to only filter out snapshots that were taken on the hour.
However, I am a firm believer that you should use as finer granularity as is possible, so I would urge you to consider why you wouldn’t use half-hourly snapshots when they are available?
really really awesome.
you are did a wonderful job, this script work well.
Great Thanks,
Aiglefin
Wonderful script, tx it makes life easier.
Hi,
I need the awr reports in the below format
dbname_Instid_beginhr_endhr.html.
Beginhr – Starttime
endhr – Endtime for the awr reports
How can i modify the same
You would need to edit the CURSOR statement at line 84 to include the columns you need from DBA_HIST_SNAPSHOT (BEGIN_INTERVAL_TIME and BEGIN_INTERVAL_TIME). You’d probably want to use a TO_CHAR operator to get them in a reasonable format.
Then you would modify v_awr_reportname variable at line 116 to use the format you need based on these values.
Do you have a script like this one for addm and ash reports ??
I’m afraid not
How would one automate this script where variables could be passed to it? i.e. snap start and end time. I have been asked by a consultant to set this up to run daily for our peak time. I would like to hard code 8-5pm for the previous day and I would like to pass the dbid to it as well. I want to set it up as a OEM or cron job not wanting to run it manually everyday and pass the variables myself. Any suggestions?
The simplest way would be to modify the query contained in the cursor C_SNAPAHOTS. You can use that to pull out the snapshot IDs for the day just passed. Then you can just comment out the section that asks for input variables.
Any tips on how to change the AWR generator to use awrgrpt instead so that it generates a combine RAC AWR report instead of separate one for each instance?
I’ll be honest, due to a role change, it’s been at least two years since I looked at this code or anything related to AWR reports. But I don’t remember it being particularly tricky, so you if you have a look at the code for awrgrpt.sql and find the inputs, it should be relatively simple to adapt my script to feed them…
Pingback: Favourite Blogs to Follow | Database Learners
Pingback: Daily Work Scripts | Cloud On Hand
Exactly what I was looking for! Reduced my development time!
I made changes to generate global awr reports and also to take an additional identifier to name the files (useful when we want awrs for multiple tests).
I also modified the where clause of the query that generates snap_id’s to pickup the starting range…
it was:
and mod(seqnum, c_snap_interval) = 0
Range: 657 to 667
START_SNAP_ID| END_SNAP_ID
—————|—————
658| 660
660| 662
662| 664
664| 666
Changed to:
and mod(seqnum, c_snap_interval) = (case when c_snap_interval = 1 then 0 else 1 end)
START_SNAP_ID| END_SNAP_ID
—————|—————
657| 659
659| 661
661| 663
663| 665
665| 667
Internal of 1 is a special case so added case statement.
Uday