ADDM Performance Monitoring

ADDM is scheduled to run automatically by the MMON process on every database instance to detect problems proactively. Each time a snapshot is taken, ADDM is triggered to perform an analysis of the period corresponding to the last two snapshots. This approach proactively monitors the instance and detects bottlenecks before they become a significant problem.

The results of each ADDM analysis are stored in Automatic Workload Repository and are also accessible through Database Control.

By default, the Oracle database server automatically captures statistical information from the SGA every 60 minutes and stores it in Automatic Workload Repository (AWR) in the form of snapshots. These snapshots are stored on disk and are similar to Statspack snapshots. However, they contain more precise information than the Statspack snapshots.

Additionally, ADDM is scheduled to run automatically by the MMON process on every database instance to detect problems proactively. Each time a snapshot is taken, ADDM is triggered to perform an analysis of the period corresponding to the last two snapshots. This approach proactively monitors the instance and detects bottlenecks before they become a significant problem.

The results of each ADDM analysis are stored in Automatic Workload Repository and are also accessible through Database Control.

Note: Although ADDM analyzes Oracle database performance over the period defined by the last two snapshots, it is possible to manually invoke an ADDM analysis across any two snapshots.

Types of Problems Analyzed by ADDM:

• 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 

ADDM is enabled by default and controlled by two parameters

1. CONTROL_MANAGEMENT_PACK_ACCESS, if set to DIAGNOSTIC or DIAGNOSTIC+TUNNING 
2. STATISTICS_LEVEL if set to either TYPICAL or ALL 

ADDM can be executed in three modes

1. Database Mode
2. Instance Mode
3. Partial Mode

Below example creates an ADDM task in database analysis mode for the entire database during the time period defined by snapshots 11050 and 11056

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'ADDM for 11PM to 5AM';
  DBMS_ADDM.ANALYZE_DB(:tname,  11050, 11056);
END;

Snap ids can be taken from below SQL




SQL> select * from DBA_HIST_SNAPSHOT







To analyze a particular instance of the database



VAR tname VARCHAR2(30);
BEGIN
  :tname := 'my ADDM for 11PM to 5AM';
  DBMS_ADDM.ANALYZE_INST(:tname,11050,11056, 1);

END;





Where 1 = Instance number




Running the ADDM in Partial Mode:

VAR tname VARCHAR2(30);
BEGIN
  :tname := 'my ADDM for 7PM to 9PM';
  DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 11050, 11056);
END;


Where 1, 2, and 4 are the instance numbers during the time period defined by snapshots 11050 and 11056 snapshots




Running the ADDM Reports:






SQL> SELECT DBMS_ADDM.GET_REPORT( :tname) FROM DUAL;







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








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