Automatic Database Diagnostic Monitor (ADDM) automatically detects and reports performance problems with the database. The results are displayed as ADDM findings on the Database Home page in Oracle Enterprise Manager Cloud Control (Cloud Control). Reviewing the ADDM findings enables you to quickly identify the performance problems that require your attention.
Each ADDM finding provides a list of recommendations for reducing the impact of the performance problem. You should review ADDM findings and implement the recommendations every day as part of regular database maintenance. Even when the database is operating at an optimal performance level, you should continue to use ADDM to monitor database performance on an ongoing basis.
3.1 Overview of Automatic Database Diagnostic Monitor(ADDM)
ADDM is diagnostic software built into Oracle Database. ADDM examines and analyzes data captured in Automatic Workload Repository (AWR) to determine possible database performance problems.
ADDM then does the following:
- Locates the root causes of the performance problems
- Provides recommendations for correcting them
- Quantifies the expected benefits
- Identifies areas where no action is necessary.
3.1.1 ADDM Analysis
An ADDM analysis is performed after each AWR snapshot (every hour by default), and the results are saved in the database. You can then view the results using Cloud Control.
ADDM analysis identifying symptoms and then refining the analysis to reach the root causes of performance problems. ADDM uses the DB time statistic to identify performance problems. Database time (DB) time is the cumulative time spent by the database in processing user requests, including both the wait time and CPU time of all user sessions that are not idle.
The goal of database performance tuning is to reduce the DB time of the system for a given workload.
3.1.2 ADDM Recommendations
In addition to diagnosing performance problems,ADDM recommends multiple solutions from which you can choose.ADDM recommendations include the following:
- Hardware changes (Adding CPUs or changing the I/O subsystem configuration)
- Database configuration (Changing initialization parameter settings)
- Schema changes (Hash partitioning a table or index, or using automatic segment space management (ASSM))
- Application changes (Using the cache option for sequences or using bind variables)
- Using other advisors (Running SQL Tuning Advisor on high-load SQL statements or running the Segment Advisor on hot objects)
ADDM benefits apply beyond production systems. Even on development and test systems, ADDM can provide an early warning of potential performance problems.
Performance tuning is an iterative process. Fixing one problem can cause a bottleneck to shift to another part of the system. Even with the benefit of the ADDM analysis, it can take multiple tuning cycles to reach a desirable level of performance.
3.2 Configuring Automatic Database Diagnostic Monitor
3.2.1 Setting Initialization Parameters to Enable ADDM
Automatic database diagnostic monitoring is enabled by default and is controlled by the
CONTROL_MANAGEMENT_PACK_ACCESS
and the
STATISTICS_LEVEL
initialization parameters.
Set
CONTROL_MANAGEMENT_PACK_ACCESS
to
DIAGNOSTIC+TUNING
(default) or
DIAGNOSTIC
to enable automatic database diagnostic monitoring. Setting
CONTROL_MANAGEMENT_PACK_ACCESS
to
NONE
disables many Oracle Database features, including ADDM, and is strongly discouraged.
Set
STATISTICS_LEVEL
to
TYPICAL
(default) or
ALL
to enable automatic database diagnostic monitoring. Setting
STATISTICS_LEVEL
to
BASIC
disables many Oracle Database features, including ADDM, and is strongly discouraged.
3.2.2 Setting the DBIO_EXPECTED Parameter
To determine the correct setting for the
DBIO_EXPECTED
initialization parameter:
- Measure the average read time of a single database block for your hardware.
This measurement must be taken for random I/O, which includes seek time if you use standard hard drives. Typical values for hard drives are between 5000 and 20000 microseconds.
- Set the value one time for all subsequent ADDM executions.
if the measured value is 8000 microseconds, then execute the following PL/SQL code as the
SYS
user:
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
'ADDM', 'DBIO_EXPECTED', 8000);
3.2.3 Managing AWR Snapshots
By default, the Automatic Workload Repository (AWR) generates snapshots of performance data once every hour, and retains the statistics in the workload repository for 8 days. You can change the default values for both the snapshot interval and the retention period.
Oracle recommends that you adjust the AWR retention period to at least one month. You can also extend the period to one business cycle so you can compare data across time frames such as the close of the fiscal quarter. You can also create AWR baselines to retain snapshots indefinitely for important time periods.
The data in the snapshot interval is analyzed by ADDM. ADDM compares the differences between snapshots to determine which SQL statements to capture, based on the effect on the system load. The ADDM analysis shows the number of SQL statements that need to be captured over time.
3.2.3.1 Creating Snapshots
Manually creating snapshots is usually not necessary because AWR generates snapshots of the performance data once every hour by default. In some cases, however, it may be necessary to manually create snapshots to capture different durations of activity, such as when you want to compare performance data over a shorter period than the snapshot interval.
3.2.3.2 Modifying Snapshot Settings
By default, AWR generates snapshots of performance data once every hour. You can modify the default values of both the interval between snapshots and their retention period.
3.3 Reviewing the Automatic Database Diagnostic Monitor Analysis
By default, ADDM runs every hour to analyze snapshots taken by AWR during that period. If the database finds performance problems, then it displays the results of the analysis under Diagnostics in the Summary section on the Database Home page.
3.4 Interpretation of Automatic Database Diagnostic Monitor Findings
The ADDM analysis results are represented as a set of findings. Each ADDM finding belongs to one of three types:
-
Problem
Findings that describe the root cause of a database performance issue
-
Symptom
Findings that contain information that often leads to one or more problem findings
-
Information
Findings that are used to report areas of the system that do not have a performance impact
Each problem finding is quantified with an estimate of the portion of DB time that resulted from the performance problem.
3.5 Implementing Automatic Database Diagnostic Monitor Recommendations
This section describes how to implement ADDM recommendations. ADDM findings are displayed in the Automatic Database Diagnostic Monitor (ADDM) page under ADDM Performance Analysis.
3.6 Viewing Snapshot Statistics
You can view the data contained in snapshots taken by AWR using Cloud Control. Typically, it is not necessary to review snapshot data because it primarily contains raw statistics. Instead, rely on ADDM, which analyzes statistics to identify performance problems. Snapshot statistics are intended primarily for advanced users, such as DBAs accustomed to using Statspack for performance analysis.