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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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.

SEE CODE DEPOT FOR FULL SCRIPTS


   
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