The homegrown automatic monitoring of an Oracle database often results in time consuming, resource intensive tactics, in terms of DBA hours and development costs. A more elegant way to monitor database performance automatically is Oracle's Automatic Database Diagnostic Monitor (ADDM). For those of us who are either constrained by time, don't know how to script or just want a more elegant way to monitor database performance automatically, there is Oracle's Automatic Database Diagnostic Monitor (ADDM). This article will step us through the configuration of Oracle's Automatic Database Diagnostic Monitor (ADDM). We will touch on setting initialization parameters that are required to enable ADDM, setting the DBIO_EXPECTED parameter, and managing Automatic Workload Repository (AWR) snapshots. The information here is based on the Oracle Database 2 Day + Performance Tuning Guide, but if you looked at the section for configuring ADDM you would quickly see that it is geared towards using Oracle Enterprise Manager (OEM). This article will be different in that we will walk through doing the same thing from the command line in SQL*Plus instead.

Step 1. Setting the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter

Firstly, it should be noted that the CONTROL_MANAGEMENT_PACK_ACCESS parameter is new to Oracle Database 11g Release 1 (11.1). Also, by default, ADDM is enabled by default but controlled by the CONTROL_MANAGEMENT_PACK_ACCESS parameter. It is also worthy to note that Oracle Database options, management packs, and other products are separately licensed, meaning you should not use them unless you've paid for them. For this reason alone, it is probably a good idea to learn how to determine the current setting for CONTROL_MANAGEMENT_PACK_ACCESS, as it is a part of the Oracle Database's management pack, controlling Oracle's Diagnostics Pack and Oracle's Tuning Pack to be precise, and how to set it properly so you do not get inadvertently charge if audited.

The CONTROL_MANAGEMENT_PACK_ACCESS has three settings:

NONE - Oracle Diagnostics Pack and Oracle Tuning Pack is disabled on the database server, is strongly discouraged by Oracle, but you must if you haven't purchased a license.

DIAGNOSTIC - Oracle Diagnostics Pack is enabled on the database server

DIAGNOSTIC+TUNING - Both Oracle Diagnostics Pack and Oracle Tuning Pack are enabled on the database server

It is noteworthy here that there is no setting for this parameter such as TUNING. The Oracle tuning Pack can only be enabled if the Oracle Diagnostics Pack is enabled.

Again, CONTROL_MANAGEMENT_PACK_ACCESS for Oracle's Enterprise Edition is, by default, set to DIAGNOSTIC+TUNING.

To show what the current setting is, simply issue the following within SQL*Plus:

SQL> show parameters CONTROL_MANAGEMENT_PACK_ACCESS
NAME TYPE VALUE
-----------------------------------------------------
control_management_pack_access string NONE

To set to a specific value, and for our purposes of configuring Automatic Database Diagnostic Monitoring (ADDM) we should at least set this parameter as:

SQL> alter system set control_management_pack_access='DIAGNOSTIC';
System altered.

On another level, for administering database instance in a cluster (RAC) it is recommended that the CONTROL_MANAGEMENT_PACK_ACCESS be set to the same value on all instances. It is not a requirement mind you but is strongly recommended to help simplify the administration.

Step 2. Setting the STATISTICS_LEVEL initialization parameter

The STATISTICS_LEVEL initialization parameter has been around for quite some time and determines the level of database and operating system statistics that will be collected. Setting this parameter to TYPICAL or ALL will enable automatic database diagnostic monitoring, which is what we want. The three valid values are as follows:

TYPICAL - the default setting will collect all the major statistics that Oracle deems necessary for database self-management while providing the best overall performance. Typically, no pun intended, TYPICAL is usually adequate for most environments.

ALL - in addition to the TYPICAL collections, ALL will collect additional statistics such as timed operating system (OS) statistics and plan execution statistics.

BASIC - disables many of the important statistics and is highly discouraged.

To show what the current setting is, simply issue the following within SQL*Plus:

SQL> show parameters STATISTICS_LEVEL
NAME TYPE VALUE
-------------------------------------------------------
statistics_level string TYPICAL

To set to a specific value, and for our purposes of configuring Automatic Database Diagnostic Monitoring (ADDM) we should at least set this parameter, if not already, as:

SQL> alter system set statistics_level='TYPICAL';
System altered.

It is worth noting here, if you would like additional information on the type of statistics collected, the V$STATISTICS_LEVEL view, when queried, provides information about the different statistics or advisories collected or controlled by the STATISTICS_LEVEL parameter. You can easily look at these by the following SQL (output truncated):

SQL> select statistics_name,description,system_status
2* from v$statistics_level;
STATISTICS_NAME DESCRIPTION SYSTEM_S
--------------------------------------------------------------------
Buffer Cache Advice Predicts the impact of differe ENABLED
nt cache sizes on number of ph
ysical reads
MTTR Advice Predicts the impact of differe ENABLED
nt MTTR settings on number of
physical I/Os
Timed Statistics Enables gathering of timed sta ENABLED
tistics
Timed OS Statistics Enables gathering of timed ope DISABLED
rating system statistics
23 rows selected.

A couple of notes about one additional parameter, TIMED_STATISTICS, in relation to STATISTICS_LEVEL, if STATISTICS_LEVEL is set to BASIC (not recommended) then you must set TIMED_STATISTICS to TRUE for the collection of timed statistics to be enabled. If other parameters are explicitly set, such as TIMED_STATISTICS or TIMED_OS_STATISTICS then the explicit setting will override the value of STATISTICS_LEVEL. Just be aware of these types of interdependencies, by reading the manuals and you won't be caught off guard.

Step 3. Setting the DBIO_EXPECTED initialization parameter

The ability for ADDM to analyze I/O performance is somewhat dependent on the DBIO_EXPECTED argument, which describes the expected I/O subsystem performance. DBIO_EXPECTED simply states the average time it takes to read a single database block, in microseconds.

Ok, thought I'd just put this in here as many of us have problems converting / conceptualizing this whole seconds, milliseconds, microseconds, and nanoseconds thing:

Seconds 1
Milliseconds 1000
Microseconds 1000000
Nanoseconds 1000000000

Oracle uses a default value of 10 milliseconds, which is pretty accurate for current hard drive performance. However, if your drives are different, maybe because you have really old hard drives or you're using RAM disks, you might consider changing this value. To make an accurate setting for the DBIO_EXPECTED parameter you will need to measure the read time of a single database block read for your particular hardware. Do not lose sight of the fact that this might be published by your vendor as well; noting that this should be a measurement of a random I/O that includes seek time. Expect something between 5000 and 20000 microseconds. Then you just need to set the value with the following command, assuming 10000 microseconds:

SQL> EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM','DBIO_EXPECTED',10000);

The setting up of oracle Automatic database diagnostic monitoring (ADDM) is quite simple. By default is should be enabled but with three initialization parameters, CONTROL_MANAGEMENT_PACK_ACCESS, STATISTICS_LEVEL, and DBIO_EXPECTED, we can easily enable and modify slightly its use. Again, all of these steps could have been accomplished through Oracle's Enterprise Manager (OEM), but wasn't this a bit more fun?

Related Articles

ADDM Enhancements in Oracle Database 11g
Monitoring an Oracle Database Automatically

Back to DBAsupport.com