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
Active | alter system set statistics_level = typical; |
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 |
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 ( |
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( Set_threshold fields |
Get Threshold | set serveroutput on |
Expand_message | select dbms_server_alert.expand_message(null, 6, null, null, null, null, null) alert_msg from dual; Expand Message fields |