Killed Session Are Not Cleaned By PMON

In this Document

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

Due to application design, database has some inactive sessions remaining in database. When these sessions are killed manually in the database using alter system and killed at OS level using kill -9 as well , sessions still remain in database (v$session) . OS session goes away but database session does not.

Patch 16494960 does not resolve the problem.

CAUSE

The session being killed is part of a global transaction with other related sessions still alive (marked for KILL or showing session marked for KILLED)
 
As per output from:
select taddr, status, sid, serial#, LAST_CALL_ET from v$session
 where taddr in
  ( select taddr from v$session where status='KILLED' )
 order by 1,2 desc ,3
;
 

SOLUTION

> Query to identify different branches and related SIDs of an XA transaction

-> You will find example SQLs in note 1248848.1 and note 332326.1

  For attached transaction branches all sessions attached to the same transaction in tightly coupled mode will have the same "V$SESSION.TADDR" so for this scenario you can use something like this:
  select xidusn||'.'||xidslot||'.'||xidsqn XID,
  sid,serial#,username, s.status
  from v$session s , v$transaction t
  where s.taddr=t.addr
  order by 1,2;

  However once you get detached branches things can get more complicated. The "get_xa_status.sql" in doc 1248848.1 gives a reasonable basis for finding information.

-> When you have tightly coupled branches of a global transaction then resources may be owned by the transaction itself, not directly by the session. Killing just one session of a multi-branch transaction does not remove the other branches / sessions. If the other session makes a call it should get an error to tell it that some other branch of the transaction failed.
  ie: killing just one session of a multi-branch transaction cannot release resources owned by the transaction itself.
  Those resources are only freed when the transaction itself is cleaned up - ie when the last session/branch completes (or is killed).
 

REFERENCES

NOTE:16494960.8 - Bug 16494960 - killed session not being cleaned up
NOTE:332326.1 - How to identify a session started by a remote distributed transaction?

NOTE:274216.1 - Removing Sessions in Killed Status on Unix
NOTE:752676.1 - Session Marked As Killed But Continues To Consume 100% CPU
NOTE:159377.1 - How to Purge a Distributed Transaction from a Database
NOTE:1248848.1 - Solving locking problems in a XA environment