Oracle Wait Events

When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen before it can proceed.

For example, if 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.

Oracle provides a special views called v$session_wait,v$session_event and v$system_event that provide detailed information about the wait state for ongoing Oracle transactions.

Wait event analysis for Oracle cane be broken down into three areas:

• Time-based event analysis - The Oracle STATSPACK utility can be used to show trends in wait event over long time periods, and fluctuations in waits can often provide useful information.
• System-wide event analysis - The system wide events, show events for background processes.
• Session wait events - These are real-time waits at the exact moment going on. 

Here are few important wait events and possible causes or them.

Wait Event	Possible Causes
db file sequential reads	·    Use of an unselective index ·    Fragmented Indexes ·    High I/O on a particular disk or mount point ·    Bad application design ·    Index reads performance can be affected by low I/O subsystem and/or poor database ·    files layout, which result in a higher average wait time
db file scattered reads	·    The Oracle session has requested and is    waiting for multiple contiguous database    blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT)    to be read into the SGA from disk. ·    Full Table scans ·    Fast Full Index Scans
log file parallel write	·    LGWR waits while writing contents of the redo log buffer cache to the online log files on disk ·    I/O wait on sub system holding the online redo log files
log file sync	·    Oracle foreground processes are waiting for a COMMIT or ROLLBACK to complete
buffer busy waits	·    Buffer busy waits are common in an I/O-bound Oracle system. ·    The two main cases where this can occur are: Another session is reading the block into the buffer Another session holds the buffer in an incompatible mode to our request ·    These waits indicate read/read, read/write, ·     or write/write contention. ·    The Oracle session is waiting to pin a buffer. ·    A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time. ·    This wait can be intensified by a large block size as more rows can be contained within the block ·    This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy It is also often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index or data block)
free buffer waits	·    This means we are waiting for a free buffer but there are none available in the cache because there are too many dirty buffers in the cache Either the buffer cache is too small or the DBWR is slow in writing modified buffers to disk ·    DBWR is unable to keep up to the write requests ·    Checkpoints happening too fast – maybe due to high database activity and under-sized  online redo log files ·    Large sorts and full table scans are filling the cache with modified blocks faster than the DBWR is able to write to disk ·    If the  number of dirty buffers that need to be written to disk is larger than the number that DBWR can write per batch, then these waits can be observed
enqueue waits	·    This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode. ·    TX Transaction Lock ·    Generally due to table or application set up issues ·    This indicates contention for row-level lock. This wait occurs when a transaction tries to update or delete rows that are currently locked by another transaction. ·    This usually is an application issue. ·    TM DML enqueue lock ·    Generally due to application issues, particularly if foreign key constraints have not been indexed. ·    ST lock ·    Database actions that modify the UET$ (used extent) and FET$ (free extent) tables require the ST lock, which includes actions such as drop, truncate, and coalesce. ·    Contention for the ST lock indicates there are multiple sessions actively performing dynamic disk space allocation or deallocation in dictionary managed tablespaces
Cache buffer chain latch	·    This latch is acquired when searching for data blocks ·    Buffer cache is a chain of blocks and each chain is protected by a child latch when it needs to be scanned ·    Hot blocks are another common cause of cache buffers chains latch contention. This happens when multiple sessions repeatedly access  one or more blocks that are protected by the same child cache buffers chains latch. ·     SQL statements with high BUFFER_GETS (logical reads) per EXECUTIONS are the main culprits ·    Multiple concurrent sessions are executing the same inefficient SQL that is going after the same data set
Cache buffer LRU chain latch	·    Processes need to get this latch when they need to move buffers based on the LRU block replacement policy in the buffer cache The cache buffer lru chain latch is acquired in order to introduce a new block into the buffer cache and when writing a buffer back to disk, specifically when trying  to scan the LRU (least recently used) chain containing all the dirty blocks in the buffer cache. ·    Competition for the cache buffers lru chain latch is symptomatic of intense buffer cache activity caused by inefficient SQL statements. Statements that repeatedly scan large unselective indexes or perform full table scans are the prime culprits.  ·    Heavy contention for this latch is generally due to heavy buffer cache activity which can be caused, for example, by: Repeatedly scanning large unselective Indexes
Direct Path Reads	·    These waits are associated with direct read operations which read data directly into the sessions PGA bypassing the SGA ·    The "direct path read" and "direct path write" wait events are related to operations that are performed in PGA like sorting, group by operation, hash join ·    In DSS type systems, or during heavy batch periods, waits on "direct path read" are quite normal ·    However, for an OLTP system these waits are significant ·    These wait events can occur during sorting operations which is not surprising as direct path reads and writes usually occur in connection with temporary tsegments ·    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 the work area in the PGA
Direct Path  Writes	·    These are waits that are associated with direct write operations that write data from users’ PGAs to data files or temporary tablespaces ·    Direct load operations (eg: Create Table as Select (CTAS) may use this) ·    Parallel DML operations ·    Sort IO (when a sort does not fit in memory
Latch Free Waits	·    This wait indicates that the process is waiting for a latch that is currently busy (held by another process). ·    When you see a latch free wait event in the V$SESSION_WAIT view, it means the process failed to obtain the latch in the willing-to-wait mode after spinning _SPIN_COUNT times and went to sleep. When processes compete heavily for latches, they will also consume more CPU resources because of spinning. The result is a higher response time
Library cache latch	·    The library cache latches protect the cached SQL statements and objects definitions held in the library cache within the shared pool. The library cache latch must be acquired in order to add a new statement to the library cache ·    Application is making heavy use of literal SQL- use of bind variables will reduce this latch considerably
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.
Row cache objects latch	·    This latch comes into play when user processes are attempting to access the cached data dictionary values.

set linesize 220

set pagesize 200

column event format a40

select event,count(*),avg(wait_time)

from v$session,v$session_wait

where v$session.sid = v$session_wait.sid

and type != 'BACKGROUND'

group by event

order by 2 desc ;

~~~ Cheers