Oracle ASH in OEM Oracle Tips by Burleson Consulting

Active Session History in Enterprise Manager

Together, the AWR and ASH metrics form the foundation for a complete Oracle tuning framework, and the Enterprise Manager provides a great tool for visualizing the bottlenecks.  Now that the underlying wait event collection mechanism has been explained, it is time to explore how OEM gives an intelligent window into this critical Oracle tuning information.

Before the use of OEM to identify a performance issue is examined, it must be noted that the AWR and ASH information can be used inside OEM to create customized exception alerts.  Even when the DBA is not watching, OEM can send an e-mail warning about any impending performance issue.  Figure 16.2 shows the ASH alert threshold screen:

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, it may be desirable to have OEM alert the DBA 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 increases by more than 10%

§         Wait Time (sec): Alert when wait time exceeds two seconds

§         Wait Time (%): Alert when wait time increases by more than 25%

The new OEM also allows the viewing of 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, such as locks, latches, pins, etc.  The DBA can drill down on the concurrency link to go to the OEM Active sessions waiting screen as shown in Figure 16.3.

This display is also a learning aid because OEM lists all of the sources of concurrency waits, including library cache lock, latch, and buffer busy waits, and it also displays the values associated with each concurrency component.  When one double clicks on the chosen snapshot, OEM delivers a summary histogram of the response time components for the top ten SQL statements and top ten sessions that were identified during the AWR snapshot as shown in Figure 16.4 below.

This visual display of summary information allows the quick identification of the most resource intensive tasks.  In addition, one can 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.

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 (OEM) now has a built-in interface to the Automatic Database Diagnostic Monitor (ADDM), and the intelligent SQL Tuning Advisor, both of which are explored in other chapters.

The main OEM performance screen displays a summary of session wait time server side components as shown in Figure 16.6.  Understanding the components involved in total response time can give huge insight into the root cause of a performance bottleneck.

In Figure 16.5, there are currently 3.1 active sessions with approximately one-third of the response time being consumed in CPU activities, which is a very common profile for 10g databases with large data caches.  The figure also includes the important SQL Response Time (%) delta metric that displays marginal changes on overall SQL performance.

The OEM interface to ASH also allows the DBA to drill down and view details on any of the active Oracle session.  Figure 16.6 shows the hyperlinks to detailed session statistics, wait events, open cursors and locks associated with the task.

Active Session History in Ion

Oracle10g OEM has limited functionality, especially for plotting and trending of Oracle wait event information, and Ion can easily relieve this burden.

Figure 16.7 shows the Ion screen for trending and plotting Oracle wait events, and Ion also allows the DBA to monitor, in real time, ASH wait statistics as shown in Figure 16.8 below:

The following section will dive deeper into the ASH tables to show how they can help identify and correct wait bottlenecks.

---

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: