Common Oracle AWR Wait Events Explained
This document provides an overview of frequently observed wait events in Oracle Automatic Workload Repository (AWR) reports, categorized by their primary area of impact. Understanding these wait events is crucial for diagnosing and resolving performance bottlenecks in an Oracle database.
DB FILE Type Waits – Physical I/O
These events indicate waits related to physical I/O operations from datafiles.
DB File Sequential Reads
Description: The user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. Typically a single-block read.
Common Causes: Memory starvation (insufficient buffer cache), non-selective indexes, high concurrency on specific blocks.
DB File Scattered Reads
Description: Similar to db file sequential reads, but the session is reading multiple data blocks (e.g., from a full table scan) and scattering them into different, non-contiguous buffers in the SGA.
Common Causes: Full table scans, insufficient indexing, large sorting operations.
Direct Path Writes
Description: Occurs when a process is writing buffers directly from the session's Program Global Area (PGA) to datafiles, bypassing the SGA buffer cache. The process waits for the write call to complete.
Common Causes: Direct-path INSERT operations (e.g., INSERT /*+ APPEND */), data loads (e.g., SQL*Loader direct path), parallel DML.
Direct Path Reads
Description: An I/O operation that reads data directly into the session's PGA instead of the SGA.
Common Causes: Parallel query execution (parallel slaves retrieving data), sorting to disk, hash joins spilling to disk, temporary tablespace activity.
DB File Parallel Writes
Description: This wait event is associated with the Database Writer (DBWR) process. DBWR compiles a set of dirty blocks, hands the batch over to the OS for writing to datafiles, and waits for the I/O to complete.
Common Causes: High database activity generating many dirty blocks, backup operations, partitioned table usage (where DBWR writes blocks for multiple partitions in parallel).
DB File Parallel Reads
Description: Occurs during database recovery, where blocks that need to be changed are read in parallel. Can also happen during buffer prefetching as an optimization.
Common Causes: Instance recovery, crash recovery, certain optimization paths (e.g., multi-block reads for specific operations).
DB File Single Write
Description: Used to wait for the writing of single blocks, typically file headers.
Common Causes: File header writes (e.g., during checkpointing, file resizing), excessive number of data files.
Direct Path Read Temp
Description: Reading data directly from temporary files into the PGA.
Common Causes: Extensive temporary segment activity due to large sorts, hash joins, temporary table usage, or bitmap operations that spill to disk.
Direct Path Write Temp
Description: Writing data directly to temporary files from the PGA.
Common Causes: Similar to Direct Path Read Temp, indicating heavy use of temporary tablespace for sorts, hashes, or temporary tables.
BUFFER Type Waits – Logical I/O
These events relate to contention or issues within the SGA buffer cache.
Free Buffer Waits
Buffer Busy Wait
Latch Free Waits
Description: Latches are internal Oracle locking mechanisms that protect SGA data structures. This wait occurs if a session needs to acquire a latch that is currently held by another session.
Common Causes: High concurrency on shared memory structures (e.g., library cache latch, shared pool latch, cache buffers chains latch), inefficient SQL (e.g., excessive parsing).
Log Type Waits
These events are related to the redo log buffer and online redo log files.
Log File Parallel Write
Description: The Log Writer (LGWR) process is writing the redo buffer to the online redo log files in parallel and waits for the I/O to complete.
Common Causes: Slow I/O subsystem for redo logs, insufficient redo log group members, large redo generation rates.
Log Buffer Space
Description: A session waits for space to become available in the log buffer because redo information is being generated faster than LGWR can write it to the redo files.
Common Causes: Log buffer is too small (LOG_BUFFER), redo log files are on disks with I/O contention, very high transaction rates.
Log File Switch (Archiving Needed)
Description: A log switch is pending, but the next online redo log file cannot be used because it has not yet been archived.
Common Causes: Archiver processes are slow or stopped, archive destination is full or slow, insufficient archiver processes (LOG_ARCHIVE_MAX_PROCESSES).
Log File Switch (Checkpoint Incomplete)
Description: A log switch is pending, but the checkpoint for the next log file has not completed, preventing LGWR from wrapping into it.
Common Causes: Redo log files are sized too small, FAST_START_MTTR_TARGET is set too high, slow I/O for datafiles (impacting checkpoint completion).
Log File Switch Completion
Description: Sessions are waiting for a log switch operation to fully complete.
Common Causes: Slow I/O for redo logs, issues with archiver processes, or slow checkpoint completion.
Log File Sync
Description: When a user session commits, its redo information must be flushed to the redo logfile. The session waits for LGWR to write the log buffer to disk and then post the user session. The wait time includes both the write and the post.
Common Causes: Slow I/O to redo logs (most common), high commit rate, CPU contention affecting LGWR, network latency in RAC environments.
PX – Parallel Query
These events are related to operations performed by parallel execution processes.
GC – Global Cache (RAC Related)
These events indicate contention or waits in Oracle Real Application Clusters (RAC) environments for accessing data blocks across instances.
gc current block request: Waiting for a current version of a data block from another instance.
gc cr block request: Waiting for a consistent read (CR) version of a data block from another instance.
gc current block 3-way: A more complex scenario for current block requests involving three instances.
gc current block busy: The requested current block is busy on the holding instance.
gc cr block grant 2-way: A simpler scenario for consistent read block requests.
Undo or Rollback Segment Related
These events are associated with undo segment management and rollback operations.
By analyzing the top wait events in AWR reports and understanding their causes, DBAs can effectively pinpoint and address performance bottlenecks in Oracle databases.
