Why Wait Event Tuning for Oracle?

Wait event tuning has always been mysterious because the wait events happen so fast that it is often difficult to see what’s happening without taking a detailed task dump. Now with ASH, not only can specific details about individual sessions, SQL statements, and users be seen, but wait information can be tracked over periods of time, revealing the all important signatures that help schedule just-in-time resources to relieve instance related wait bottlenecks.

Time-based wait tuning is especially useful for tracking changes to SQL execution over time as well as database-wide stress. The Oracle ASH tables can be used to show trends in wait event over long time periods, and fluctuations in waits can often provide useful information.

A top-down approach starts at a high level and shows scripts that can be used to track system wide events and show events for background processes. Time-based wait event analysis for Oracle can be broken down into several areas:

• External wait event analysis: The advent of the new mainframe-like SMP servers means that servers may share hardware resources with dozens of other applications and databases. These external resources demand server resources, such as RAM, CPU, and disk channels, which can affect Oracle waits, and the ASH tables provide insights into external network waits, such as SQL*Net waits, and disk waits such as db file sequential and scattered read waits.

• Internal wait event analysis: Oracle internal mechanisms, such as buffer busy waits and latch waits, can change over time, depending on the overall database load. Time-series analysis with the ASH tables allows the DBA to find the cause of internal bottlenecks, even after the transaction has completed.

• Session-level wait event analysis: Oracle resources associated with a session can be traced to the exact external or internal wait event.

• SQL event analysis: In most OLTP systems, the same SQL statements may execute thousands of times per hour. Because SQL is the interface between applications and Oracle, tracing waits on an SQL statement over time can be especially useful.

When running the system-wide time-based wait event scripts, the DBA will be able to identify areas where the database is spending most of its time waiting. Before custom ASH scripts are presented, the following section provides a quick tour of the Oracle10g OEM as well as information on how it materializes the information from the ASH component.
 

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: