Chapter 19: Wait Events

---

Overview

A wait event is a database event indicating that something is waiting for something else. In other words, a process could be waiting for another process to release a resource that the first process requires.

A wait event occurs because something is being processed where other processes waiting for its completion are dependent on the result of the event causing the wait. When one process waits for another to complete a wait event is recorded in database statistics.

Tip 

The TIMED_STATISTICS configuration parameter must be set to TRUE.

Much like ratio calculations wait events are sometimes best analyzed as symptoms of problems, such as contention. Wait event frequency points to symptoms of problems, rather than being direct solutions. Therefore, tune the problem causing the wait event. Do not necessarily simply attempt to reduce wait events without being sure that the real cause of the problem is addressed, although sometimes this will be the case. Understand the problem. Also note that frequent occurrence of some wait events is normal, depending on database type and applications.

There are numerous performance views involved in recording of wait event details. These are some of them:

• V$EVENT_NAME.   All possible events.

• V$SYSTEM_EVENT.   Total waits for all occurring database events, aggregated for all sessions.

• V$SESSION_EVENT.   The same information as V$SYSTEM_ EVENT except detailing events per session rather than aggregated for all sessions.

• V$SESSION_WAIT.   This view is session level and shows a real-time picture where wait events are listed as they are happening. Contains hooks through to physical objects and sessions, through sessions ultimately leading to SQL code, amongst many other things.

• V$WAITSTAT.   Shows contention or competition for access to different types of blocks such as datafile blocks, rollback, and sort space.

There is much more to wait events than presented in this chapter. This chapter and the next simply present wait events and latches. The Oracle Database Wait Event Interface will be covered in Chapter 22. The Oracle Database Wait Event Interface can be used to drill down through a multitude of performance views, in an attempt to isolate events causing performance bottlenecks.

Note 

 Oracle Database 10 Grid   Numerous Wait Model changes are made including changes to various V$ performance views and a number of additional V$ performance views. These will be covered in Chapter 22.

Wait events can be divided into significant and idle events. A significant event is an event potentially indicating a problem such as physical or logical resource contention. An idle event describes an activity such as process polling and generally is not an indication of a problem. First of all let's get idle events out of the way so that we know what can generally be ignored.

---