Automatic Database Diagnostic Monitor (ADDM)

Oracle now provides automatic performance tuning capabilities, the heart of this function is the new statistics collection facility the automatic workload repository (AWR), which automatically collects and stores statistical data in the sysaux tablespace. ADDM ranks both the problems and its recommendations according to the crucial DB time statistic.AWR collects new performance statistics in the form of hourly snapshots (MMON processes the AWR request) and saves these to the sysaux tablespace, it is a snapshot shot of a single point in time. Every time AWR runs ADDM will automatically does a top-down system analysis and reports its findings on the database control home page.

See AWR on how to setup and configure it.

The purpose of ADDM is to reduce a key database metric called DB Time which is the total time (in microseconds) the database spends actually processing users requests. DB time includes the total amount of time spent on actual database calls (at the user level) and ignores time spent on background process. ADDM will only report on processes that contribute excessive DB time.

ADDM will report on the following

The report itself will contain

Configuring ADDM

To active the AWR change the system parameter statistics_level to one of three values


alter system set statistics_level = typical;
alter system set statistics_level = all;

De-active alter system set statistics_level = basic;
Display show parameter statistics_level;

To change the snapshot interval and how many days the snapshots are kept you use the package dbms_workload_repository or Enterprise Manager

Snapshot configuration
Change snapshotting values

exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200);

interval = minutes
retention = seconds

Display values select * from dba_hist_wr_control;
Snapshot Management
Create a snapshot exec dbms_workload_repository.create_snapshot;
Delete snapshots exec dbms_workload_repository.drop_snapshot_range (low_snap_id=>1077, high_snap_id=>1078);
Display snapshots select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;

ADDM views

There are a number of views that should used when involving the ADDM

DBA_ADVISOR_FINDINGS finding identified by ADDM
DBA_ADVISOR_OBJECTS describe the objects that are referenced in findings
DBA_ADVISOR_RECOMMENDATIONS describe the recommendations based on ADDM findings
DBA_ADVISOR_RATIONALE describe the rationale behind each ADDM finding
DBA_SCHEDULER_JOBS list the gather_stats_job which runs the automatic snapshotting
dba_hist_baseline display baselines (see below)
dba_hist_snapshot display snapshots that are available
dba_hist_wr_control display the snapshot current settings
v$sys_time_model provides the accumulated time statistics for various operations in the entire database (in microseconds)
v$sess_time_model provides the accumulated time statistics for various operations in the session (in microseconds)

ADDM Report

To run ADDM report you can use the following operating system scripts or use Enterprise Manager.

addmrpt.sql the script will ask for begin snapshot and end snapshot plus if you want the report in text or html, the scriopt can be found in $ORACLE_HOME/RDBMS/ADMIN

ADDM baselines

The main purpose of a baseline is to preserve typical runtime statistics in the AWR repository, allowing you to run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots in the AWR.

Create baseline

exec dbms_workload_repository.create_baseline (
  start_snap_id=> 1007,
  end_snap_id=> 1009,
  baseline_name=>'EOM baseline'

Remove baseline exec dbms_workload_repository.drop_baseline ( baseline_name => 'EOM baseline');
Display baselines select baseline_id, baseline_name, start_snap_id, end_snap_id from dba_hist_baseline;

Server Alerts

Server generated alerts are controlled by the MMON (manageability monitor) which is assisted by the MMNL (manageability monitor light). The metrics are gathered and thresholds checked once every minute. There are two thresholds warning and critical. There are over 100 alerts, MMON compares the metrics to the configured threshold and will write a message to the ALERT_QUEUE (in the AWR - sysaux tablespace). Processes can subscribe (only if you need to write your own alert handler) to this queue (and other queues) you pass on information from one process to another. The Enterprise manager daemon will action the ALERT_QUEUE and can places alerts messages in the GUI or can even send an email, you can also write your own alert handler. Remember the metrics are collected from the SYSAUX tablespace not the v$ views. There is no connection to the alert log and the server alert system.

Alert Types select internal_metric_name from v$alert_types;
Current alerts select reason, object_type type, object_name name from dba_outstanding_alerts;
Old Alerts select reason, object_type type, object_name name from dba_alert_history;
Useful Views
dba_outstanding_alerts current alert waiting for resolution
dba_alert_history alerts that have been cleared
dba_thresholds threshold settings defined for the instance
v$alert_types alert type and group information
v$metric system-level metric values in memory
v$metricname names, identifiers and other info about system metrics
v$metric_history historical system-level metric values in memory

Stateless or non thresholds alerts are stored in dba_alert_history because they are solved as soon as they occur. Remember that the parameter statistics_level must be set to typical or all in order to obtain alerts. Shutdown commands are only recorded in the alert log file.

The dbms_server_alert package contains procedures to change the alerts

Change threshold


exec dbms_server_alert.set_threshold(
  dbms_server_alert.operator_ge, 90,
  dbms_server_alert.operator_ge, 99,
  1, 1, null, dbms_server_alert.object_type_tablespace,

Set_threshold fields
Metrics_id - the name of the metric
Warning_operator - the comparison operator to compare values
Warning_value - warning threshold
Critical_operator - comparison operator for comparing current value to the warning threshold
Critical_value - critical threshold
Observation_period - timer period at which metrics are computed against the threshold
Consecutive_occurences - how many times the value exceeds the threshold before an alert is raised
Instance_name - the instance that the threshold is applied
Object_type - object type i.e tablespace, session, service, etc
Object_name - name of the object

Get Threshold

set serveroutput on
  vWarnOp NUMBER(10);
  vWarnVal VARCHAR2(100);
  vCritOp NUMBER(10);
  vCritVal VARCHAR2(100);
  vObsvPer NUMBER(5);
  vConOcur NUMBER(5);
     vWarnOp, vWarnVal, vCritOp, vCritVal, vObsvPer, vConOcur, NULL,
  dbms_server_alert.object_type_tablespace, 'TEST');
  dbms_output.put_line('WarnOp: ' || TO_CHAR(vWarnOp));
  dbms_output.put_line('WarnVal: ' || vWarnVal);
  dbms_output.put_line('CritOp: ' || TO_CHAR(vCritOp));
  dbms_output.put_line('CritVal: ' || vCritVal);
  dbms_output.put_line('Observation: ' || vObsvper);
  dbms_output.put_line('Occurences: ' || vConOcur);


select dbms_server_alert.expand_message(null, 6, null, null, null, null, null) alert_msg from dual;

Expand Message fields
User_language - the current sessions language
Message_id - alert message ID
Argument_1 - 1 st argument
Argument_2 - 2nd argument
Argument_3 - 3rd argument
Argument_4 - 4th argument
Argument_5 - 5th argument