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.

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.

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.

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.

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.