|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 are:
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
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
The enqueue wait event can be queried through v$enqueue_stat.
See also enqueue types in x$ksqst
free buffer waits
See also optimal size of block buffer.
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 events can be categorized by wait classes. These classes are exposed through v$session_wait_class.
The following wait classes exist:
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.