Oracle Wait Events Explained: A Complete Guide to Oracle Performance Tuning

 Introduction

Performance tuning is one of the most important responsibilities of an Oracle Database Administrator. When users complain that “the database is slow,” the first question should not be “Which SQL is slow?” but rather “What is Oracle waiting for?”

Oracle answers this through Wait Events.

Every session spends time either:

  • Using the CPU
  • Waiting for a resource

By analyzing wait events, DBAs can identify whether the bottleneck is related to storage, CPU, locks, memory, SQL execution, Oracle RAC, or application design.

This article explains Oracle Wait Events, how to analyze them, common wait events, root causes, and proven techniques to resolve performance issues.


What Are Oracle Wait Events?

A wait event represents the time a database session spends waiting for a required resource.

Resources include:

  • Data blocks
  • Redo logs
  • Memory
  • Locks
  • Latches
  • Network
  • Disk I/O
  • RAC Cache Fusion

Oracle records these waits internally, allowing DBAs to understand exactly where time is being spent.


Why Wait Event Analysis Matters

Instead of guessing why performance is poor, wait event analysis helps you:

  • Identify the real bottleneck
  • Reduce troubleshooting time
  • Improve SQL performance
  • Optimize application response
  • Increase throughput
  • Prevent recurring issues

Remember:

A wait event is usually a symptom, not the root cause.


Oracle Wait Event Categories

1. User I/O Waits

Examples:

  • db file sequential read
  • db file scattered read
  • direct path read
  • direct path write

Usually related to storage performance or SQL execution plans.


2. Commit Waits

Examples:

log file sync
log file parallel write

Usually caused by:

  • Frequent commits
  • Slow redo storage

3. Concurrency Waits

Examples:

buffer busy waits
enq: TX - row lock contention
latch free

Usually indicate contention between sessions.


4. Cluster Waits (Oracle RAC)

Examples:

gc current request
gc cr request
gc buffer busy acquire
gc buffer busy release

Usually caused by Cache Fusion block transfers.


5. Configuration Waits

Examples:

  • Shared Pool issues
  • Undo shortages
  • Temporary tablespace limitations


How to Analyze Wait Events

Step 1 – Review AWR

Open the AWR report.

Navigate to:

Top Timed Events

This immediately shows where Oracle spends most of its time.


Step 2 – Query Current Waits


SELECT event,
       COUNT(*)
FROM gv$session
WHERE state='WAITING'
GROUP BY event
ORDER BY COUNT(*) DESC;


Step 3 – Review ASH

ASH provides real-time wait information.

Useful query:

SELECT sql_id,
       event,
       COUNT(*)
FROM gv$active_session_history
GROUP BY sql_id,event
ORDER BY COUNT(*) DESC;

Step 4 – Identify the SQL


SELECT sql_id,
       sql_text
FROM gv$sql
WHERE sql_id='&SQL_ID';

Step 5 – Check Execution Plan

Never tune the wait event before understanding the SQL execution plan.

Use:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

Common Wait Events and Their Solutions


⸻


1. db file sequential read


Meaning


Single-block reads.


Usually caused by index lookups.


Root Cause


  • Missing indexes
  • Poor SQL
  • Slow storage


Fix


✔ Create appropriate indexes


✔ Tune SQL


✔ Verify storage latency


⸻


2. db file scattered read


Meaning


Multi-block reads.


Usually Full Table Scans.


Fix


  • Improve indexing
  • Update statistics
  • Reduce unnecessary scans


⸻


3. log file sync


Meaning


Sessions waiting for COMMIT.


Root Cause


  • Frequent commits
  • Slow redo logs


Fix


✔ Reduce commit frequency


✔ Place redo logs on fast SSD/NVMe


✔ Increase redo log size if frequent switches occur


⸻


4. log file parallel write


Meaning


LGWR writing redo logs.


Fix


  • Faster storage
  • Separate redo disks
  • Verify I/O subsystem


⸻


5. buffer busy waits


Meaning


Sessions waiting for the same data block.


Fix


✔ Increase INITRANS


✔ Partition large tables


✔ Reduce concurrent updates


✔ Review hot blocks


⸻


6. enq: TX - row lock contention


Meaning


Row-level locking.


Root Cause


Multiple sessions updating the same rows.


Fix


  • Identify blocking session
SELECT blocking_session,
       sid,
       event
FROM gv$session;

  • Commit faster
  • Reduce long-running transactions


7. latch free

Meaning

Latch contention.

Fix

  • Reduce hard parsing
  • Use bind variables
  • Tune shared pool


8. library cache lock

Meaning

Waiting for library cache objects.

Fix

  • Avoid excessive object recompilation
  • Reduce DDL during business hours
  • Use bind variables


9. gc current request (Oracle RAC)

Meaning

Current block requested from another RAC node.

Fix

✔ Reduce hot blocks

✔ Reverse Key Indexes

✔ Partition tables

✔ RAC Services


10. gc cr request

Meaning

Consistent Read block requested.

Fix

  • Improve SQL efficiency
  • Reduce cross-instance reads
  • Optimize workload distribution


11. gc buffer busy acquire

Meaning

Waiting for a busy RAC block.

Fix

  • Identify hot objects
  • Reverse Key Index
  • Increase INITRANS
  • Partition tables


12. read by other session

Meaning

Another session is already reading the required block.

Fix

  • Tune SQL
  • Improve indexing
  • Reduce simultaneous scans


Useful Monitoring Queries

Top Wait Events

SELECT event,
       total_waits,
       time_waited
FROM v$system_event
ORDER BY time_waited DESC;


Active Waiting Sessions


SELECT sid,
       serial#,
       sql_id,
       event
FROM gv$session
WHERE state='WAITING';

Top SQL by Wait Time


SELECT sql_id,
       elapsed_time,
       executions
FROM v$sql
ORDER BY elapsed_time DESC;


Top Wait Events in ASH


SELECT event,
       COUNT(*)
FROM gv$active_session_history
GROUP BY event
ORDER BY COUNT(*) DESC;

Oracle Performance Tuning Workflow


User reports slow application
           │
           ▼
Collect AWR / ASH
           │
           ▼
Identify Top Wait Event
           │
           ▼
Find SQL_ID
           │
           ▼
Review Execution Plan
           │
           ▼
Check Objects / Indexes
           │
           ▼
Identify Root Cause
           │
           ▼
Apply Fix
           │
           ▼
Validate with AWR

Oracle Performance Tuning Best Practices

✔ Keep Optimizer Statistics up to date.

✔ Monitor AWR regularly.

✔ Use ASH for real-time troubleshooting.

✔ Tune SQL before modifying database parameters.

✔ Avoid unnecessary Full Table Scans.

✔ Use bind variables.

✔ Monitor top wait events daily.

✔ Design indexes based on workload.

✔ Keep transactions short.

✔ Validate every tuning change.


Real Production Scenario

An e-commerce application experienced intermittent slow response times during peak sales.

Investigation

  • AWR showed log file sync as the top wait event.
  • ASH identified frequent commits from the order processing module.
  • Redo logs were stored on shared storage with high latency.

Resolution

  • Batched commit operations instead of committing every row.
  • Moved redo logs to dedicated NVMe storage.
  • Increased redo log file size to reduce log switches.

Outcome

  • Average commit latency dropped significantly.
  • Database throughput increased.
  • End-user response time improved during peak traffic.


Conclusion

Oracle Wait Events provide one of the most accurate ways to diagnose database performance issues. Instead of focusing on symptoms, DBAs should use wait events to identify the underlying bottleneck—whether it is SQL, storage, locking, RAC, or application design.

By combining AWR, ASH, Execution Plans, and dynamic performance views, you can systematically identify root causes and apply targeted solutions rather than relying on guesswork.




Comments

Post a Comment