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.
Overview
Reports
DBMS_ADDM.REAL_TIME_ADDM_REPORT Function
Faking a Problem
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.
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.
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;
Thanks.
NJ
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.
Overview
Reports
DBMS_ADDM.REAL_TIME_ADDM_REPORT Function
Faking a Problem
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.
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.
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;
Thanks.
NJ
