Manually Generating AWR Report

,

One of my customers asked me to check performance on his production database server but could not allow any access to the server itself. He asked if I could generate the AWR reports from his client machine and since it’s not really trivial (or hard) I created this script.

When we want to generate the AWR report, we usually run the awrrpt.sql script. We don’t need to be on the database server for that – we just need the awrrpt script and the scripts it calls – so any database server installation will do. Once we connect to the database and run the script, it will automatically run the relevant queries and scripts and generate the report.

Underneath all the scripts there is DBMS package called DBMS_WORKLOAD_REPOSITORY which we can call ourselves. In this case, since I didn’t have the scripts, I had to use it but I can think of other cases where it make sense to use it (for example, for generating multiple reports, or automatically send reports by email).

There are two ways to generate the report. We can generate a text version or HTML version – and indeed, there are two functions for that: AWRRPT_HTML_TYPE_TABLE and AWRRPT_TEXT_TYPE_TABLE. Those function accept these parameters, so let’s see where we get the data for that:

FUNCTION AWR_REPORT_HTML RETURNS AWRRPT_HTML_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 L_DBID                         NUMBER                  IN
 L_INST_NUM                     NUMBER                  IN
 L_BID                          NUMBER                  IN
 L_EID                          NUMBER                  IN
 L_OPTIONS                      NUMBER                  IN     DEFAULT

FUNCTION AWR_REPORT_TEXT RETURNS AWRRPT_TEXT_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 L_DBID                         NUMBER                  IN
 L_INST_NUM                     NUMBER                  IN
 L_BID                          NUMBER                  IN
 L_EID                          NUMBER                  IN
 L_OPTIONS                      NUMBER                  IN     DEFAULT

In order to get the DBID, Instance number and a list of snap ids we can use (here, from the last day), we can run this query:

SELECT DBID, instance_number, snap_id, begin_interval_time, end_interval_time
  FROM dba_hist_snapshot
 where begin_interval_time > sysdate - 1
 ORDER BY snap_id

In order to generate the report as cleanly as possible we can use this script.

For HTML output:

Set heading off
Set trimspool off
Set linesize 1500
Set termout on
Set feedback off

Spool awr_from_console.htm
select output from table(dbms_workload_repository.awr_report_html(&dbid, &inst_num, &bid, &eid));
spool off;

For TEXT output:

Set heading off
Set trimspool off
Set linesize 80
Set feedback off
Set termout on

Spool awr_from_console.txt
select output from table(dbms_workload_repository.awr_report_text(&dbid, &inst_num, &bid, &eid));
spool off;

And the result will be:

SQL> Set heading off
SQL> Set trimspool off
SQL> Set linesize 80
SQL> Set termout on
SQL>
SQL> Spool awr_from_console.txt
SQL> select output from table(dbms_workload_repository.awr_report_text(&dbid, &inst_num, &bid, &eid));
Enter value for dbid: 2492639615
Enter value for inst_num: 1
Enter value for bid: 953
Enter value for eid: 954
old   1: select output from table(dbms_workload_repository.awr_report_text(&dbid, &inst_num, &bid, &eid))
new   1: select output from table(dbms_workload_repository.awr_report_text(2492639615, 1, 953, 954))

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
GHTEST        2492639615 ghtest              1 15-Dec-16 09:18 11.2.0.4.0  NO

[…awr report…]

SQL> spool off
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.