ss

Oracle Tips by Burleson

Automatic Database Diagnostic Monitor

As indicated by its name, the goal of the Automatic Database Diagnostic Monitor (ADDM) is to automatically monitor and diagnose database problems. The ADDM monitors the data in the AWR and makes tuning recommendations in areas like:

CPU Utilization
Connection management
Parsing activity
I/O operations

ADDM information is used extensively in the Enterprise Manager Database Diagnostic Pack which provides a graphical front-end that contains information about database performance and recommendations for improvements.

Additionally, ADDM will generate recommendations about sizing certain memory structures. The risks and rewards of using the recommendations generated by the ADDM are presented in later chapters of this book.

The goal of ADDM is to calculate a single throughput metric called DB time, which is the total time spent by the database server in processing user requests. DB time includes the wait time and CPU time of all non-idle user sessions. As presented in a previous chapter in this book, the metric of DB time can be significantly skewed due to events being incorrectly listed as idle when they may be significant to the problem at hand. While this can cause problems for the Wait Event performance tuner, a solid understanding of the fundamental architecture of ADDM will allow direct access to more granular information and the skewing can be avoided or minimized.

The key views used for ADDM are v$sess_time_model (Figure 8.3) and v$sys_time_model (Figure 8.4) as shown below.

SQL> desc v$sess_time_model

Name Null? Type

----------------------------- ---------- ----------------

SID NUMBER

STAT_ID NUMBER

STAT_NAME VARCHAR2(64)

VALUE NUMBER

Figure 8.3 – Describe of v$sess_time_model

SQL> desc v$sys_time_model

Name Null? Type

----------------------------- ---------- ----------------