The The Automated Database Diagnostic Monitoring (ADDM) Alerts

The The Automated Database Diagnostic Monitoring (ADDM) Alerts

Rating:

There are two methods of database maintenance and performance maintenancemonitoring, which are as followsthe following:

1. Reactive method.


2. Proactive method.

Oracle 10g has provided the new feature of proactive method of database maintenance and performance monitoring. With the release of Oracle 10g, has come the proactive method of Database maintenance and Performance monitoring. Oracle 10g has introduced two background processes, which are Memory Monitor (MMON) and Memory Monitor Lite (MMNL). These two processes work together to fetch data from the System Global Area. The MMON collects data from data dictionary views, and optimizer, and data dictionary views. The MMON stores the resultant data in the database. Theisse data areis stored in tables called Automatic Workload Repository (AWR).



Now, tThe Automated Database Diagnostic Monitoring (ADDM) feature of Oracle 10g analyzes the statistics gathered by the AWR. This is called AWR snapshot. Then, it compares the latest snapshot with the previous AWR snapshots and easily detects the errors that have occurred between the current and the previous snapshots such as lock contention, resource-intensive SQL or PL/SQL, CPU and I/O bottlenecks etc. and suggests corrective measures for the same. The purpose of the ADDM is to lessen the database (DB) time. DB time is the total time for which spent by the database is in processing users requests. running since the instance startup.

DB time = CPU time + Wait time

WhereHere, CPU stands for Central Processing Unit. The CPU time is the net time for which the CPU is actually executing the instructions. This methodDatabase time includes the total time that the database users have spent either working or remaining idle, waiting for CPU, I/O or other resources.

Note: A high DB time points to an increasing use of server resources as well as waits for the server resources. Therefore, the DB time should be as low as possible.

So, tThis method of analyzing database performance has proved to be better than the old method. The results of the ADDM are stored in the AWR. A study of the AWR suggests the loopholes in the functioning of the database. The results can be used as a scale for future reference as well as to rectify the current malfunctioning. And, all this is done semi-automatically or automatically. It is much easier to analyze the ADDM results through the Enterprise Manager (EM) database control. Oracle 10g provides utilities in EM Database Control to study the various recommendations and information gathered by ADDM.

ADDM monitors and makes recommendations on segments, makes suggestions on SQL, undo activity, memory, time to recover, etc. Apart from that, the monitoring of the database, CPU utilization, I/O utilization, space and security management are also managed by ADDM alerts. The ADDM alerts are an essential and integral part of the ADDM architecture. The ADDM alerts on occur when management or performance issues are detected. ADDM alerts can also be raised when the CPU usage exceeds a certain limit. This is called Baseline metric. Baseline metrices are gathered during normal database activityIf configured, then the ADDM can start taking corrective actions as well. It can also mail the user if an email address is given. An alert is triggered whenever a metric or a specified database threshold is crossed. AWR stores the statistical performance measured in matrices. Some of the alerts are given below:

1. Snapshot Too Old.: Alerts whenever the ORA-01555 error message takes place.
2. Tablespace Space Usage:. Alerts when a tablespace’s free space falls below 15 percent and again when it falls below 3 percent.
3. Resumable Session Suspended:. Alerts when an operation, which can be resumed, goes into suspended state.
4. Recovery area low on free space:. When the flash Recovery area is low on free space, it alerts the user.

There are a number of other alerts as well, which can be used by configuring them. If configured, then the ADDM can start taking corrective actions. It can also mail the user if an email address is given. An alert is triggered whenever a metric or a specified database threshold is crossed. The AWR stores the statistical performance measured in the matrices.
Moreover, a Alerts can be classified into two the following two categories based on the level of criticality:

1. Warning.


2. Critical.

For example, archiving destination can have two alerts.: Oone can be set up at, say, when the archive destination is 75% full, resulting in a message displayed on the EM database control main windowscreen and another at critical threshold in which one can receive emails when the arching destination is 95% full. In thise manner, the worsening of the issue can be monitored.

ADDM alerts can also be raised when the CPU usage exceeds a certain limit. This is called Baseline metric. Baseline metrices are gathered during normal database activity.

Rating: