Oracle Wait Events That Everyone Should Know

Here are some of the Common Oracle wait events that everyone should Know.

Wait events
You can find which event session is waiting for it by following query

select event from V$session_wait where sid=&1

I am trying to explain few common Oracle wait events ,there causes and resolutions

enqueue

The process is waiting on an oracle enqueue (a lock you can see in v$lock). This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user. The blockers can be find out by using following query

select * from dba_waiters

library cache pin
The process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view. Avoid compiling PL/SQL object or oracle view at high usage time to avoid this wait event

library cache load lock
The process is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.)

latch free
The process is waiting for a latch held by another process. (This wait event does not apply to processes that are spinning while waiting for a latch; when a process is spinning, it is not waiting.).Latches are lightweight serialization devices used to coordinate multi-user access to shared data structures, objects, and files.
Latches are locks designed to be held for extremely short periods of time for example, the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool.

Oracle Latches are typically requested internally in a ‘willing to wait’ mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an ‘immediate’ mode, which is similar in concept to a SELECT FOR UPDATE NO-WAIT, meaning that the process will go do something else, such as try to grab an equivalent sibling latch that may be free, rather than sit and wait for this latch to become available. Since many requests may be waiting for a latch at the same time, you may see some processes waiting longer than others.

Latches are assigned rather randomly, based on the luck of the draw, if you will. Whichever session asks for a latch right after it was released will get it. There is no line of latch waiters just a mob of waiters constantly retrying.

buffer busy waits
The process wants to access a data block that is currently not in memory, but another process has already issued an I/O request to read the block into memory. (The process is waiting for the other process to finish bringing the block into memory.). The hot blocks can be found using view V$BH

db file scattered read
The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.

We should check if the query should be  using full table scan. Make sure Oracle optimizer stats are Up to date. Use Partition pruning to reduce number of blocks visited

If an query that has been running fine for a while suddenly clocks a lot of time on the db file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes has been dropped or become unusable.

db file sequential read
The process has issued an I/O request to read one block from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during an index lookup or a fetch from a oracle table by ROWID when the required data block is not already in memory. Do not be misled by the confusing name of this wait event!

We should be checking if the right indexes are being used. A wrong index can make the query perform badly.Make sure optimizer stats are Up to date.

db file parallel read
The process has issued multiple I/O requests in parallel to read blocks from data files into memory, and is waiting for all requests to complete. The documentation says this wait event occurs only during recovery, but in fact it also occurs during regular activity when a process batches many single block I/O requests together and issues them in parallel. (In spite of the name, you will not see this wait event during parallel query or parallel DML. In those cases wait events with PX in their names occur instead.)

db file parallel write
The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.

direct path read, direct path write
The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.

SQL statements with functions that require sorts, such as ORDER BY, GROUP BY, UNION, DISTINCT, and ROLLUP, write sort runs to the temporary tablespace when the input size is larger than work-area in PGA

Make sure the optimizer stats are up to data and query is using  the right driving table. Check to see if the composite index’s columns can be rearranged to match the ORDER BY clause to avoid sort entirely.

Make sure appropriate value  PGA_AGGREGATE_TARGET is set.  If possible use UNION ALL instead of UNION.

Shared pool latch

The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. Contentions for the shared pool and library cache latches are mainly due to intense hard  parsing. A hard parse applies to new cursors and cursors that are aged out and must be re-executed
The cost of parsing a new SQL statement is expensive both in terms of CPU requirements and the number of times  the library cache and shared pool latches  may need to be acquired and released.

Eliminating literal SQL is also useful to avoid the shared pool latch

control file sequential read
The process is waiting for blocks to be read from a control file. This happens generally

• making a backup of the controlfiles
• sharing information (between instances) from the controlfile
• reading other blocks from the controlfiles
• reading the header block

If this is major waiting event, it means control file location need to changed to faster disk location

control file parallel write
The process has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.

log buffer space
The process is waiting for space to become available in the log buffer (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk.

This can also happen, if the I/O to disk where redo logs are located is slow

There should be no log buffer space waits as such in the database.Consider making the log buffer bigger if it is small or consider moving log files to faster disks such as striped disks.

Select event, total_waits, total_timeouts, time_waited, average_wait
from v$system_event
where event = 'log buffer space';
Select sid, event, seconds_in_wait, state
from v$session_wait
where event = 'log buffer space';
Select name, value
from v$sysstat
where name in ('redo log space requests');

The pct_buff_alloc_retries should be zero or less than 0.01 (< 1%). If it is greater consider making the log buffer bigger. If it is greater consider moving the log files to faster disks such as striped disks.

Select v1.value as redo_buff_alloc_retries, v2.value as redo_entries,
trunc(v1.value/v2.value,4) as pct_buff_alloc_retries
from v$sysstat v1, v$sysstat v2
where v1.name = 'redo buffer allocation retries'
and v2.name = 'redo entries';

log file sequential read
The process is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.

log file parallel write
The process is waiting for blocks to be written to all online redo log members in one group. LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.

log file sync
The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction. (A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)

A slow LGWR process can introduce log file sync waits which makes the user to experience wait times during commit or rollback. The log file parallel write and log file sync wait events are interrelated and must be dealt simultaneously.

We must try to allocate the redo logs to high performance disk(Solid state disk). Also we should try to reduce the load on LGWR by reducing  commits in the applications.

The manual hot-backup  piece can also introduce stress in the system by generating lot of redo stuff,So avoid that during peak time

Sometimes LGWR is starving for CPU resource. If the server is very busy, then LGWR can starve for CPU too. This will lead to slower response from LGWR, increasing ‘log file sync’ waits. After all, these system calls and I/O calls must use CPU. In this case, ‘log file sync’ is a secondary symptom and resolving root cause for high CPU usage will reduce ‘log file sync’ waits.

Due to memory starvation issues, LGWR can also be paged out. This can lead to slower response from LGWR too.

undo segment extension

The session is waiting for an undo segment to be extended or shrunk.

write complete waits

The session is waiting for a requested buffer to be written to disk; the buffer cannot be used while it is being written.

Latch: cache buffer chains

The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache.

Blocks in the buffer cache are placed on linked lists (cache buffer chains) which hang off a hash table. The hash chain that a block is placed on is based on the DBA and CLASS of the block. Each hash chain is protected by a single child latch. Processes need to get the relevant latch to allow them to scan a hash chain for a buffer so that the linked list does not change underneath them.

Contention on this latch usually means that there is a block that is in great contention (known as a hot block).

To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the V$LATCH_CHILDREN view. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

This latch has a memory address, identified by the ADDR column.

SELECT
addr,
sleeps
FROM
v$latch_children c,
v$latchname n
WHERE
n.name='cache buffers chains' and
c.latch#=n.latch# and
sleeps > 100
ORDER BY sleeps
/

Use the value in the ADDR column joined with the V$BH view to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR) of a heavily contended latch, this queries the file and block numbers:

SELECT file#, dbablk, class, state, TCH
FROM X$BH
WHERE HLADDR='address of latch';

X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.

Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output.

After you have identified the hot block, query DBA_EXTENTS using the file number and block number to identify the segment.

Important Information on wait event

The v$session_wait view displays information about wait events for which active sessions are currently waiting. The following is the description of this view, and it contains some very useful columns, especially the P1 and P2 references to the objects associated with the wait events.

desc v$session_wait

Name Null? Type
--------------------------- -------- ------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)

Using v$session_wait, it is easy to interpret each wait event parameter using the corresponding descriptive text columns for that parameter. Also, wait class columns were added so that various wait events could be grouped into the related areas of processing such as network, application, idle, concurrency, etc.
These column was also added to v$session table from 10g onwards. So you can just use v$session to find all the details

Each wait event contains other parameters that provide additional information about the event.
How to find the information about wait event and its parameter

The meaning of each wait event corresponds know by querying the V$EVENT_NAME p1, p2, p3 of
col name format a25;
col p1 format a10;
col p2 format a10;
col p3 format a10;
SELECT NAME, PARAMETER1 P1, PARAMETER2 P2, PARAMETER3 P3
FROM V$EVENT_NAME
WHERE NAME = '&event_name';

Let say for example we took

The event_name input values: db file scattered read
Original value of 3: WHERE NAME = ‘& event_name A’
The new value 3: WHERE NAME = ‘db file scattered read’

The name P1 P2 P3

---

db file scattered read file # block # blocks

file #: data file number
Block #: starting block number
blocks: to read the the the number of of the data block

Now let us see how the above information can help us capture various things
Suppose  a particular session waits for a buffer busy waits event, the database object causing this wait event can easily be determined:

select username, event, p1, p2 from  v$session_wait  where sid = 4563;

The output of this query for a particular session with SID 4563 might look like this:

USERNAME    EVENT            SID P1 P2
---------- ----------------- --- -- ---
APPS         buffer busy waits 4563  11  545

Columns P1 and P2 allow the DBA to determine file and block numbers that caused this wait event. The query below retrieves the object name that owns data block 155, the value of P2 above:

SQL> select segment_name,segment_type
from dba_extents
where file_id = 11
and 45 between block_id and block_id + blocks – 1;

The ability to analyze and correct Oracle Database  wait events is critical in any tuning project. The majority of activity in a database involves reading data, so this type of tuning can have a huge, positive impact on performance.

Note: When doing wait analysis, it is critical to remember that all Oracle databases experience wait events, and that the presence of waits does not always indicate a problem. In fact, all well-tuned databases have some bottleneck.

we can use 10046 event to trace wait event of the session also

Also Reads
Oracle documentation
v$active_session_history
Explain Plan in Oracle