Deal With Wait Events
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.
- 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.
- 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.
- 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.
- 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.
- 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 Log Writer process to make space available.
- When a COMMIT is issued, the session must wait for the Log Writer process to write the blocks in question to the redo log file.
- The Log Writer 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 them 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.
Related Topics
Tune Your Oracle Database
