Generating an AWR report for Autonomous Databases

Oracle Autonomous Database automates the lifecycle management of a database, everything from provisioning, scaling, backups and patching, but what it doesn’t do yet is fully tune your application.

You are still on the hook to make sure your app doesn’t have any concurrency bottlenecks or poorly written SQL that Auto Indexing can’t address.

So, what can you do to monitor your app while it’s running on an Autonomous Database?

The first place you can start is the Performance Hub tab on the cloud console. Here you’ll find both real-time and historical performance data in the form of Active Session History (ASH) information for the last seven days, and SQL Monitor reports for the high-load SQL. You can aggregate the ASH data several different ways including by wait class, database service, resource group or SQL ID. The same information is also available in Oracle Management Cloud (OMC), and SQL Developer Web.

But if you are trying to get a holistic view of how your app is behaving on a database, nothing beats an Automatic Workload Repository report or AWR report.

What is an AWR report?

An AWR report is generated by diffing two collections or snapshots of performance data gathered both from in-memory v$ views and the in-database workload repository or DBA_HIST tables. It contains:

• • • Wait events, which are divided into 13 wait classes to help identify performance problems
    • Time model statistics including operation times, such as the overall parse time (hard, soft, failed) or execution times for SQL and PLSQL, as well as, statistics indicating the amount of DB time associated with a process
    • SQL statements statistics
    • Active Session History statistics
    • Object usage statistics
    • Operating system statistics

With the Oracle Autonomous Database, you have two ways to generate an AWR report – download one via the Performance Hub or manually generate one.

How to Generate an AWR report from the Performance Hub

It’s incredibly easy to generate an AWR report for a time period within the last eight days by using the drop-down Report menu on the upper right-hand side of the Performance Hub screen.

Move the time picker on the Activity chart to highlight the period of time you are interested in. Then click on the Report button in the upper right-hand side of the screen and select AWR from the drop-down menu.

This will trigger a pop-up window to appear, which gives you two options. Let Oracle automatically determine the two snapshots closes to the time period you are interested in or manual specify the two timestamps you want.

Once you have made your choice click Download. The AWR for the corresponding time period will be automatically generated and downloaded to your machine.

How to Manually Generate an AWR report

The simplest way to manually generate an AWR report is to use the awrrpt.sql script Oracle provides in the $ORACLE_HOME directory. But with an Autonomous Database, you don’t have access to the $ORACLE_HOME directory on the database server. So how do you generate a report?

It’s easier than you think once you have the right pieces of information to call the functions supplied in the DBMS_WORKLOAD_REPOSITORY package.

You need to supply the database id, the instance number and the snap id you want to use for the begin snapshot and end snapshot to each function.

Below are two PL/SQL blocks to make it super easy to generate an AWR for your Autonomous Database for any time period.  The first displays a list of SNAP_ID from the current instance, while the second, creates the actual AWR report for whichever SNAP_IDs you enter.

-- Get the list of snap_ids for this database on this instance (remember its a RAC database)
 
DECLARE
 db_id NUMBER;
 inst_id NUMBER;
 
 BEGIN
   dbms_output.enable(1000000);
   SELECT dbid INTO db_id FROM v$database;
   SELECT instance_number INTO inst_id FROM v$instance;
 
   FOR v_rec IN (SELECT snap_id, begin_interval_time, end_interval_time
              FROM   dba_hist_snapshot
              WHERE  dbid = db_id
              AND    instance_number = inst_id
              ORDER BY snap_id DESC) LOOP
 
   dbms_output.put_line(' | ' ||v_rec.snap_id || ' | ' ||v_rec.begin_interval_time  || ' | ' ||v_rec.end_interval_time);
   END LOOP;
END;
/
-- Generate the AWR report for the SNAP_IDs provided by the user
 
DECLARE
  db_id NUMBER;
  inst_id NUMBER;
  start_id NUMBER;
  end_id NUMBER;
 
 BEGIN
   dbms_output.enable(1000000);
   SELECT dbid INTO db_id FROM v$database;
   SELECT instance_number INTO inst_id FROM v$instance;
   start_id := &enter_start_id;
   end_id := &enter_end_id;
 
   FOR v_awr IN (SELECT output
              FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(db_id,inst_id,start_id,end_id))) LOOP
 
   dbms_output.put_line(v_awr.output);
   END LOOP;
END;
/

If you are actively tuning your system and just want to generate the latest AWR report for your Autonomous Database on the local instance, you can use the supplied report in SQL Developer, called Last AWR Report.

Generating AWR report in SQL Developer

If you haven’t used reports in SQL Developer before, be sure to check out Jeff Smith‘s blog post on how to use them.

To get the Reports window to appear, click on the View menu and select Reports from the drop-down list.

In the Reports window, click on Data Dictionary Reports, followed by ASH and AWR. You should now see a report called Last AWR Report which will automatically generate a text-based AWR report for the last hour.

You can also copy the Last AWR Report to the User Defined Reports folder and modify it to suit your own needs using the SQL Developer copy and paste functionality.

To edit the report, right-click on the report name and select edit, which will open the window with the report text, as shown below. You may also notice the code looks very similar to what I showed above and it is as I used this code as the basis for my example!

Many thanks to Jeff Smith for showing me how to use and edit the supplied reports in SQL Developer!