Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







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 advisor.


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.


Figure 19.91: OEM server 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:

         Active Sessions Waiting: I/O - Alert when there are more than 500 active sessions waiting on I/O

         Active Sessions Waiting: I/O (%) Alert when active sessions waiting on I/O increase by more than 10%

         Wait Time (sec) Alert when wait time exceeds 2 seconds

         Wait Time (%) 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.


Figure 19.92:  The OEM 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 component. 


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.


Figure 19.93:  The OEM 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.


Figure 19.94: The OEM 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.


Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational