8.2. Managing Server-Generated Alerts

Now that you've seen the role of the AWR, we can move ahead to the other elements in the CMI. The first will be server-generated alerts.

The term server-generated alerts refers to the database's ability to detect problematic situations and to send an alert with both notification of the problem and suggestions about how to resolve it. These alerts can be classified as either threshold or non-threshold alerts.

Threshold alerts leverage the metrics computed by the MMON process to determine potential performance problems. This is accomplished by comparing the current metrics to preset threshold levels. If the threshold is exceeded, an alarm is generated.

Non-threshold alerts are triggered by database events, as opposed to specific threshold levels. For example, when an ORA-01555 Snapshot Too Old error occurs, a non-threshold alert is generated.

Oracle offers a set of default alerts that are automatically generated based on predefined criteria. You can also define your own alerts, based on metric thresholds of your choice.

The following sections will discuss threshold and non-threshold alerts, as well as Oracle's default alerts. You'll also learn about the views available to access alert data. Lastly, you'll see how to manage, create, and modify your own alerts through the PL/SQL API and through EM Database Control.

8.2.1. Threshold and Non-Threshold Alerts

Threshold alerts are kind of like smoke detectors. Smoke detectors monitor the air in your house and generate an alert (a very loud one) if the level of smoke surpasses a certain level. A threshold alert, however, can be defined based on 161 different performance metrics and offers more civilized notification methods (such as e-mail and paging).

Let's say you are concerned with a specific performance metric such as "physical reads per second." A threshold alert can be defined to monitor this metric and raise an alert based on the current value of the metric against two threshold settings: the warning threshold and the critical threshold.

The warning threshold is the lowest level at which an alert will be generated. When physical reads are starting to creep up, for instance, a warning threshold may be exceeded. In this case, an alert is generated and notification is sent containing a predefined warning value.

When a critical threshold is exceeded, an alert is generated and notification is sent containing a predefined critical value. This may represent the level at which your instance is disk-bound and the performance impact is severe.

Threshold alerts are not limited to instance-level metrics, though. They can also be used for database-specific metrics such as tablespace usage.

Non-threshold alerts are generated based on specific database events, rather than on threshold settings. Often, these are events that are logged to the alert log such as a hung ARCH process. In fact, an alert can be generated whenever any event is logged to the alert log.

8.2.2. Default Alerts

Oracle comes with a set of predefined alerts, both threshold and non-threshold. These alerts represent a base set of situations that Oracle deems worthy of notification in every database. For example, any ORA-01555 (snapshot too old) situation will generate an alert. Also, if Oracle detects a certain number of blocked sessions (session waiting for resources held by another session), failed jobs, or if tablespace usage exceeds a predefined percentage, alerts will be generated. Some of these default alerts will allow user modification, though some of them are locked and cannot be altered.

8.2.3. An Overview of the Alert Process

The MMON process automatically computes metric values from in-memory performance statistics once every minute. If a metric has a threshold value defined (either warning, critical, or both), the MMON process compares the newly computed metric to the threshold level. If the threshold has been exceeded, an alert is generated.

Also, when a foreground process encounters certain unusual conditions, the process can invoke MMON to process the condition on its behalf.

When an alert is generated, an alert message is sent to a persistent queue called ALERT_QUE, which is monitored by Enterprise Manager. When the message is received, the alert is displayed on the EM Database Control console.

If notification methods have been defined, the alert is also sent to the designated administrator(s) via their notification method of choice.

Depending on the alert, EM Database Control may also include corrective action suggestions in the notification.

If MMON is unable to successfully write the alert message to the queue, it will instead be written to the alert log.

8.2.4. Using Enterprise Manager to Configure Thresholds

Metric thresholds can be managed through the EM Database Control Manage Metrics screen. In the following sections, you will learn how to view and edit metric thresholds using EM Database Control. You will also learn to set multiple thresholds on aggregate metrics.

8.2.4.1. Viewing Metric Thresholds

From the EM Database Control console, click the Manage Metrics link, which will open the Manage Metrics screen. This screen allows you to view current metrics settings, as shown in Figure 8.2.

Notice that for each metric, a comparison operator, warning threshold, critical threshold, and response action can be assigned. For some metrics, Oracle has predefined the thresholds, as discussed earlier.

Figure 8.2. The Manage Metrics screen

8.2.4.2. Editing Metric Thresholds

By default, many metrics do not have any thresholds defined. To set threshold levels for a metric, click the Edit Thresholds button on the Manage Metrics screen. This opens the Edit Thresholds screen (shown in Figure 8.3), which looks much like the Manage Metrics screen except that it allows edits.

Figure 8.3. The Edit Thresholds screen

Table 8.6 lists the settings that you can edit for each metric. To make a change, simply enter the new value(s) and click OK.

8.2.4.3. Setting Multiple Thresholds

Some metrics may contain values for multiple distinct entities. For example, Average File Read Time averages the metrics for all datafiles in the database. However, you may want to set thresholds for each file individually. In this case, you can select the metric and click the Specify Multiple Thresholds button, which brings up the Specify Multiple Thresholds screen. The Specify Multiple Thresholds button is located at the top right side of the metrics display area.

The Specify Multiple Thresholds screen is context-sensitive based on the selected metric. For example, if the selected metric is related to the archive, you'll be prompted for individual archive area destinations. If the metric is related to file I/O, you'll be prompted for individual filenames. Figure 8.4 shows the Specify Multiple Thresholds screen when called for the Average File Read Time metric. As you can see, a threshold has been set for one specific data file. You can define thresholds for each data file individually to achieve finer granularity in monitoring your data files.

Figure 8.4. The Specify Multiple Thresholds screen

8.2.5. Setting Thresholds through PL/SQL

Server-generated alerts can also be set up through the DBMS_SERVER_ALERTS PL/SQL package. The package contains two procedures: SET_THRESHOLD and GET_THRESHOLD.

The SET_THRESHOLD procedure is used to define thresholds for a given metric. The GET_THRESHOLD procedure returns current threshold settings for a given metric.

As you saw previously, thresholds are set using a comparison operator. These operators determine the type of comparison to be performed between the current metrics and the threshold levels. In PL/SQL, this parameter is defined by using one of Oracle's predefined constants. Table 8.7 lists the constants and the operations that they represent.

The SET_THRESHOLD procedure parameters are listed in Table 8.8.

The following example sets the threshold for the CPU Time Per Call metric:

EXEC DBMS_SERVER_ALERTS.SET_THRESHOLD(
  METRICS_ID => CPU_TIME_PER_CALL,
  WARNING_OPERATOR => OPERATOR_GT,

WARNING_VALUE => '15000',
  CRITICAL_OPERATOR => OPERATOR_GT,
  CRITICAL_VALUE => '30000', OBSERVATION_PERIOD => 10,
  CONSECUTIVE_OCCURRENCES => 3);

OBJECT_TYPE_SYSTEM
OBJECT_TYPE_FILE
OBJECT_TYPE_SERVICE
OBJECT_TYPE_TABLESPACE
OBJECT_TYPE_EVENT_CLASS
OBJECT_TYPE_SESSION

This example causes a warning alert to be generated if the CPU_TIME_PER_CALL exceeds 15,000 or a critical alert to be generated if CPU_TIME_PER_CALL exceeds 30,000 for 40 minutes.

8.2.6. Viewing Server Alerts

A number of dictionary views are available to access information regarding server alerts. These are listed in Table 8.9.