Oracle EBS Performance Issue: Inactive Forms Session Holding TX Locks (RCA and Resolution)

In Oracle E-Business Suite (EBS) environments, performance issues are often attributed to high workload or system resource constraints. However, some of the most critical slowdowns originate from less obvious sources — inactive sessions holding uncommitted transactions. This post walks through a real-world production incident where an inactive Oracle Forms session caused cascading blocking across multiple users due to TX row-level locks.

---

Observed Symptoms

• Oracle Forms screens becoming unresponsive in Order Management and Shipping modules
• Concurrent programs stuck in running state
• Increased database wait event: enq: TX - row lock contention

---

Investigation

Analysis of v$session, v$transaction, and dba_wait_chains revealed a single inactive Oracle Forms session (frmweb) holding an active transaction with multiple downstream sessions waiting on TX row lock contention.

-- Identify blocking sessions
SELECT blocking_session, sid, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;

-- Detect inactive sessions with active transactions
SELECT s.sid, s.serial#, s.status, s.program,
       s.username, t.start_time,
       ROUND(s.last_call_et/3600,2) hrs_inactive
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
AND s.status = 'INACTIVE'
ORDER BY hrs_inactive DESC;

-- Analyze full wait chain
SELECT * FROM dba_wait_chains;

The root blocking session showed STATUS = INACTIVE and EVENT = 'SQL*Net message from client' but had an active transaction in v$transaction — confirming it was idle at the application level but actively holding locks at the database level.

---

Root Cause

An Oracle Forms session executed a SELECT ... FOR UPDATE NOWAIT on WSH_DELIVERY_DETAILS, then became idle without committing or rolling back. This held exclusive row locks that blocked other sessions attempting to access the same rows, creating a cascading blocking chain.

-- The problematic SQL pattern
SELECT *
FROM WSH_DELIVERY_DETAILS
WHERE ROWID = :B1
FOR UPDATE NOWAIT;

---

Resolution

The root blocking session was identified, verified to have no active business transactions, approvals were obtained, and the session was terminated:

-- Kill blocking session (only after full validation and approval)
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

Locks were released immediately, the blocking chain resolved, and application responsiveness was restored.

---

Preventive Measures

• Implement idle session timeout policies
• Educate users on proper transaction handling in Oracle Forms
• Review custom code using FOR UPDATE — keep transactions short and commit promptly
• Monitor long-running and idle transactions proactively

---

Key Takeaways

• An inactive session can still hold active transactions and critical locks
• Always identify the root blocker — intermediate sessions are symptoms, not the cause
• Application-level inactivity does not mean database-level inactivity
• In Oracle EBS, the most disruptive issues are often caused by inactive sessions holding uncommitted transactions

---

Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
Connect: sdanwarahmed@gmail.com  |  LinkedIn