Author: H.Tonguç Yilmaz

Page: 1 2 3

Over the past years Oracle has provided many performance gathering and reporting tools. In this article I will give some great tips on using Oracle Automatic Database Diagnostic Monitor feature manually.

Automatic Workload Repository (AWR):

Automatic Workload Repository (AWR) tool looks periodically at the system performance and stores the information found. This enables database administrators to retrieve information about workload changes and database usage patterns. The statistical data needed for accurate diagnosis of a problem is saved in the Automatic Workload Repository (AWR).

Automatic Database Diagnostic Monitor (ADDM):

The Automatic Database Diagnostic Monitor (ADDM) identifies potential performance bottlenecks by analyzing data in the Automatic Workload Repository. ADDM locates the root cause and provides recommendations for correcting each of the identified issues on regular basis. Monitoring of database by ADDM at short, regular intervals leads to database performance diagnostics. As AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event.

DB time:

DB time is the cumulative time that a database spends in processing user requests. It includes wait time and CPU time of all non-idle user sessions. DB time is displayed in the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

Manageability Monitor Process:

Manageability Monitor Process (MMON) is a background process that is used to schedule the automatic running of the ADDM. In order to gather cache of performance statistics for AWR one must ensure that the initialization parameters STATISTICS_LEVEL is set to TYPICAL or ALL. Every time the AWR collects its most recent snapshot, the ADDM is scheduled by MMON.

Viewing Automatic Database Diagnostic Monitor (ADDM) Findings:

You can use the OEM Database Control for viewing ADDM findings. OEM Database Control is the primary interface for diagnostic monitoring. You can also run the addmrpt.sql script provided by Oracle. The addmrpt.sql script is very similar to statspack or awr reports. You can also run the DBMS_ADVISOR APIs but one must have ADVISOR privilege to run the DBMS_ADVISOR package. Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The power of Oracle Advisory Utilities for predicting I/O reduction!!
   SQL FROM Clause - Do not misuse
   Secrets of Resources with AWR Reports, Great!!
   Bad DBA - Bad Practices