At any moment, an Oracle session is either working on the CPU or waiting for something: a row lock, a disk read, a commit to be written, or simply the next request from its client. Oracle names every wait with a wait event and records it in V$SESSION, so a session that seems to hang can be checked in seconds. This guide runs three sessions, one holding a row lock, one waiting for it, and one busy on the CPU, and reads their states from V$SESSION, V$SQL, and V$SESSION_EVENT.
What You Need
Querying V$SESSION and the other V$ views needs the SELECT_CATALOG_ROLE role or SELECT privileges on the views. The examples set each session's module with DBMS_APPLICATION_INFO, so the sessions are easy to find.
Sample Table and Sessions
Example:
create table demo_seats (seat varchar2(4) primary key, passenger varchar2(30));
insert into demo_seats values ('12A', null);
insert into demo_seats values ('12B', null);
commit;Output:
Table DEMO_SEATS created. 1 row inserted. 1 row inserted. Commit complete.
Three sessions start a few seconds apart. The first updates seat 12A and then sleeps, keeping its transaction open:
Example:
-- session 1: updates a row and then sleeps, keeping the row locked
exec dbms_application_info.set_module('DEMO_HOLDER', null)
update demo_seats set passenger = 'Ana' where seat = '12A';
exec dbms_session.sleep(45)
rollback;The second tries to update the same row:
Example:
-- session 2: wants the same row
exec dbms_application_info.set_module('DEMO_WAITER', null)
update demo_seats set passenger = 'Leo' where seat = '12A';
rollback;The third keeps the CPU busy for 25 seconds:
Example:
-- session 3: busy on the CPU for 25 seconds
exec dbms_application_info.set_module('DEMO_WORKER', null)
declare
l_end number := dbms_utility.get_time + 2500;
l_x number := 0;
begin
while dbms_utility.get_time < l_end loop
l_x := mod(l_x + sqrt(l_x + 1), 1000);
end loop;
end;
/What Each Session Is Doing
A fourth session looks at the three while they run:
Example:
-- what each session is doing right now
select sid, module, status, state, event, wait_class,
round(wait_time_micro / 1e6, 1) as seconds, blocking_session as blocker
from v$session
where module like 'DEMO_%'
order by module;Output:
SID MODULE STATUS STATE EVENT WAIT_CLASS SECONDS BLOCKER
______ ______________ _________ ____________________ ________________________________ ______________ __________ __________
221 DEMO_HOLDER ACTIVE WAITING PL/SQL lock timer Idle 17.8
22 DEMO_WAITER ACTIVE WAITING enq: TX - row lock contention Application 12.6 221
222 DEMO_WORKER ACTIVE WAITED KNOWN TIME SQL*Net message from client Idle 0- The holder is WAITING on PL/SQL lock timer, the event of DBMS_SESSION.SLEEP, in the Idle class. It is not blocked by anything; it just holds its lock while it sleeps.
- The waiter is WAITING on enq: TX - row lock contention, in the Application class, for 12.6 seconds, and BLOCKING_SESSION points to the holder, SID 221.
- The worker is not waiting at all. When STATE is anything other than WAITING, the session is on the CPU, and EVENT only names its last wait, which here is an old SQL*Net message from client. Read the event only when STATE is WAITING.
The Statement Behind the Wait
SQL_ID leads to the statement each session is running:
Example:
-- the statement each session is running select s.module, s.sql_id, substr(q.sql_text, 1, 55) as sql_text from v$session s left join v$sql q on q.sql_id = s.sql_id and q.child_number = s.sql_child_number where s.module like 'DEMO_%' order by s.module;
Output:
MODULE SQL_ID SQL_TEXT ______________ ________________ __________________________________________________________ DEMO_HOLDER at6h1vp8gv8j8 BEGIN dbms_session.sleep(45); END; DEMO_WAITER csd3h2sjb7u6c update demo_seats set passenger = 'Leo' where seat = '1 DEMO_WORKER g9fvy99gk8shm declare l_end number := dbms_utility.get_time + 2500;
The waiter's UPDATE on DEMO_SEATS is the statement held up by the row lock. The holder's current statement is the sleep; the UPDATE that took the lock ran earlier, so it no longer appears as its SQL_ID.
A Session's Waits So Far
V$SESSION_EVENT adds up every wait of a session since it connected:
Example:
-- the waiting session's wait events so far select event, total_waits, round(time_waited_micro / 1e6, 1) as seconds, wait_class from v$session_event where sid = (select sid from v$session where module = 'DEMO_WAITER') order by time_waited_micro desc fetch first 5 rows only;
Output:
EVENT TOTAL_WAITS SECONDS WAIT_CLASS ________________________________ ______________ __________ ______________ enq: TX - row lock contention 1 15 Application SQL*Net message from client 73 0.6 Idle log file sync 1 0 Commit events in waitclass Other 67 0 Other SQL*Net message to client 73 0 Network
The waiter has spent 15 seconds on one row lock wait, and only fractions of a second on everything else. For a session that was slow overall, this shows where its time went.
When the holder finally rolled back, the waiter got the row at once:
Output:
PL/SQL procedure successfully completed. 1 row updated. Rollback complete.
Common Wait Events
- enq: TX - row lock contention: waiting for a row locked by another transaction.
- db file sequential read and db file scattered read: single-block reads, typically through indexes, and multi-block reads, typically for full scans.
- log file sync: waiting for a COMMIT to be written to the redo log.
- direct path read temp and direct path write temp: sorts and hash joins that spill to the temporary tablespace.
- SQL*Net message from client: the session is idle, waiting for its application to send the next call.
Things to Know
- Idle class waits, such as SQL*Net message from client, mean the database is not the bottleneck; the time is spent in the application or the network.
- V$SESSION shows only the current moment. For waits in the past, Active Session History keeps samples, where licensed.
- Set the module and action in applications with DBMS_APPLICATION_INFO; it makes sessions identifiable in all these views.
Related Guides
- How to Fix ORA-00054: Resource Busy and Acquire with NOWAIT Specified or Timeout Expired in Oracle
- How to Track Sessions with DBMS_APPLICATION_INFO
Conclusion
V$SESSION shows for every session whether it is waiting and on what: STATE, EVENT, WAIT_CLASS, the time waited, and the blocking session. Read EVENT only when STATE is WAITING, follow SQL_ID to the statement, and use V$SESSION_EVENT to see where a session's time went overall.
