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.
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
The easiest way to 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.
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!