Menu

Dipesh Majumdar

Blog and Paintings

Automatic Database Diagnostic Monitor (ADDM)

July 4, 2012

What is ADDM? What does ADDM do?
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.For each of the identified issues, it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the statistics_level parameter is set to TYPICAL or ALL.

How to setup ADDM?
Parameters need to be set as shown below: CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC/DIAGNOSTICS+TUNING (The default setting is DIAGNOSTIC+TUNING.) STATISTICS_LEVEL=ALL/TYPICAL (The default setting is TYPICAL.) Setting CONTROL_MANAGEMENT_PACK_ACCESS to NONE disables ADDM. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including ADDM, and is strongly discouraged.

GOAL of ADDM?
To reduce db_time The problems reported in the ADDM are sorted on what basis? The problems reported by ADDM are sorted by the amount of DB time they are responsible for. System areas that are not responsible for a significant portion of DB time are reported as non-problem areas.

How is ADDM Report generated? How are these reports accessed/read?
Automatic Database Diagnostic Monitor runs automatically every time a new snapshot is taken by AWR (by default every hour), and the corresponding report is built comparing the last two snapshots available, so we have an ADDM report every hour.
An ADDM report can be generated from SQL*Plus using the addmrpt.sql script located in the $ORACLE_HOME/rdbms/admin directory. When run, the script lists all available snapshots and prompts the user to enter the start and end snapshot along with the report name. We can see the reports with SQL*Plus using the DBMS_ADDM.GET_REPORT function, which returns a CLOB containing the report (80-columns formatted), or we can use Oracle Enterprise Manager to view the reports generated both in automatic or manual mode.
Below script can enable one access ADDM report.
set serveroutput on
declare x varchar2(30);
y clob;
begin dbms_addm.analyze_db(x,2950,2960);
--these numbers 2950,2960 are snap_ids that one can find in dba_hist_snapshot (commented line)
select dbms_addm.get_report(x) into y from dual;
dbms_output.put_line(dbms_lob.substr(y,8000,1));
dbms_output.put_line('test');
end;

ADDM can run in which modes?
database mode, instance mode, partial mode. If it is not a rac setup, consider running in instance mode.

WHAT IS DBIO_EXPECTED?
ADDM analysis of I/O performance partially depends on a single argument, DBIO_ EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block in microseconds. Oracle Database uses the default value of 10 milliseconds, which is an appropriate value for most modern hard drives. If your hardware is significantly different, such as very old hardware or very fast RAM disks, consider using a different value.

WHERE WILL I GET THE SNAPSHOT IDS?
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY END_INTERVAL_TIME DESC;

WHAT ARE SOME OF THE ADDM RELATED DATA DICTIONARY VIEWS?
dba_hist_snapshot - Lists all valid snapshots.
dba_advisor_tasks - Basic information about existing tasks.
dba_advisor_log - Status information about existing tasks.
dba_advisor_findings - Findings identified for an existing task.
dba_advisor_recommendations - Recommendations for the problems identified by an existing task.

Go Back

Comment