Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g

Overview

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.

The ADDM analysis includes:

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

The findings (problems) are listed in order of potential impact on database performance, along with recommendations to resolve the issue and the symptoms which lead to it's discovery.

An example from my test instance is:

FINDING 1: 59% impact (944 seconds)

-----------------------------------

The buffer cache was undersized causing significant additional read I/O.

   RECOMMENDATION 1: DB Configuration, 59% benefit (944 seconds)

      ACTION: Increase SGA target size by increasing the value of parameter

         "sga_target" by 28 M.

   SYMPTOMS THAT LED TO THE FINDING:

      Wait class "User I/O" was consuming significant database time. (83%

      impact [1336 seconds])

The recommendations may include:

• Hardware changes
• Database configuration changes
• Schema changes
• Application changes
• Using other advisors

The analysis of I/O performance is affected by the DBIO_EXPECTED parameter which should be set to the average time (in microseconds) it takes to read a single database block from disk. Typical values range from 5000 to 20000 microsoconds.

The parameter can be set using:

EXECUTE DBMS_ADVISOR.set_default_task_parameter('ADDM', 'DBIO_EXPECTED', 8000);

Enterprise Manager

The obvious place to start viewing ADDM reports is Enterprise Manager. The "Performance Analysis" section on the "Home" page is a list of the top five findings from the last ADDM analysis task.

Specific reports can be produced by clicking on the "Advisor Central" link, then the "ADDM" link. The resulting page allows you to select a start and end snapshot, create an ADDM task and display the resulting report by clicking on a few links.

addmrpt.sql Script

The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows:

-- UNIX

@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql

-- Windows

@d:\oracle\product\10.1.0\db_1\rdbms\admin\addmrpt.sql

It then lists all available snapshots and prompts you to enter the start and end snapshot along with the report name.

DBMS_ADVISOR

The DBMS_ADVISOR package can be used to create and execute any advisor tasks, including ADDM tasks. The following example shows how it is used to create, execute and display a typical ADDM report:

BEGIN

  -- Create an ADDM task.

  DBMS_ADVISOR.create_task (

    advisor_name      => 'ADDM',

    task_name         => '970_1032_AWR_SNAPSHOT',

    task_desc         => 'Advisor for snapshots 970 to 1032.');

  -- Set the start and end snapshots.

  DBMS_ADVISOR.set_task_parameter (

    task_name => '970_1032_AWR_SNAPSHOT',

    parameter => 'START_SNAPSHOT',

    value     => 970);

  DBMS_ADVISOR.set_task_parameter (

    task_name => '970_1032_AWR_SNAPSHOT',

    parameter => 'END_SNAPSHOT',

    value     => 1032);

  -- Execute the task.

  DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');

END;

/

-- Display the report.

SET LONG 100000

SET PAGESIZE 50000

SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report

FROM   dual;

SET PAGESIZE 24

The value for the SET LONG command should be adjusted to allow the whole report to be displayed.

The relevant AWR snapshots can be identified using the DBA_HIST_SNAPSHOT view.

Related Views

The following views can be used to display the ADDM output without using Enterprise Manager or the GET_TASK_REPORT function:

• 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.

Source: Link