Wednesday, May 9, 2012

Oracle Database 11g Automatic Database Diagnostic Monitor (ADDM)

ADDM analyzes the AWR data on regular basis and diagnose the root cause of any performance issue with providing recommendations for correcting the problems.


AWR is discussed in detail in the following post


BASH-DBA: Oracle 11g AWR (Automatic Workload Repository)


ADDM Analysis:


An ADDM analysis is performed each time an AWR snapshot is taken and the results are saved in the database. The time period analyzed by ADDM is defined by the last two snapshots (the last hour by default). The goal of the analysis is to reduce a single throughput metric called DB time. DB timecan be viewed from V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.


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
Setting up ADDM:


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 analyze any two AWR snapshots (on the same database), as long as both snapshots are still stored in the AWR (have not been purged)


Manually Running the ADDM:


Oracle suggests running the ADDM  using Oracle Enterprise Manager, however in this article I am focusing on manual ADDM execution.


ADDM can be executed in three modes


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


Running ADDM in Database Mode:


In RAC, running the ADDM in database mode will analyze all the instances in the cluster. Database mode based ADDM executed can also be done in single instance but it will be treated as instance 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;

where,


'ADDM for 7PM to 9PM' = Task Name
11050 = begin snap id
11056 =  end snap id

Snap ids can be taken from below SQL

SQL> select * from DBA_HIST_SNAPSHOT

Running the ADDM in Instance Mode:

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;


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

DBMS_ADDM.GET_REPORT(:TNAME)
--------------------------------------------------------------------------------
          ADDM Report for Task 'ADDM for 11PM to 5AM'
          -----------------
SQL> SET LONG 1000000 PAGESIZE 0;
SQL> SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
          ADDM Report for Task 'ADDM for 7PM to 9PM'
          ------------------------------------------
Analysis Period
---------------
AWR snapshot range from 11050 to 11056.
Time period starts at 08-MAY-12 11.00.42 PM
Time period ends at 09-MAY-12 05.00.50 AM


Analysis Target
---------------
Database 'TESTDB' with DB ID 1544388080.
Database version 11.2.0.2.0.
Analysis was requested for all instances, but ADDM analyzed instance testdb,
numbered 1 and hosted at testdblab.
See the "Additional Information" section for more information on the requested
instances.


Activity During the Analysis Period
-----------------------------------
Total database time was 29 seconds.
The average number of active sessions was 0.
ADDM analyzed 1 of the requested 1 instances.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
The database's maintenance windows were active during 49% of the analysis period.

ADDM Views:

DBA_ADVISOR_FINDINGS
DBA_ADDM_FINDINGS
DBA_ADVISOR_FINDING_NAMES
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_TASKS

No comments:

Post a Comment