ANBOB™

    the wait event “inactive transaction branch” are related to externally  coordinated transaction branches, typically from XA clients.
A wait on this event typically means that the XA client/s have  asked different sessions to work on the same transaction  branch concurrently.

eg:   Session 1 starts a transaction TX branch B1
session 1 attaches to TX B1 and does some work
Concurrently session 2 asks to attach to TX B1
– session 2 cannot attach to TX B1 as it is
still attached to session 1 so session 2
waits on “inactive transaction branch”
Usually the steps would either be serialized by the  clients OR would be using separate transaction branches

You should check the client application logic for concurrent operations issued  on the same transaction branch

Diag Method

— identify SQL text

set long 999999
select sql_fulltext
from v$sqlstats
where sql_id='35y99s5k1rjh0';

— Identify trace

select p.spid, p.tracefile
from v$session s, v$process p
where s.sid=1551
and s.paddr=p.addr;

— Collect  call stack

SQL> oradebug setospid <v$process pid>

SQL> oradebug dump errorstack 3;
--
SQL> oradebug short_stack;
SQL> oradebug tracefile_name

— Collect systemstate and hanganalyze

conn / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
-- take another systemstate after 1 minute

--oradebug -g all dump systemstate 266
oradebug hanganalyze 3
-- take another hanganalyze after 2 minutes

oradebug close_trace
oradebug flush

— global transaction id and transaction branch information

oradebug setospid xxx
oradebug tracefile_name
oradebug event 24793 trace name context forever, level 48
oradebug event 24793 trace name context off

Known BUGs
Query Over DBLINK Waiting For “‘inactive transaction branch” (ID 2060008.1)

Solution:

select spid from v$process where addr in (select paddr from v$session where sid=<session_id>);
kill -9 <process_id>

References
MOS 1983151.1 21354456.8

打赏

微信扫一扫，打赏作者吧～