Re: Oracle DB with lots of XA Tx

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 4 Aug 2015 09:56:08 +0200 (CEST)
Message-ID: <1537287791.191651.1438674968655.JavaMail.open-xchange_at_app02.ox.hosteurope.de>

Hi Venkat,
in which function do these sleeps and waits happen? ksupuc -> ktcsbr -> ktcbba?

If yes, then you might be affeced by unpublished bug fix #20130575 (MOS ID #2018260.1), but luckily there is a patch #20130575 (also for 11.2.0.4), which lifts the restriction for getting the latch to a certain point.  

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Venkat Krish <venkat.lear_at_gmail.com> hat am 3. August 2015 um 21:47 geschrieben:
>
> Dear Oracle Smart-ians.
>
> Background:
>
> Running Oracle 11.2.0.4 running on Solaris 10. ( beefy box with 100+ cores & running many dbs in same server..dont ask me why)
>
> Problem:
>
> In one of our critical DBs during peak activity, we see the top activity "latch free" dominated.
>
> top event shows 85% latch free

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class ----------- ----------- -------------- ------------ --------- ----------- latch free 4,884,174 376.5K 77 84.5 Other  

& looking at latch statistics

Latch Name                    Get Requests  Pct Get Miss    Avg Slps /Miss Wait Time (s) NoWait Requests  
----------------------------- ------------  ------------    -------------- ------------- --------------- ----------------
transaction branch allocation   56,706,998         48.17              0.18       376221                0 

we do know, the application might be abusing XA transactions & things could be done better either with more statements in same transaction or trying loosely coupled etc.
But our hands are tied and application cannot be touched.

So the question is..Is there any configuration or parameter that can be tweaked on the db end just to see if it makes reduces the time it spends on "transaction branch allocation".?

Thanks in adv,
Venkat

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 04 2015 - 09:56:08 CEST