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
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:
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.
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.
wait event analysis: Oracle resources associated with a
session can be traced to the exact external or internal wait
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.
SEE CODE DEPOT FOR FULL SCRIPTS
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: