Re: Oracle DB with lots of XA Tx

From: Venkat Krish <venkat.lear_at_gmail.com>
Date: Tue, 4 Aug 2015 17:54:45 -0400
Message-ID: <CAOV8h7v-Ty92tUt65=oubCPBKzHTw0NL+1ODyMyBuLb3JS4SOQ_at_mail.gmail.com>

Spot on Stefan. Thanks much. I swear I had put in enough searches in MOS before..Fact is, I even had a SR created on this same topic a few months back & that was eventually closed by an "expert" stating the latch free waits looked very normal & nothing could be done.

I will def'tly give this patch a try. Thanks again.

Regards
Venkat

& the pattern looks ditto the same..Awr details below

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
> ----------- ----------- -------------- ------------ ---------
> -----------
> latch free 4,884,174 376.5K 77 84.5 Other
> -- top latch stats
> Latch Name Get Requests Pct Get Miss Avg Slps /Miss
> Wait Time (s) NoWait Requests Pct NoWait Miss
> ----------------------------- ------------ ------------ --------------
> ------------- --------------- ----------------
> transaction branch allocation 56,706,998 48.17 0.18
> 376221 0

>


> -- top Latch Sleep

> Latch Name                    Get Requests  Misses     Sleeps    Spin Gets

> ----------------------------- ------------  ---------- --------  ----------

> transaction branch allocation 56,706,998 27,318,070 5,004,635
> 22,676,899 .
> --latch miss sources
> Latch Name Where "NoWait Misses" Sleeps "Waiter
> Sleeps"
> ---------------------------- ------ --------------- ---------
> --------------
> transaction branch allocation ksupuc 0 3,414,627 3,932,743
> transaction branch allocation ktcsbr 0 715,952 390,057
> transaction branch allocation ktcdbr 0 422,407 199,934
> transaction branch allocation ktcbba 0 238,800 312,285
> transaction branch allocation ktcbod 0 213,140 170,053
>

On Tue, Aug 4, 2015 at 3:56 AM, Stefan Koehler <contact_at_soocs.de> wrote:

> 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 - 23:54:45 CEST