Oracle DBA

In Oracle Cloud Infrastructure (OCI), Cloud Ops DBAs often face performance issues caused by blocking sessions. A blocking session occurs when one session holds a lock on a resource and other sessions wait for that lock to be released. In cloud-hosted databases, blocking can cause query slowness, application timeouts, and even outages if not addressed quickly.

This blog explains how to identify blocking sessions in Oracle Cloud Databases and how to terminate them safely.

Step 1: Identify Blocking Sessions

The first step is to find which sessions are blocking others. Use the following SQL query:

SET LINESIZE 200
SET PAGESIZE 100

SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.sql_id,
       s.blocking_session,
       s.event,
       s.seconds_in_wait
FROM   v$session s
WHERE  blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

Sample output

 SID SERIAL# USERNAME PROGRAM           SQL_ID    BLOCKING_SESSION EVENT                          SECONDS_IN_WAIT
---- ------- -------- ---------------- --------- ---------------- ------------------------------ ---------------
 345   11234 HR       JDBC Thin Client 9k3y7z2p              123 enq: TX - row lock contention              890
 567   21345 APPUSER  sqlplus@dbhost01 8h2x9k1m              123 enq: TX - row lock contention              450

In this output, sessions 345 and 567 are waiting because they are blocked by session 123.

Step 2: Check the SQL Executed by the Blocking Session

To see what the blocking session is running, use the sql_id:

SELECT sql_id, sql_text
FROM   v$sql
WHERE  sql_id = '8h2x9k1m';

Sample output

SQL_ID    SQL_TEXT
--------- ---------------------------------------------------------------------
8h2x9k1m  UPDATE employees SET salary = salary * 1.1 WHERE department_id = 90

This shows that the blocking session is performing an update on the employees table.

Step 3: Kill the Blocking Session

If the blocking session is causing critical impact, you may need to terminate it. First, identify the SID and SERIAL# of the blocking session:

SELECT sid, serial#, username, program
FROM   v$session
WHERE  sid = 123;

Sample output

 SID SERIAL# USERNAME PROGRAM
---- ------- -------- -------------------
 123   56789 HR       JDBC Thin Client

Kill the session with the following command:

ALTER SYSTEM KILL SESSION '123,56789' IMMEDIATE;

Sample output

System altered.

This terminates the blocking session immediately, releasing the locks and allowing waiting sessions to proceed.

Step 4: Automating Blocking Session Detection

For routine checks, you can schedule a query to detect blocking sessions and send alerts. Example query:

SELECT blocking_session, COUNT(*) AS blocked_sessions
FROM   v$session
WHERE  blocking_session IS NOT NULL
GROUP BY blocking_session;

Sample output

BLOCKING_SESSION BLOCKED_SESSIONS
---------------- ----------------
             123                5

This shows that session 123 is blocking five other sessions.

Step 5: Best Practices for Cloud Ops DBAs

1. Always analyze the SQL of the blocking session before killing it.
2. Communicate with the application team if possible, since killing sessions may rollback transactions.
3. Automate monitoring for blocking sessions using scripts integrated with OCI CLI or cloud monitoring tools.
4. Work with developers to optimize application logic and reduce locking contention.

Conclusion

Blocking sessions are a common source of performance issues in Oracle Cloud Databases. By identifying blocking sessions through v$session, analyzing their SQL with v$sql, and carefully terminating them when necessary, Cloud Ops DBAs can restore performance quickly. Combining proactive monitoring with OCI tools ensures that blocking does not disrupt critical workloads.