Wait Events

Wait Events

This document describes different types of wait events

Every wait event belongs to a class of wait event. The following list describes each of those classes.

Administrative – waits resulting from DBA commands that cause users to wait to complete that action. Most of the DBA commands will wait till the command is successfully executed. For example, an index rebuild will wait on an administrative wait till it completes the rebuild operation. A create table as select operation will have to wait till the table is successfully created.

Application – waits resulting from user application code. Most of the enqueue waits belong to Application Wait category. A user session has to wait on TX wait before updating a row in a table.)

Cluster – Real Application Cluster related waits are categorized as Cluster waits. In real application Clusters all buffer cache related waits are cluster wide waits as the buffer cache is global in RAC. Similarly the block contention waits are also global in RAC.

Commit operation is confirmed to user session only when all the redo information corresponding to that transaction is successfully written to the disk. Till then the session waits on the ‘log file sync’ waits. This is the only wait on the commit class wait.

Concurrency – latches are used to serialized internal operations in the database. Waits for for internal database resources result in concurrency waits. Latch Free waits are classic examples for the concurrency waits.

Configuration – waits are related to database resource related issues. Waits caused by smaller log files or smaller shared pool waits are categorized in this class.

Idle – waits are special type of waits and they can not be treated as real waits. A session waits on IDLE event when there is nothing to do. Waits that signify the session is inactive, waiting for work (for example, ‘SQL*Net message from client’).

Network – waits related to SQL Net messaging.

Other – these waits are not categorized in any other of the regular classes. These should not typically occur on a system unless there is an underlying problem.

Scheduler – Oracle Database Resource manager allows Oracle DBA to manage a database server’s CPU resources effectively for application user groups and during different resource demand periods. Waits related to resource manager are classified in Scheduler waits.

System I/O – these waits are normally caused by the background processes. Waits for I/O are classified in this waits.

Whenever an Oracle session is not actually consuming or waiting for CPU resources, it will usually be in one of a number of wait events. For instance, a session may be waiting for some I/O request to be performed for free space in the SGA, for network traffic, or for an internal Oracle resource such as a latch. Some waits, such as those for datafile or log file I/O are normal and unavoidable (although you may be able to reduce their magnitude). Other waits, such as those for latches or buffers in the SGA may indicate inefficiency or bottlenecks.

In a perfect Oracle implementation, the Oracle server process is able to perform its tasks using its own resources without experiencing any delays. However, in reality, Oracle sessions often wait on system or database requests, or for resources to become available.

During a typical transaction, the Oracle session may need to wait for various resources to become available, such as:

While the application is idle, the server process is waiting for a message from the client.

1. When the server process parses a new SQL statement, and the statement has not previously been executed, it has to acquire a latch to add the new statement to the library cache. If the latch required is held by another session, the server process may have to wait for the latch to become available.

2. The server process also has to acquire a latch when executing a SQL statement held in the shared pool. It may have to wait on the latch if it is currently held by a different session.

3. When accessing a data block in the buffer cache, the server process has to change the location of the block on the least recently used (LRU) list. This requires obtaining and possibly waiting for the appropriate latch.

4. If the block is not in the buffer cache, the session has to issue and wait for an I/O request to obtain the block. Moving a new block into the buffer cache also requires a latch that might be unavailable and cause a wait.

5. Changing the data block requires obtaining a latch both to change the block itself and to make an entry in the redo log buffer. Additionally, if there is insufficient free space in the redo log buffer, the session needs to wait for the Logwriter process to make space available.

6. When a COMMIT is issued, the session must wait for the Logwriter process to write the blocks in question to the redo log file.

7. The Logwriter session itself may need to wait if the redo log is full, and the next redo log has an outstanding checkpoint or archive operation outstanding.

There are many reasons why an Oracle session may need to wait. Some of these waits (such as waiting for I/O operations) are inevitable. However, you can reduce waits in many cases by tuning I/O, the buffer cache, or the SQL involved. Other operations (such as waiting for latches) may indicate inefficiencies in your configuration and opportunities for further tuning.

Types of waits:
 DB file waits
 Log file sync/write waits
 Log file space/switch waits
 Buffer busy waits
 Free buffer waits
 Write complete waits
 Enqueue waits
 Latch free waits

DB file waits
Wait conditions starting with the phrase DB FILE (for example, db file parallel write, db file scattered read, db file sequential read, and db file single write) all occur when an Oracle session issues an I/O request against an Oracle datafile. The session uses the operating system’s read system call, and waits while the I/O subsystem performs the I/O.

Database-file writes are only performed by the database writer. The db file write waits are never experienced by user sessions. However, user sessions do read data from database files directly, and almost always experience db file read waits.

Unless your entire database is cached in memory, waiting for db file I/O is inevitable. The presence of db file waits does not indicate that anything is wrong within the database. In most healthy databases, db file waits account for about 80-90% of all non-idle wait times.

DB file waits can be reduced by:

1. Optimizing disk I/O, striping datafiles (see Improving database I/O).

2. Reducing I/O requirements by increasing the size of the buffer cache.

3. Reducing the I/O requirements of SQL statements through SQL tuning (see Isolating and tuning problem SQL).

Log file sync/write waits

Just as Oracle sessions must inevitably wait for db file I/O, they must also wait for log file I/O. These waits occur when a COMMIT is issued. A COMMIT causes the Redo log writer session to flush the contents of the redo log to the redo file. The user session must wait for this write operation to complete before the COMMIT statement returns control.

The session issuing the commit waits on the log file sync event. When the Log Writer process issues the I/O request, it waits on the log file parallel write event.

Both wait events are inevitable and usually account for between 10-20% of total non-idle wait times in a healthy database.

The average wait time for a log file parallel write is an important measure. It indicates how quickly the log writer process can flush the redo buffer. It is a good indicator of the efficiency of the redo log device. Values of 0.2 hundredths of a second are good, and values of up to five hundredths of a second are not unusual. Values above this range may indicate contention for the redo log device. For more information, see Improving redo log writer performance.

Log file space/switch waits
Log file space/switch waits occur when a redo log entry cannot be made. A redo log cannot be written to when:

1. there is no free space in the redo log buffer, or
2. it is in the process of being switched.

The following error messages may also be included in the alarm log for the database instance:

CANNOT ALLOCATE NEW LOG CHECKPOINT NOT COMPLETE
CANNOT ALLOCATE NEW LOG
ALL ONLINE LOGS NEED ARCHIVING.
The incidence of these events should be negligible in a well-tuned database. These two conditions are defined by the following events:

Log buffer space. Waiting for free space in the redo log buffer. You can reduce this wait by increasing the size of the log buffer (LOG_BUFFER parameter), or by optimizing the performance of the log writer.

Log file switch (checkpoint incomplete). The next redo log cannot be used because a checkpoint that commenced when the log was last switched has not completed.

Log file switch (archiving needed). A redo log cannot be used because an archive operation that commenced when it was last switched has not completed.
These waits may indicate that:

1. your redo logs are on slow devices
2. your LOG_BUFFER setting is too low
3. you have too few, or too small redo logs
4.
If you are getting log file switch (archiving needed) events, consider alternating your redo logs across multiple devices to reduce contention between the log writer and archiver processes, add more redos, increase their size. For more information, see Improving redo log writer performance.

Buffer busy waits
Buffer busy waits occur when a session cannot access a needed block because it is in use by another session.

The two most common causes are:

1. insufficient free lists for a table, or
2. too few rollback segments.

If most buffer waits are for data blocks, then it is likely that you need to create multiple freelists (using the FREELIST clause of the CREATE TABLE statement) for tables that are subject to very heavy concurrent inserts. If the leading category is for Undo Header or Undo Block, you may need to create additional rollback segments.

A freelist is a list of free blocks associated with a segment, which are eligible for accepting data when a new insert request comes. This normally speeds up the insert process since Oracle does not need to look at the entire block to put that row inside a table. The freelist structure is managed by a chain structure called a linked list. A singly linked list is the data structure used in managing freelists.

Free buffer waits
Free buffer waits occur when a session needs to read a data block from a database file on disk into the buffer cache. If there are no unmodified (or clean) blocks in the buffer cache, then the session has to wait for the database writer process to write modified (or dirty) blocks to disk in order for free buffers to be made available.

Normally, the database writer is constantly writing dirty buffers to disk and so this event rarely occurs. When it does occur, it is usually due to one of the following reasons:

1. Untuned disk layout. If datafiles are not spread evenly across disk devices, then a single disk may form a bottleneck to both read and write performance. In this circumstance, the database writer may not be able to clear dirty blocks from this device as rapidly as they are created.

2. Untuned database writers. To efficiently write to multiple disk devices, it is essential that you either configure multiple database writers or implement asynchronous or list I/O. This helps the database writer to keep up with changes to the buffer cache.
3. Untuned sorts. If the SORT_DIRECT_WRITES parameter is set to FALSE, large sorts that require a temporary segment may write the sort blocks to the buffer cache and rely on the database writer to move them into the temporary segment. This can flood the buffer cache and cause other sessions to encounter free buffer waits.

SORT_DIRECT_WRITES – The default value FALSE indicates that sorts writing to disk will write through the buffer cache. When set to TRUE, each sort will allocate additional buffers in memory for direct writes. The Oracle process performing the sort writes the sort data directly to the disk, bypassing the buffer cache. The direct write buffer memory is released when the last row is fetched from the sort space. The total number of direct write buffers in each user process never exceeds SORT_WRITE_BUFFERS.

Can improve your sort performance by up to six times if you have adequate memory. Setting the parameter to TRUE or AUTO forces sorts that would normally be written to disk via the buffer cache to write directly from other areas of memory, bypassing the buffer cache.

Write complete waits
Write complete waits occur when a session tries to modify a block that is currently being written to disk by the database writer process. This happens occasionally, but if it is contributing significantly to overall waits it may indicate inefficiencies in the database writer.

The solution may involve optimizing datafile I/O and database writer configuration. This can be done by spreading datafiles across multiple disks, using multiple database writers, or employing asynchronous or list I/O.

Enqueue waits
Enqueue waits occur when a session waits to obtain a lock. In most cases, this occurs because of a lock on a table or row that the waiting session needs to lock or modify. In some circumstances, the lock involved may be an Oracle internal lock. If the database is well tuned and the application design sound, enqueue waits should be negligible.

Common causes of excessive enqueue waits are:

1. Contention for a specific row in the database. The application design may require that many processes update or lock the same row in the database. One common example of this occurs when primary keys are generated using a sequence table.

2. Table locks caused by foreign keys that have not been indexed. If an update is made to a foreign key that has not been indexed, the parent table is subjected to a table lock until the transaction is complete.

3. Old-style temporary tablespaces. If the tablespace named as the temporary tablespace has not been identified with the temporary clause (introduced in Oracle 7.3), sessions may contend for a space transaction lock.

4. The space reserved for transactions within a data block is too small. By default, only one transaction slot for tables, or two for indexes, is allocated when the table or index is created. If additional transaction slots are required, they are created providing there is free space in the block. However, if all transaction slots are in use, and there is no free space in the block, a session that needs to lock a row in the block encounters an enqueue wait. This occurs even if the row in question is not actually updated or locked. This can occur if both PCTFREE and INITRANS were set too low.

Latch free waits
Latches are Oracle internal locking mechanisms. They prevent multiple sessions from simultaneously updating the same item within Oracle shared memory (SGA). If a session needs to acquire a latch that is held by another session, a latch free wait may occur.

The presence of latch free waits of any significant magnitude may indicate a bottleneck within the SGA. The specific action depends on the latch. For more information, see Relieving latch contention.

Log file wait events
Just as Oracle sessions must wait for database file I/O, they must also wait for log file I/O. Such waits occur whenever a COMMIT statement causes a write to the redo log. The session issuing the COMMIT waits on the log file sync event. When the log writer issues the I/O request, it waits on the log file parallel write event.

Both these wait events are inevitable, and often account for between 10-20% of total non-idle wait times in a healthy database.

The average wait time for a log file parallel write is an important measure. It indicates how quickly the log writer process can flush the redo buffer. It is a good indicator of the efficiency of the redo log device. Values below one-hundredth of a second are good, and values of up to five-hundredths of a second are not unusual. Values above this range may indicate contention for the redo log device.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: