Oracle OEM Alerts Oracle Tips by Burleson Consulting

Tuning with Metrics and Exceptions

From this main OEM performance screen the DBA can quickly drill-down and view all AWR metrics and scroll through the complete list of automatically captured statistics as shown in Figure 19.2.

This feature allows the DBA to drill down into important Oracle performance areas including instance efficiency, SQL response time, SGA pool wastage, and wait bottlenecks. 

There is more to the data collection than instance-wide metrics.  OEM can be customized to send alerts for whatever combination of metric values desired. 

For example, the OEM Grid controller is used to add an additional RAC node to the system during this period, just-in-time to meet the increased processing demands as shown in Figure 19.3.

Now that it has been shown how OEM incorporates external metrics, the ways OEM makes it easy to view Active Session History (ASH) information will be revealed.  The ASH component is brand new in Oracle10g and allows the DBA to quickly spotlight the important wait events associated with any Oracle task.

Active Session History in Enterprise Manager

Oracle10g now has an Active Session History (ASH) component that automatically collects detailed metrics on individual Oracle sessions.  OEM also has an interface to the ASH component of AWR.  The ASH uses special dba_hist views to collect and store highly detailed system event information allowing immediate access to every detail about Oracle execution.

Together, the AWR and ASH metrics form the foundation for a complete Oracle tuning framework, and Enterprise Manager provides the vehicle.  Now that the underlying mechanism is shown, the DBA can explore how OEM gives an intelligent window into this critical Oracle tuning information.

While this functionality of OEM is amazing in its own right, Oracle10g has taken the AWR model beyond the intelligent display of performance metrics.  Using true Artificial Intelligence (AI), OEM now has a built-in interface to the Automatic Database Diagnostic Monitor, pronounced “Adam”, and the intelligent SQL Tuning and SQL Access advisors.

The next section explores the Automated Alert mechanism within Enterprise Manager.  This is a very important feature for Oracle tuning because it allows alert thresholds to be predefined and notifications about pending database problems to be sent.  This gives the DBA the critical time necessary to fix the issue before the end-users suffer.

The use of the Enterprise Manager with ADDM and the SQL Tuning advisor can save the DBA from the tedium of manually tuning hundreds of SQL statements. The new Oracle10g SQL profiles allow DBAs to rapidly and reliably complete a complex tuning effort in just a few hours.

Easy Customization of OEM Alerts

The Oracle10g Enterprise Manager recognizes that no DBA has the time to constantly monitor all of the metrics in real-time and provides an easy to use exception reporting mechanism.  Figure 19.4 shows the MANAGE METRICS screen in which the DBA can easily define a customized alert mechanism for a database.

When a drill down into the metric list occurs, OEM displays hundreds of individual tuning metrics and provides the ability to set personalized alert thresholds as shown in Figure 19.5.  OEM allows the DBA to specify any scalar thresholds, such as greater than or less than, and has full pattern matching capabilities for text-based alerts such as alert log messages.

For example, DBAs can set an OEM threshold to send them a pager alert or use OEM2GO whenever their critical metrics change.  There are several critical instance-wide performance metrics displayed in Figure 19.5:

§         SQL Response Time (%)

§         System Response Time (centi-seconds)

§         Shared Pool Free (%)

Because of the time-series nature of AWR, it is easy to trigger an exception alert when the marginal values of any metrics change.  All metrics denoted with the (%) are delta-based, meaning that OEM triggers an alert whenever any metric moves by more than a specified percentage, regardless of its current value.  This delta-based mechanism is used to allow time to repair a pending performance issue before it cripples the end-users.

For automated notification, a SNMP interface can be easily configured to have OEM send the DBA a notification e-mail whenever the threshold value has been exceeded.  This alert can be an e-mail, a telephone message or an alert on the OEM2GO PDA device.

Responding to OEM Alerts

Whenever an alert is received, many DBA’s run ADDM or another advisor to get a more detailed diagnostics of system or object behavior. The DBA can also opt to enable a corrective script to run on receiving an alert as mentioned in Managing Metric Thresholds section.

If a Tablespace Space Usage alert is received, remedial actions can be taken by running the Oracle10g Segment Advisor on the tablespace to identify objects for shrinking. Those objects can then be coalesced or extended.

All of the job details, including the schedule, job definition and the broken flag, can be edited within Enterprise Manager by double clicking on the job of interest. Figure 19.11 shows the edit job dialog.

The run procedure on this screen allows the DBA to run a specified job immediately, with the next_date recalculated from that point.  The force parameter indicates that the job queue affinity can be ignored allowing any instance to run the job.

Job information is also available from Oracle Enterprise Manager (OEM) (Network > Databases > Your-Instance > Distributed > Advanced Replication > Administration > DBMS Job Tab). 

Exception Tuning Inside Enterprise Manager

The Automatic Diagnostic component of the Oracle Performance OEM screen contains an alert area in which ADDM warns the DBA about historical performance exceptions.  This exception-based reporting is very important to Oracle tuning because Oracle databases change rapidly, and transient performance issues are very difficult to detect without an exception-based mechanism.

Exception reports allow the Oracle professional to view specific times and conditions when Oracle processing demands have exceeded the server capacity.  More important, these transient server exceptions give insight regarding repeating server trends. 

Figure 19.35 is a representation of the OEM alert screen.

In Figure 19.35, the Oracle alerts are located on the top-half of the screen and the external server alerts are located on the bottom half.  The server-related alerts are critical to Oracle performance because Oracle10g allows the DBA to relieve server stress by adding additional servers.  Common server-related alerts might include:

CPU utilization: Whenever the CPU run queue exceeds the number of processors on the server, the database is CPU-bound.  Actions might include tuning SQL to reduce logical I/O or adding more CPU resources

Filesystem Shortage: When using Oracle with autoextend datafiles, the only constraint to file growth is the limitation of the OS filesystem.  Should a filesystem become unable to accommodate an automatic datafile expansion, Oracle halts the process until additional space is allocated.  This monitoring task is critical to ensuring the continuous availability of the database.

Swap Shortage: The swap disk is used on a virtual processor to store infrequently used RAM frames.  When the swap disk becomes full, more disks should be added.

This ability to perform server-side alerts is extremely valuable to the Oracle professional who must monitor both internal and external Oracle environments.

The next section shows techniques for extending the OEM functionality for trend-based reporting, and explores the Automated Database Diagnostic Monitor (ADDM) as well as the SQL Tuning Advisor within OEM.  A real world Oracle10g migration for an Oracle8i application using the obsolete rule-based SQL optimizer (RBO) is also shown. 

A good understanding of the basic functionality of OEM performance monitoring and how OEM accesses the new AWR database is needed before exploring how Enterprise Manager interprets AWR and ASH information.   This information is used to diagnose performance issues with the Automatic Database Diagnostic Monitor (pronounced “Adam”). 

The bottom of the following screen shows the Related Links, including the OEM Advisor Central link as shown in Figure 19.36:

This link between the database and server exceptions provides a preview of the exceptional conditions and validates the recommendations from the Advisor Central area of OEM.

Next, attention can be focused on the examination of the OEM advisor area.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts: