🧩 How to Monitor Session Waits in Oracle Database

🧩 Introduction

Performance problems in Oracle databases are often related to sessions waiting for resources. These waits could be caused by I/O, locks, latches, or other system bottlenecks. As a DBA, monitoring session waits helps you quickly identify where the database is slowing down and what corrective action is needed.

Step 1: Check Active Session Waits

Use the following query to find current waits for active sessions:

SELECT sid, serial#, username, event, wait_class, state, seconds_in_wait

FROM v$session

WHERE state = 'WAITING'

ORDER BY seconds_in_wait DESC;

➡️ This query shows which sessions are currently waiting, what they are waiting for, and for how long.

Step 2: Find Top Wait Events

To get an overview of the most common wait events:

SELECT event, total_waits, time_waited, average_wait

FROM v$system_event

ORDER BY time_waited DESC;

➡️ This helps you identify the top resource bottlenecks in your database.

Step 3: Monitor Session Wait History

For historical session waits:

SELECT sid, event, wait_time, seconds_in_wait

FROM v$session_wait;

➡️ Useful for troubleshooting intermittent or recurring performance issues.

Step 4: Use Automatic Workload Repository (AWR)

If AWR is enabled, generate a report to see wait event trends over time:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

➡️ AWR provides detailed insights into session waits, resource usage, and performance patterns.

Conclusion

Monitoring session waits is essential for diagnosing Oracle performance issues. By regularly reviewing active waits, top events, and historical patterns, DBAs can quickly pinpoint bottlenecks and ensure the database runs efficiently.

---