Analyzing Wait Events

Recipes in this chapter show you how to analyze the most important Oracle wait events. An
overwhelming amount of wait time in a database is due to I/O–related waits, such as those caused by
either full table scans or indexed reads. While indexed reads may seem to be completely normal on the
face of it, too many indexed reads can also slow down performance. Therefore, you must investigate why
the database is performing a large number of indexed reads. For example, if you see the db file
sequential read event (indicates indexed reads) at the top of the wait event list, you must look a bit
further to see how the database is accumulating these read events. If you find that the database is
performing hundreds of thousands of query executions, with each query doing only a few indexed reads,
that’s fine. However, if you find that just a couple of queries are contributing to a high number of logical
reads, then, most likely, those queries are reading more data than necessary. You must tune those
queries to reduce the db file sequential read events.

Wait events are statistics that a server process or thread increments when it waits for an event to
complete, in order to continue its processing. For example, a SQL statement may be modifying data, but
the server process may have to wait for a data block to be read from disk, because it’s not available in the
SGA. Although there’s a large number of wait events, the most common events are the following:

• buffer busy waits
• free buffer waits
• db file scattered read
• db file sequential read
• enqueue waits
• log buffer space
• log file sync

Analyzing Oracle wait events is the most important performance tuning task you’ll perform when troubleshooting a slow-running query. When a query is running slow, it usually means that there are

• Excessive waits of one type or another.
• Some of the waits may be due to excessive I/O due to missing indexes.
• Other waits may be caused by a latch or a locking event.

In general, wait events that account for the most wait time warrant further investigation. However, it’s important to
understand that wait events show only the symptoms of underlying problems—thus, you should view a
wait event as a window into a particular problem, and not the problem itself. When Oracle encounters a
problem such as buffer contention or latch contention, it simply increments a specific type of wait event
relating to that latch or buffer. By doing this, the database is showing where it had to wait for a specific
resource, and was thus unable to continue processing. The buffer or latch contention can often be
traced to faulty application logic, but some wait events could also emanate from system issues such as a
misconfigured RAID system. Missing indexes, inappropriate initialization parameters, inadequate values
for initialization parameters that relate to memory, and inadequate sizing of redo log files are just some
of the things that can lead to excessive waits in a database. The great benefit of analyzing Oracle wait
events is that it takes the guesswork out of performance tuning—you can see exactly what is causing a
performance slowdown, so you can immediately focus on fixing the problem.