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.
listing of the AWR metrics from inside OEM.
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.
Grid/RAC display screen.
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
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
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.
Manage Metrics screen.
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.
alert thresholds within OEM.
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
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
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
OEM: Edit job.
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.
exception reporting 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:
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
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.
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
This ability to perform server-side alerts is extremely valuable to
the Oracle professional who must monitor both internal and external
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
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
The bottom of the following screen shows the Related Links,
including the OEM Advisor Central link as shown in Figure 19.36:
The OEM alerts
screen with link to Advisor Central.
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
SEE CODE DEPOT FOR FULL SCRIPTS
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: