René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Wait events in Oracle

When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen befor it can proceed.
For example, if Oracle (or the SQL statement) wants to modify data, and the corresponding database block is not currently in the SGA, Oracle waits for this block to be available for modification.
All possible wait events can be found in v$event_name. In Oracle 10g R1, there are some 806 different wait events.
What Oracle waits for and how long it has totally waited for these events can be monitored through the following views:

Important events

buffer busy waits

If two processes try (almost) simultaneausly the same block and the block is not resident in the buffer cache, one process will allocate a buffer in the buffer cache and lock it and the read the block into the buffer. The other process is locked until the block is read. This wait is refered to as buffer busy wait.
See also this link.

db file scattered read

A process reads multiple blocks (mostly as part of a full table scan or an index fast full scan).
It can also indicate a multiblock read when the process reads parts of a sort segement.

db file single block read

db file sequential read

In most cases, this event means that a foreground process reads a single block (because it reads a block from an index or because it reads a block by rowid).

direct path read

enqueue

The enqueue wait event can be queried through v$enqueue_stat.

free buffer waits

latch free

log buffer space

This wait event indicates that the size of the log buffer is chosen too small.

log file sync

SQL*Net more data from client

SQL*Net more data to dblink

write complete waits

Wait classes

Wait events can be categorized by wait classes. These classes are exposed through v$session_wait_class.
The following wait classes exist:

Administrative

Application

Cluster

Concurrency

Configuration

Commit

Idle Waits

Network

Other

System I/O

Scheduler

User I/O

Parameters

The parameters P1, P2 and P3 in v$session_wait are dependent on the wait.
P1 refers sometimes to the datafile number.
If this number is greater than db_files, it refers to a temp file.
The name of the datafile for a number can be retrieved through v$datafiles.