resources

Find Blocking Sessions in Oracle Database 19c

To find blocking sessions in Oracle Database 19c, query GV$SESSION while the slowdown is happening. A waiting row with BLOCKING_SESSION_STATUS = 'VALID' identifies its immediate blocker; the final-blocker columns can point to the end of a longer wait chain. On Oracle RAC, use both the instance number and session ID to look up the blocker.

Before you run the query

Use a SQL client and an account with approved read access to GV$SESSION. You can use SQL*Plus with Oracle Instant Client to connect. For a PDB issue, start with the affected PDB service. If you need a root-level view, confirm with your DBA which containers your monitoring account can see. Oracle documents how container data and the instance identifier affect dynamic performance views. The query uses current session data; it does not require querying AWR or ASH.

1. Capture the sessions that are waiting

SELECT inst_id, con_id, sid, serial#,
       event, ROUND(wait_time_micro / 1000000, 2) AS wait_seconds,
       blocking_session_status,
       blocking_instance, blocking_session,
       final_blocking_session_status,
       final_blocking_instance, final_blocking_session
FROM   gv$session
WHERE  state = 'WAITING'
AND    (blocking_session_status = 'VALID'
        OR final_blocking_session_status = 'VALID')
ORDER  BY wait_time_micro DESC;

Each row is a waiter. Read BLOCKING_INSTANCE and BLOCKING_SESSION only when their status is VALID, and apply the same rule to the final blocker. If several waiters point to the same final blocker, start there. In a cycle, Oracle can choose one session as the final blocker; the result does not establish which session caused the cycle. See the 19c session column definitions for the status and wait-time values.

2. Look up the blocker on the right instance

Use the instance and session numbers from the matching column pair. This SQL*Plus example prompts for both values. Enter the numeric values you just observed.

SELECT inst_id, con_id, sid, serial#,
       username, status, module, sql_id, prev_sql_id
FROM   gv$session
WHERE  inst_id = &blocker_instance
AND    sid = &blocker_sid;

Record SERIAL# with the instance and SID so you can distinguish a later session that reuses the SID. The blocker can be inactive; the current or previous SQL ID is a clue, not proof of which statement acquired the lock. Correlate the observation with the application owner’s transaction logs before deciding how to resolve it.

3. Verify the result before acting

Run the first query again while the affected request is still slow. If the lookup found no blocker, the session might have ended or be outside your visible containers. If the first query returned no rows, it found no current wait with a known blocker in that sample. It can miss a brief wait or one whose blocker status is UNKNOWN. Capture timestamps and wait events while the symptom is present, and refer to Oracle’s guide to interpreting wait events before investigating another cause.

Share the waiter, blocker, container and timestamps with the owner of the affected workload. Avoid ending a session based on one sample. When the owner resolves the transaction, check that the wait disappears and the original request recovers. If the request is still slow, continue with a broader Oracle performance investigation.

If you want to see why a session can wait on an uncommitted change, start with the chapters on locking and concurrency in Expert Oracle Database Architecture. Try the examples in a test schema and compare the waiting session with the blocker before and after the transaction ends.

Oracle database blocking session illustration
Article illustration
Expert Oracle Database Architecture, 4th Edition
Darl Kuhn and Thomas Kyte, Apress
View on Amazon (paid link)