Wait Events, Blocking Sessions and Locks

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Intermediate
Wait Events, Blocking Sessions and Locks
Intermediate Topic 5 of 7

What is a Wait Event?

When a session is not on the CPU, it is waiting for something: a disk read, a lock, a network message. Oracle names each reason a wait event. 'The database is slow' almost always means sessions are waiting on a particular event.

What Are Sessions Doing Right Now?

sql SELECT sid, serial#, username, status, event, wait_class, seconds_in_wait, sql_id FROM v$session WHERE type = 'USER' AND status = 'ACTIVE' ORDER BY seconds_in_wait DESC;
Wait classMeaning
User I/OReading from disk
CommitWaiting for log writes
ConcurrencyWaiting for other sessions (latches, library cache)
ApplicationRow locks, usually caused by the application
IdleNot a problem. The session is just waiting for work.

Row Locks

When a session changes a row, it locks the row until it commits or rolls back. A second session trying to change the same row must wait. This is normal. It becomes a problem when the first session forgets to commit.

Find Who is Blocking Whom

sql SELECT s.sid, s.serial#, s.username, s.blocking_session AS blocker_sid, s.event, s.seconds_in_wait, s.sql_id FROM v$session s WHERE s.blocking_session IS NOT NULL;
Result

Each row is a waiting session. blocker_sid is the session at the head of the queue. Look at what the blocker is doing.

sql -- details of the blocker SELECT sid, serial#, username, status, machine, program, logon_time, sql_id, prev_sql_id FROM v$session WHERE sid = 123;
Idle blocker: A common cause is a blocker with status = INACTIVE. The user ran an UPDATE, did not commit and went to lunch. Ask them to commit or roll back.

Reproduce It Yourself

  1. Session 1: UPDATE shop.products SET price = 1 WHERE product_id = 1; (no commit)
  2. Session 2: run the same UPDATE. It hangs.
  3. Session 3 (DBA): run the blocking query above.

Kill a Session (Last Resort)

sql ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;

The two numbers are sid and serial# from V$SESSION. Oracle rolls back the session's uncommitted work, which can take time if it changed a lot.

Think first: Killing a session loses the user's work. Try to contact the user first. Never kill background processes or sessions you do not understand.

Deadlocks

A deadlock happens when session A waits for a row held by B while B waits for a row held by A. Oracle detects it within seconds and raises ORA-00060 in one session, rolling back that statement. The cause is the application: both should update rows in the same order.

Other Wait Events You Will See

EventWhat to check
library cache lock / latchHard parsing, missing bind variables
buffer busy waitsMany sessions on the same hot block
log file syncCommit frequency, redo disk speed
direct path readLarge full scans bypassing the cache
sql -- system-wide totals since startup SELECT event, total_waits, ROUND(time_waited_micro/1e6) AS secs FROM v$system_event WHERE wait_class <> 'Idle' ORDER BY time_waited_micro DESC FETCH FIRST 10 ROWS ONLY;

Conclusion

Find waiting sessions in V$SESSION, follow blocking_session to the head of the queue, ask the owner to commit and kill only as a last step.

Quick Summary: Row locks are normal; forgotten commits are the problem. blocking_session shows the culprit.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators