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

From: <"">
Date: Mon, 9 Mar 2020 21:34:42 +0000
Message-ID: <0D8F4CAC0F9D3C4AACC63F50FD9957F762FAF4F5_at_PRDTXWPEMLMB32.prod-am.ameritrade.com>

Thanks, I'll take a look at the log files (though as I mentioned, the performance seems fine currently).

Is there any way other than asking the developers to know whether XA transactions are being used?

Jay Miller
Sr. Oracle DBA
201.369.8355

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Sunday, March 08, 2020 8:50 AM
To: Oracle L
Subject: Re: What might cause Inactive Transaction Branch waits in a database without any db links?

I agree about the XA.

I think the last time I saw this it was something to do with Hibernate being configured to assume that distributed transactions might be needed - so used XA; then with the connection pool in the middle a hibernate session would pick a second session from the connection pool and get into conflict with itself.

I note you spend a lot of time on checkpoint incomplete - are your log files a bit small ?

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Martin Berger <martin.a.berger_at_gmail.com> Sent: 08 March 2020 11:12
To: dmarc-noreply_at_freelists.org
Cc: Oracle-L oracle-l
Subject: Re: What might cause Inactive Transaction Branch waits in a database without any db links?

Hi Jay,

Do you have any application using XA transactions? ( https://urldefense.proofpoint.com/v2/url?u=https-3A__en.m.wikipedia.org_wiki_X_Open-5FXA&d=DwIGaQ&c=nulvIAQnC0yOOjC0e0NVa8TOcyq9jNhjZ156R-JJU10&r=aiKV3Uv2Wo7GqYQcis9TSvB1MZslPOnintrOY1rjG58&m=Yo64HmyEwqnQ3j90arLu_D3j5YR1_kWE8udUv4oI1rU&s=VkrS4Z8WTMK0RblZApgHvGidBFBf7qesJm43Vdp4ji4&e= ). These can cause inactive transaction branch waits.

hth,
 Martin

^∆x

Redacted sender Jay.Miller for DMARC <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> schrieb am Fr., 6. März 2020, 21:10: 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?

TIA,
Jay Miller
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Mon Mar 09 2020 - 22:34:42 CET