Oracle Wait Events Explained: Top 10 and How to Fix Them

When an Oracle session cannot make progress on the CPU, it waits — for a disk block, a lock, a log write, a network round trip. Oracle instruments every one of these pauses as a wait event. Add up where the time goes and you have the single most useful lens on database performance: the wait profile.

The guiding principle is Oracle's own performance methodology, sometimes called tuning by wait: find the events that consume the most DB time, and attack those first. Don't tune what isn't slow.

Quick Reference: Top 10 Oracle Wait Events

Wait Event	Class	Typical Cause	First Action
`db file sequential read`	User I/O	Index access, single-block I/O	Check Top SQL by physical reads
`db file scattered read`	User I/O	Full table scan, missing index	Check execution plans
`log file sync`	Commit	Slow redo I/O or too many commits	Check redo log storage
`log file parallel write`	System I/O	Slow redo log I/O	Move redo to faster storage
`buffer busy waits`	Concurrency	Hot block contention	Check Segments by Buffer Busy Waits
`gc buffer busy acquire`	Cluster (RAC)	Cross-instance block transfer	Check service affinity
`latch: cache buffers chains`	Concurrency	Hot block read by many sessions	Find hot block with ASH
`enq: TX - row lock contention`	Application	Uncommitted row lock	Find blocking session
`read by other session`	User I/O	Hot block + physical I/O	Reduce physical reads
`direct path read`	User I/O	Sort spill, parallel scan	Increase PGA or fix query

DB Time = CPU + Waits

Every performance report (AWR, ASH, Statspack) breaks total DB time into time on CPU and time spent waiting. Waits are grouped into classes: User I/O, System I/O, Concurrency, Commit, Configuration, Network, and Others.

Understanding the class immediately narrows your diagnosis:

User I/O → the problem is in how SQL accesses data (indexes, scans)
Concurrency → sessions are competing for the same resource
Commit → too many commits or slow redo log I/O
Application → locking issues caused by application logic
Cluster → RAC-specific cross-instance traffic

Where to find wait events in your AWR report: - Top 10 Foreground Events by Total Wait Time — the primary section - Wait Classes summary — shows the class breakdown - Event Histogram — shows the distribution of wait times (short vs long waits)

1. `db file sequential read`

Class: User I/O
What it means: A single-block read from datafiles — almost always caused by index lookups or table access by ROWID.

This is the most common top wait event and is often completely healthy. An OLTP database doing thousands of index-driven lookups per second will naturally show high db file sequential read totals.

When to investigate: - It represents more than 40-50% of total DB time - Average wait time is high (> 5-10ms — check your storage baseline) - It's growing compared to a previous baseline AWR

Diagnostic SQL:

-- Find SQL statements driving the most physical reads
SELECT sql_id, executions, disk_reads, disk_reads/NULLIF(executions,0) AS reads_per_exec,
       SUBSTR(sql_text, 1, 80) AS sql_text
FROM   v$sql
ORDER  BY disk_reads DESC
FETCH  FIRST 10 ROWS ONLY;

-- Check buffer cache hit ratio
SELECT 1 - (physical_reads / (consistent_gets + db_block_gets)) AS hit_ratio
FROM   v$buffer_pool_statistics
WHERE  name = 'DEFAULT';

Fixes: - Add or modify indexes to reduce the number of blocks read - Increase the buffer cache (db_cache_size) if the hit ratio is below 95% - Consider result caching for frequently read lookup tables - For range scans, check if a composite index would be more selective

2. `db file scattered read`

Class: User I/O
What it means: Multi-block reads — the signature of full table scans and fast full index scans. Oracle reads multiple blocks in a single I/O, which is why the data is "scattered" across the buffer cache.

When it's acceptable: Large reports or batch jobs that intentionally process most of a table. Full scans on small tables (< few thousand blocks) are also fine.

When it's a problem: OLTP queries showing this event usually have a missing index or a query written in a way that prevents index usage.

Diagnostic SQL:

-- Find tables being full-scanned most often
SELECT o.object_name, o.object_type, s.total_waits, s.time_waited
FROM   v$segment_statistics s
JOIN   dba_objects o ON o.object_id = s.obj#
WHERE  s.statistic_name = 'physical reads direct'
ORDER  BY s.time_waited DESC
FETCH  FIRST 10 ROWS ONLY;

-- Find SQL doing full table scans
SELECT sql_id, executions, 
       ROUND(elapsed_time/1000000, 2) AS elapsed_sec,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sql
WHERE  sql_text LIKE '%TABLE ACCESS FULL%'  -- simplistic, use execution plans
ORDER  BY elapsed_time DESC
FETCH  FIRST 10 ROWS ONLY;

Fixes: - Create an appropriate index for the lookup columns - Fix queries that use functions on indexed columns: WHERE UPPER(name) = 'JOHN' prevents index use; rewrite as WHERE name = 'JOHN' or create a function-based index - Fix implicit type conversions: WHERE numeric_col = '123' (string literal on numeric column) prevents index use - Increase db_file_multiblock_read_count for intentional large scans (improves throughput) - Use parallel query for large batch scans to reduce elapsed time

3. `log file sync`

Class: Commit
What it means: A session issued a COMMIT and is waiting for the Log Writer (LGWR) to flush the redo log buffer to disk and acknowledge. No commit returns to the application until this completes.

This event comes in two flavors with different fixes:

Flavor 1 — High average wait time (> 5ms per wait): The redo log I/O is slow. LGWR is struggling to write to the log files fast enough.

Flavor 2 — Low average wait time but enormous count: The application is committing too frequently — row by row, or after every small operation.

Diagnostic SQL:

-- Check log file sync stats: count vs time
SELECT event, total_waits, time_waited, 
       ROUND(time_waited / NULLIF(total_waits, 0), 2) AS avg_wait_ms
FROM   v$system_event
WHERE  event = 'log file sync'
ORDER  BY time_waited DESC;

-- Check commit frequency
SELECT name, value FROM v$sysstat 
WHERE name IN ('user commits', 'user rollbacks', 'redo writes', 'redo blocks written');

-- Find sessions committing most frequently
SELECT s.sid, s.username, s.program, st.value AS commits
FROM   v$sesstat st
JOIN   v$session s ON s.sid = st.sid
JOIN   v$statname sn ON sn.statistic# = st.statistic#
WHERE  sn.name = 'user commits'
ORDER  BY st.value DESC
FETCH  FIRST 10 ROWS ONLY;

Fixes: - Slow I/O: Move redo logs to dedicated, fast storage (NVMe SSD ideally). Never share spindles with datafiles. Use striped storage. - Too many commits: Batch commits in the application — commit every 1000 rows instead of every row. Check for "autocommit" mode in application frameworks. - On non-critical data: Consider NOLOGGING for bulk loads (not for regular DML in production). - Increase the number and size of redo log groups if they are switching too frequently.

4. `log file parallel write`

Class: System I/O
What it means: LGWR itself is waiting to write redo to the log files. This is the internal counterpart of log file sync — where log file sync is what sessions see, log file parallel write is what LGWR sees.

High values here confirm that the redo log I/O path is the bottleneck.

Fixes: Same as log file sync Flavor 1 — faster dedicated storage for redo logs, separate from datafiles.

5. `buffer busy waits`

Class: Concurrency
What it means: A session wants a buffer (block in memory) that another session is currently modifying. The second session has to wait until the first finishes.

This is a hotspot problem. Classic causes: - Many sessions inserting into the same extent of a table (right-hand side inserts into a heap table with a sequence) - Many sessions reading the same index block (right-hand side of a B-tree index fed by a sequence) - A single small lookup table read by every session

Diagnostic SQL:

-- Find the hot objects
SELECT o.object_name, o.object_type, s.total_waits, s.time_waited
FROM   v$segment_statistics s
JOIN   dba_objects o ON o.object_id = s.obj#
WHERE  s.statistic_name = 'buffer busy waits'
ORDER  BY s.time_waited DESC
FETCH  FIRST 10 ROWS ONLY;

-- Find the hot block (requires ASH)
SELECT current_obj#, current_block#, count(*) AS waits
FROM   v$active_session_history
WHERE  event = 'buffer busy waits'
  AND  sample_time > SYSDATE - 1/24
GROUP  BY current_obj#, current_block#
ORDER  BY waits DESC;

Fixes: - For insert hotspots on sequences: Use a hash partition on the target table to spread inserts across partitions. Or use SEQUENCE.CACHE to reduce the frequency of dictionary updates. - For B-tree index right-hand contention: Consider a reverse key index to spread index insertions across leaf blocks. Note: reverse key indexes cannot do range scans. - For frequently read lookup tables: Increase INITRANS on the object to allow more concurrent readers. - Cache small hot tables: ALTER TABLE small_lookup CACHE; pins it in the keep buffer pool.

6. `gc buffer busy acquire` / `gc buffer busy release` (RAC only)

Class: Cluster
What it means: In Oracle RAC, when a node needs a block that another node has modified, it must request it through the Global Cache Service (GCS). A session is waiting to acquire (or release) a block via Cache Fusion.

Diagnostic SQL:

-- Check global cache stats by instance
SELECT inst_id, event, total_waits, time_waited
FROM   gv$system_event
WHERE  event LIKE 'gc buffer busy%'
ORDER  BY inst_id, time_waited DESC;

-- Find the hot objects causing cross-instance traffic
SELECT inst_id, current_obj#, count(*) AS waits
FROM   gv$active_session_history
WHERE  event LIKE 'gc buffer busy%'
GROUP  BY inst_id, current_obj#
ORDER  BY waits DESC;

Fixes: - Service affinity: Route related workload to a single RAC instance so the blocks stay local. Define services that pin specific workloads to specific nodes. - Partitioning: Partition tables so each instance owns its hot partitions. - Review the application: Shared sequences updated by all instances are a common source. Use instance-specific sequences or SEQUENCE.SESSION.

7. `latch: cache buffers chains`

Class: Concurrency
What it means: Latches are lightweight internal locks protecting Oracle's memory structures. The cache buffers chains latch protects the linked list of buffers in the buffer cache. High contention means a very hot block is being accessed by hundreds of sessions simultaneously.

This is distinct from buffer busy waits: buffer busy waits happens when a block is being written, while latch: cache buffers chains happens when a block is being read by too many concurrent sessions.

Diagnostic SQL:

-- Find the hot latch
SELECT addr, latch#, name, gets, misses, sleeps,
       ROUND(misses/NULLIF(gets,0)*100, 2) AS miss_pct
FROM   v$latch_children
WHERE  name = 'cache buffers chains'
ORDER  BY sleeps DESC
FETCH  FIRST 5 ROWS ONLY;

-- Use ASH to find what's being accessed
SELECT sql_id, current_obj#, current_block#, count(*) AS samples
FROM   v$active_session_history
WHERE  event = 'latch: cache buffers chains'
  AND  sample_time > SYSDATE - 1/24
GROUP  BY sql_id, current_obj#, current_block#
ORDER  BY samples DESC;

Fixes: - Find the SQL that generates the hot block access and reduce its frequency - Cache the result at the application level if the block is a small reference table - For lookup tables, consider RESULT_CACHE hint or Oracle's result cache feature - Increase the number of buffer cache latches (not directly configurable, but indirectly by tuning the above)

8. `enq: TX - row lock contention`

Class: Application
What it means: A session is waiting on a row lock held by another session's uncommitted transaction. This is almost always an application-level issue, not a database configuration problem.

Common causes: - Long-running transactions that hold locks - "Hot row" patterns where many sessions update a single shared counter row - Missing COMMIT statements after DML in application code - Deadlocks (Oracle resolves these automatically, but they indicate a design problem)

Diagnostic SQL:

-- Find the blocking session and what it's holding
SELECT l.sid AS blocked_sid, 
       bl.sid AS blocker_sid,
       bl.username AS blocker_user,
       bl.status AS blocker_status,
       bl.sql_id AS blocker_sql_id,
       bl.seconds_in_wait AS seconds_blocked
FROM   v$lock l
JOIN   v$session bl ON bl.sid = l.block
WHERE  l.request > 0;

-- Find the SQL the blocker is running
SELECT sql_text FROM v$sql 
WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = <blocker_sid>);

Fixes: - Shorten transactions: commit as soon as the logical unit of work is complete - Eliminate "hot row" patterns: replace a single counter row with a sequence or partition the counter by session/user - Review application code for missing commits in error handling paths - Set an appropriate LOCK_TIMEOUT at the application level so blocked sessions fail gracefully rather than queuing indefinitely

9. `read by other session`

Class: User I/O
What it means: A session is waiting because another session is already reading the same block from disk into the buffer cache. Oracle avoids reading the same block twice from disk — the second session waits for the first to finish.

This is a combination of a hot block (many sessions want the same data) and physical I/O (the block is not in the buffer cache).

Fixes: - Increase the buffer cache to keep hot blocks in memory, reducing physical reads - Same index and query optimization as db file sequential read - For very small hot tables, use ALTER TABLE t CACHE to prioritize them in the buffer cache

10. `direct path read` / `direct path write`

Class: User I/O
What it means: Reads or writes that bypass the buffer cache entirely. Caused by: - Large parallel full table scans (parallel query reads directly into PGA, not SGA) - Sort and hash join spills to temp tablespace when PGA is insufficient - Direct path loads (INSERT /*+ APPEND */)

Diagnostic SQL:

-- Check temp usage (sort/hash spills)
SELECT s.sid, s.username, u.blocks * 8 / 1024 AS temp_mb, s.sql_id
FROM   v$sort_usage u
JOIN   v$session s ON s.saddr = u.session_addr
ORDER  BY u.blocks DESC;

-- Check PGA usage vs target
SELECT name, value FROM v$pgastat
WHERE name IN ('aggregate PGA target parameter', 
               'aggregate PGA auto target',
               'total PGA used for auto workareas');

Fixes: - For sort/hash spills: Increase PGA_AGGREGATE_TARGET to give sessions more memory for in-memory sorts. Fix the query to process fewer rows (better predicates, better indexes). - For parallel scans: This is often intentional and healthy. If it's excessive, review PARALLEL degree hints and table/index parallel settings. - For temp spills from hash joins: Consider hints (/*+ USE_NL */) to switch to nested loops for smaller result sets, or review if the join order is optimal.

Finding Wait Events with ASH (Active Session History)

For incidents shorter than 30 minutes, ASH is more precise than AWR. ASH samples active sessions every second and stores the data in v$active_session_history (in memory) and dba_hist_active_sess_history (on disk, persisted by AWR).

-- Top wait events in the last hour from ASH
SELECT event, 
       count(*) AS samples,
       ROUND(count(*) * 100 / SUM(count(*)) OVER(), 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 1/24
  AND  session_state = 'WAITING'
GROUP  BY event
ORDER  BY samples DESC
FETCH  FIRST 10 ROWS ONLY;

-- Top SQL by wait time in a specific window
SELECT sql_id,
       count(*) AS samples,
       count(*) * 10 AS approx_seconds  -- each sample = ~1 second
FROM   dba_hist_active_sess_history
WHERE  sample_time BETWEEN TO_DATE('2026-06-20 14:00', 'YYYY-MM-DD HH24:MI')
                       AND TO_DATE('2026-06-20 15:00', 'YYYY-MM-DD HH24:MI')
  AND  session_state = 'WAITING'
GROUP  BY sql_id
ORDER  BY samples DESC
FETCH  FIRST 10 ROWS ONLY;

Use ASH when: - The incident lasted less than 30 minutes - You need to pinpoint which specific SQL or session caused the wait - AWR averages are masking a short spike

A Diagnostic Workflow You Can Reuse

1. Open Top 10 Foreground Events in AWR/ASH
       ↓
2. Take the top 2-3 events by total wait time
   (ignore the long tail — fix the biggest first)
       ↓
3. Identify the class (I/O, Concurrency, Commit, Application)
       ↓
4. Run the diagnostic SQL for that event
       ↓
5. Pivot to Top SQL and Segments sections
   to find the specific statement or object
       ↓
6. Apply the targeted fix
       ↓
7. Re-measure with a fresh AWR/ASH report

Don't optimize multiple wait events simultaneously — fix the top one, re-measure, and let the new top event guide your next step. The second-ranked event often disappears or changes completely once the first is resolved.

Frequently Asked Questions

How many wait events does Oracle have? Thousands. Oracle 19c has over 1,500 named wait events. In practice, you will encounter the same 10-20 events in the vast majority of performance issues. The rest are rare or internal.

What is idle wait event? Idle events (like SQL*Net message from client, jobq slave wait) represent time when a session is doing nothing — waiting for the application to send the next SQL statement. They are excluded from the foreground events section of AWR and should generally be ignored in performance analysis.

How do I know if a wait event is "bad"? Compare against a baseline. A db file sequential read average of 2ms may be normal on spinning disk but high on SSD. Always compare the current AWR report against a "known good" report from the same time window on a normal day.

What is the difference between enq: TX and enq: TM? enq: TX - row lock contention is a row-level lock on DML. enq: TM - contention is a table-level lock, usually caused by DML on a table with a foreign key that is missing an index on the child table — a very common and easily fixed issue.

Can I see historical wait events beyond the AWR retention window? Not from Oracle's built-in tools. If you need longer-term history, consider a third-party monitoring tool that stores snapshots in its own repository, or extend the AWR retention period with DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings.

Let the Analysis Come to You

Correlating the wait profile with the right SQL and segments — and knowing which of two top events is the real driver — is where DBA experience pays off. Every wait event tells a story, but reading that story across dozens of AWR sections takes time.

DBA Copilot automates this correlation: upload your AWR or ASH report and the AI identifies the dominant wait events, ties them to the responsible SQL statements and objects, and proposes prioritised, actionable recommendations — in seconds, without needing direct access to your database.

Try DBA Copilot free — no credit card required

Want to generate an AWR report first? See our guide: How to Generate an Oracle AWR Report

Oracle Wait Events Explained: Top 10 and How to Fix Them

Quick Reference: Top 10 Oracle Wait Events

DB Time = CPU + Waits

1. db file sequential read

2. db file scattered read

3. log file sync

4. log file parallel write

5. buffer busy waits

6. gc buffer busy acquire / gc buffer busy release (RAC only)

7. latch: cache buffers chains

8. enq: TX - row lock contention

9. read by other session

10. direct path read / direct path write

Finding Wait Events with ASH (Active Session History)

A Diagnostic Workflow You Can Reuse

Frequently Asked Questions

Let the Analysis Come to You

Want to analyze your database performance automatically?

1. `db file sequential read`

2. `db file scattered read`

3. `log file sync`

4. `log file parallel write`

5. `buffer busy waits`

6. `gc buffer busy acquire` / `gc buffer busy release` (RAC only)

7. `latch: cache buffers chains`

8. `enq: TX - row lock contention`

9. `read by other session`

10. `direct path read` / `direct path write`