AWR Generator: SQL script for generating multiple AWR Reports

spannerThe 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, Violin Memory, accept any responsibility for damage caused through the use of these scripts and tools.

41 Responses to AWR Generator: SQL script for generating multiple AWR Reports

  1. Ronald Rood says:

    Why not just pull the awr snapshots and import them?

    • flashdba says:

      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.

  2. obtechora says:

    Indeed, i have a similar script but requiring a directory. So your script is really welcome !
    Thanks again for sharing your Stuff 🙂
    Olivier

  3. Nilesh says:

    Beautiful

  4. 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.

    • flashdba says:

      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.

        • flashdba says:

          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.

  5. Stephen Butterworth says:

    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.

  6. aksil77 says:

    small correction : v_report_suffix CHAR(5);
    thx for sharing

  7. aksil77 says:

    — 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;

    • flashdba says:

      You’re absolutely right. Does it show that I never generate reports in HTML?

      Thanks for bug testing and supplying the corrections…

  8. Franklin says:

    Very, very usefull…. Thanks a lot for this script.

  9. Ally says:

    Lovely script.

    How would I generate AWR Cluster aggregated statistics from all the instances?

    Usually use:

    SQL> @?/rdbms/admin/awrgrpt.sql

    • flashdba says:

      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…

  10. One smells a revision 🙂

  11. Simon Palmer says:

    Great script, very useful. Thanks…

  12. brunillo says:

    Really really Fantastic!!!

  13. sreenu says:

    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..?

  14. http://simpsonstappedoutcheatshack.blogspot.com says:

    I really like it when folks come together and share ideas.
    Great blog, keep it up!

  15. Sameer says:

    Hi ,
    Thanks for the script … how can i use the same for generating multuple ASH reports as well for every hour .

    Thanks again …
    Sameer

    • flashdba says:

      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.

  16. ying says:

    Hi, have you ever tried to generate an AWR report in PDB level? Thank you!

  17. Sirish says:

    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?

    • flashdba says:

      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).

  18. Joseph says:

    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..

    • flashdba says:

      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?

  19. atbinss says:

    It worked for me as well in a RAC environment with 4 instances, Thanx!

  20. Kaushal Ruparel says:

    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.

    • flashdba says:

      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…

  21. hainvan says:

    Fantastic! Thank you very much for sharing this script…

  22. Lori Loeschmann says:

    Thank you for sharing this!

  23. hainvan says:

    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.

    • flashdba says:

      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?

  24. Aiglefin says:

    really really awesome.

    you are did a wonderful job, this script work well.

    Great Thanks,
    Aiglefin

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s