8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Real-Time Automatic Database Diagnostics Monitor (ADDM) in Oracle Database 12c Release 1 (12.1)
The Automatic Database Diagnostics Monitor (ADDM) has been available since Oracle 10g as part of the Diagnostics and Tuning option in Enterprise Edition. ADDM analyzes the contents of the Automatic Workload Repository (AWR) to identify potential performance bottlenecks and attempts to perform root cause analysis of them. Since the AWR snapshots are taken on an hourly basis by default, this has meant ADDM was limited to retrospective analysis on an hourly basis.
Enterprise Manager Cloud Control has included functionality to perform real-time ADDM reports for some time.
In addition to the existing ADDM functionality, Oracle Database 12c introduced Real-Time ADDM, which identifies potential performance problems as they happen, analyzes them and stores reports, allowing up-to-the-minute diagnosis of performance problems.
This functionality requires Enterprise Edition and the Diagnostics and Tuning option. Make sure you have the correct licensing before using this functionality.
Related articles.
- Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g
- Automatic Database Diagnostic Monitor (ADDM) Enhancements in Oracle Database 11g Release 1
- Automatic Database Diagnostic Monitor (ADDM) Compare Period in Oracle Database 12c Release 1 (12.1)
Overview
The manageability monitor (MMON) process kicks in every 3 seconds and checks the performance statistics that are currently in memory. If it notices any potential performance issues, it triggers a real-time ADDM analysis run, creates a report and stores it in the Automatic Workload Repository (AWR). The stored reports can be seen in the DBA_HIST_REPORTS
view.
The documentation lists the potential triggers for a real-time analysis here.
Reports
Real-time ADDM reports are generated using the "rtaddmrpt.sql" and "rtaddmrpti.sql" scripts in the "$ORACLE_HOME/rdbms/admin" directory.
The "rtaddmrpt.sql" script assumes the report is for the current database and instance, and uses a reporting period of the -60 mins to current time (SYSDATE). This sounds a little like a conventional ADDM report, but the information is sourced from the DBA_HIST_REPORTS
view, not the conventional ADDM analysis. An example of running the "rtaddmrpt.sql" script is shown below. Only the report_id
and report_name
required user input.
SQL> @rtaddmrpt Current Database ~~~~~~~~~~~~~~~~ 845557242 Instances in this Report reposistory ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 845557242 1 Default to current database Using database id: 845557242 Enter begin time for report: -- Valid input formats: -- To specify absolute begin time: -- [MM/DD[/YY]] HH24:MI[:SS] -- Examples: 02/23/03 14:30:15 -- 02/23 14:30:15 -- 14:30:15 -- 14:30 -- To specify relative begin time: (start with '-' sign) -- -[HH24:]MI -- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins) -- -25 (SYSDATE - 25 Mins) Default to -60 mins Report begin time specified: Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Press Enter to analyze till current time Report duration specified: Using 20/07/2015 09:39:41 as report begin time Using 20/07/2015 10:39:41 as report end time Report ids in this workload repository. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 845557242 1922 20/07/2015 10:34:29 High Load 20.16 Select a report id from the list. If the report id list is empty, please select a different begin time and end time. Enter value for report_id: 1922 Report id specified : 1922 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is rtaddmrpt_0720_1039.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: /tmp/rtaddmrpt_0720_1039.html Using the report name /tmp/rtaddmrpt_0720_1039.html ... Removed HTML Output ... Report written to /tmp/rtaddmrpt_0720_1039.html SQL>
You can see the example output here.
- rtaddmrpt_0720_1039.html (Your browser may block this as an "unsafe script")
The "rtaddmrpti.sql" script allows you to manually select the database and reporting period. The items in bold required user input.
SQL> @rtaddmrpti Instances in this Report reposistory ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Db Id Inst Num ---------- -------- 845557242 1 Default to current database Enter value for dbid: Using database id: 845557242 Enter begin time for report: -- Valid input formats: -- To specify absolute begin time: -- [MM/DD[/YY]] HH24:MI[:SS] -- Examples: 02/23/03 14:30:15 -- 02/23 14:30:15 -- 14:30:15 -- 14:30 -- To specify relative begin time: (start with '-' sign) -- -[HH24:]MI -- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins) -- -25 (SYSDATE - 25 Mins) Default to -60 mins Enter value for begin_time: -5 Report begin time specified: -5 Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Press Enter to analyze till current time Enter value for duration: Report duration specified: Using 20/07/2015 10:29:46 as report begin time Using 20/07/2015 10:34:47 as report end time Report ids in this workload repository. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DBID REPORT_ID TIME trigger_cause impact ---------- --------- -------------------- ------------------------- ---------- 845557242 1922 20/07/2015 10:34:29 High Load 20.16 Select a report id from the list. If the report id list is empty, please select a different begin time and end time. Enter value for report_id: 1922 Report id specified : 1922 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is rtaddmrpt_0720_1034.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: /tmp/rtaddmrpt_0720_1034.html Using the report name /tmp/rtaddmrpt_0720_1034.html ... Removed HTML Output ... Report written to /tmp/rtaddmrpt_0720_1034.html SQL>
You can see the example output here.
- rtaddmrpt_0720_1034.html (Your browser may block this as an "unsafe script")
DBMS_ADDM.REAL_TIME_ADDM_REPORT Function
The DBMS_ADDM
package contains a function called REAL_TIME_ADDM_REPORT
, which looks promising at first glance.
"This function produces a real-time ADDM report for ADDM-related activity for the last five minutes..."
Unfortunately, it doesn't return a formatted real-time ADDM report, just some data in XML format. You can see the output using the following query.
SET LONG 1000000 LONGCHUNKSIZE 1000000 SET LINESIZE 1000 PAGESIZE 0 SET TRIM ON TRIMSPOOL ON SET ECHO OFF FEEDBACK OFF SELECT DBMS_ADDM.real_time_addm_report FROM dual;
Faking a Problem
The reporting scripts described above require records in the DBA_HIST_REPORTS
view for the analysis period, or they result in the following error.
declare * ERROR at line 1: ORA-20000: No valid reports found in the specified time range. Please specify a different begin and end time ORA-06512: at line 11
If you are looking at a small test system, you may not have any performance problems to monitor, so you will need to fake some. I did this using Swingbench by running the Order Entry workload and locking the SOE.ORDERS
table for a few seconds.
CONN sys@pdb1 AS SYSDBA LOCK TABLE soe.orders IN EXCLUSIVE MODE; -- Wait a while. ROLLBACK;
For more information see:
- Real-Time ADDM Analysis
- DBMS_ADDM
- Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g
- Automatic Database Diagnostic Monitor (ADDM) Enhancements in Oracle Database 11g Release 1
- Automatic Database Diagnostic Monitor (ADDM) Compare Period in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...