Databases Reference
In-Depth Information
Analyzing data using Automatic Database
Diagnostic Monitor (ADDM)
In this recipe, we present the Automatic Database Diagnostic Monitor, a tool which analyzes
the data collected by AWR to diagnose the cause of a performance problem, providing advice
on how to solve the issue.
Getting ready
ADDM is enabled by default in Oracle Database 11
g
; it depends upon two configuration
parameters of the
init.ora
file,
STATISTICS_LEVEL
and
CONTROL_MANAGEMENT_PACK_
ACCESS
. The value for these parameters should be
TYPICAL
or
ALL
for the former and
DIAGNOSTIC
or
DIAGNOSTIC+TUNING
for the latter. To show the current parameter values,
we can use the following statement:
SHOW PARAMETER STATISTICS_LEVEL
SHOW PARAMETER CONTROL_MANAGEMENT_PACK_ACCESS
While to set the parameters we can use the following commands:
ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL;
ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING';
We are now ready to diagnose a problem using ADDM.
How to do it...
The following steps will demonstrate how to use ADDM:
1.
To run the ADDM in Database mode (all instances of the database will be analyzed),
we will use the following statement where the parameters 3 and 5 in these steps are
the numbers identifying the beginning and ending snapshots to be used:
VAR task_name VARCHAR2(30);
BEGIN
:task_name := 'Report for 3 to 5';
DBMS_ADDM.ANALYZE_DB (:task_name, 3, 5);
END;
