What might cause Inactive Transaction Branch waits in a database without any db links?

From: <"">
Date: Fri, 6 Mar 2020 20:09:09 +0000
Message-ID: <0D8F4CAC0F9D3C4AACC63F50FD9957F762FAD617_at_PRDTXWPEMLMB32.prod-am.ameritrade.com>

I was looking into an unrelated issue on one of our dbs and I saw that it has been experiencing a high level of Inactive Transaction Branch waits. I started researching but the database has no db links and every reference I found indicated that it would be related to a distributed transaction over a db link.

While it doesn't seem to be causing noticeable performance issues for the app I would like to figure out what's going on

Checking p1 and p1text in v$session_wait for sessions experiencing the wait show branch#,0
From what I've read the 0 value should have been the serial# of the session causing this session to wait. But it obviously isn't.

Looking at the session in OEM the wait event shows a blank sql id and SQL Command = UNKNOWN. I ran a trace on a session but couldn’t find it associated with any specific sql there either.

Looking at an AWR report it's the top wait event:

Top 10 Foreground Events by Total Wait Time

                                           Total Wait       Wait   % DB Wait
Event                                Waits Time (sec)    Avg(ms)   time Class

------------------------------ ----------- ---------- ---------- ------ --------
inactive transaction branch 23,881 27.9K 1169.16 56.4 Other log file switch (checkpoint in 9,045 8493.7 939.05 17.2 Configur DB CPU 8354.9 16.9 db file sequential read 3,185,440 1081.5 0.34 2.2 User I/O log file sync 285,783 793.3 2.78 1.6 Commit enq: TX - row lock contention 10,605 736.9 69.49 1.5 Applicat direct path read temp 617,178 561.8 0.91 1.1 User I/O direct path write temp 406,728 520.6 1.28 1.1 User I/O buffer busy waits 9,150 472.6 51.65 1.0 Concurre direct path read 527,903 355 0.67 .7 User I/O

Anyone have ideas or advice on how to track down what is causing this?

Jay Miller

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 06 2020 - 21:09:09 CET

Original text of this message