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
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: @OracleSK
Venkat Krish <venkat.lear@xxxxxxxxx> hat am 3. August 2015 um 21:47geschrieben:
cores & running many dbs in same server..dont ask me why)
Dear Oracle Smart-ians.
Background:
Running Oracle 11.2.0.4 running on Solaris 10. ( beefy box with 100+
activity "latch free" dominated.
Problem:
In one of our critical DBs during peak activity, we see the top
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
top event shows 85% latch free
----------- ----------- -------------- ------------ ---------
-----------
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
