OEM Wait Event Metrics
Oracle Tips by Burleson Consulting
OEM Wait Event Metrics
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 has been introduced, it
is possible to explore how OEM yields 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),
Oracle Enterprise Manager now has a built-in interface to the
Automatic Database Diagnostic Monitor and the intelligent SQL Tuning
Using the Enterprise Manager with ADDM and the SQL Tuning advisor
can save the manual tuning of hundreds of SQL statements. The new
Oracle10g SQL profiles allow the DBA to rapidly and reliably
complete a complex tuning effort in just a few hours.
Figure 19.91 below shows the specific times when the server exceeds
the maximum CPU capacity and the total time spent by active Oracle
sessions for both waiting and working.
time-series resource component utilization
This is an especially important screen for customizing OEM alerts
because thresholds can be set based on changes with either absolute
of delta-based metrics. For example, the DBA might want to be
alerted by OEM when the following session metrics are exceeded:
- Alert when there are more than 500 active
sessions waiting on I/O
– Alert when active sessions waiting on I/O
increase by more than 10%
– Alert when wait time exceeds 2 seconds
– Alert when wait time increases by more than 25%
The new OEM also allows the DBA to view session wait information at
the metric level. For example, if OEM informs the DBA that the
major wait event in the database is related to concurrency (locks,
latches, pins), the DBA can drill down on the concurrency link to go
to the OEM Active sessions waiting screen as shown in Figure 19.92.
display for active sessions waiting on concurrency
This display is also a useful learning aid because OEM lists all of
the sources of concurrency waits, such as library cache lock, latch,
and buffer busy waits. It
also displays the values associated with each concurrency
Double clicking on the chosen snapshot causes OEM to deliver a
summary histogram of the response time components for the top 10 SQL
statements and top 10 sessions that were identified during the AWR
snapshot as shown in Figure 19.93.
top-10 SQL and top-10 session response time component display
This visual display of summary information allows users to quickly
find the most resource intensive tasks and instantly see if the main
response time component is I/O, CPU, or Oracle internal wait
events. Oracle performance investigations that used to take hours
are now completed in a matter of seconds.
From this view, one can clearly see the total components of Oracle
wait times including CPU time, concurrent management overhead
(locks, latches), and I/O. This display also shows the times when
CPU usage exceeds the server capacity.
The Automatic Diagnostic component of the Oracle Performance OEM
screen contains an alert area where ADDM warns about historical
performance exceptions. This exception-based reporting is very
important to Oracle tuning because Oracle database change rapidly,
and transient performance issues are very difficult to detect
without an exception-based mechanism. The OEM alerts screen with
the link to Advisor Central is shown in Figure 19.94.
alerts screen with link to Advisor Central
This link between database and server exceptions gives a preview of
the exceptional conditions and validates the recommendations from
the Advisor Central area of OEM.
SEE CODE DEPOT FOR FULL SCRIPTS
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!