Automatic Database Diagnostic Monitor (ADDM)

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

• Expensive SQL/Java statements
• I/O performance issues
• Locking and Concurrency issues
• Excessive parsing
• High checkpoint load
• Resource bottlenecks, including memory and CPU bottlenecks
• Undersized memory allocations
• Connection management issues, such as excessive logon/logoff activity

The report itself will contain

• Expert problem diagnosis
• Emphasis on the root cause of the problem rather than on the symptoms
• A ranking of the effects of the problems, which means you can quickly find the problem
• Recommendations ranked according to their benefit

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

• basic - this option disables the AWR
• typical (default) - activates standard level of collection
• all - same as typical but includes execution plans and timing info from the O/S

Active	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

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.tablespace_pct_full,   dbms_server_alert.operator_ge, 90,   dbms_server_alert.operator_ge, 99,   1, 1, null, dbms_server_alert.object_type_tablespace, ‘USERS2’); 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 DECLARE   vWarnOp NUMBER(10);   vWarnVal VARCHAR2(100);   vCritOp NUMBER(10);   vCritVal VARCHAR2(100);   vObsvPer NUMBER(5);   vConOcur NUMBER(5); BEGIN   dbms_server_alert.get_threshold(dbms_server_alert.tablespace_pct_full,      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); END; /
Expand_message	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