Re: TM Contention (locks type 2, 3, 6)

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Mon, 27 Feb 2012 22:52:59 -0800 (PST)
Message-ID: <1330411979.57850.YahooMailNeo_at_web45003.mail.sp1.yahoo.com>

Have seen TM in mode 3 (if memory serves correctly) for merge statements on parent tables We had provided a testcase and got a fix via

Bug 8330621: MERGE ON PARENT SCANNING CHILD TABLES
�

Fairlie Rego
Senior Oracle Consultant
http://el-caro.blogspot.com/
M: +61 402 792 405
�

---

 From: LS Cheng <exriscer_at_gmail.com>
To: Oracle Mailinglist <oracle-l_at_freelists.org> Sent: Tuesday, 28 February 2012 10:40 AM Subject: TM Contention (locks type 2, 3, 6)  

Hi
I have a database which had some application modifications (including database tables and pl/sql changes)� last week, since then we have TM contention time to time. Initially I thought it was typical parent-child FK lacking index issue however I can remember when FK has no index the request mode is 4 (as seen from v$lock) but in out case the request mode is 2, 3 and 6, I wonder what sort of issue is this?

select INST_ID, SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from v$lock
where block = 1 or request > 0
order by request, ctime

� INST_ID� � � � SID TY� � � � ID1� � � � ID2� � � LMODE� � REQUEST
CTIME� � � BLOCK
---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
� � � � 2� � � 3142 TM� � � 65317� � � � � 0� � � � � 2

0� � � �  49� � � � � 1


� � � �  2� � �  1709 TM� � � 65317� � � � � 0� � � � � 2

0� � � �  73� � � � � 1

� � � � 2� � � 1823 TM� � � 65317� � � � � 0� � � � � 2� � � � � 0
238� � � � � 1
� � � � 2� � � 3761 TM� � � 65317� � � � � 0� � � � � 2� � � � � 0
418� � � � � 1
� � � � 2� � � 3078 TM� � � 65317� � � � � 0� � � � � 2� � � � � 0
799� � � � � 1
� � � � 2� � � 2460 TM� � � 65317� � � � � 0� � � � � 2� � � � � 0

1462� � � � � 1


� � � �  2� � �  2902 TM� � � 65317� � � � � 0� � � � � 2� � � � � 0

2013� � � � � 1


� � � �  2� � �  2009 TM� � � 65317� � � � � 0� � � � � 0

2� � � � � 3� � � � � 0


� � � �  2� � �  1952 TM� � � 65317� � � � � 0� � � � � 0

2� � � � � 5� � � � � 0


� � � �  2� � �  2272 TM� � � 65317� � � � � 0� � � � � 0

2� � � � � 7� � � � � 0


� � � �  2� � �  2673 TM� � � 65317� � � � � 0� � � � � 0

2� � � � � 7� � � � � 0


� � � �  2� � �  2530 TM� � � 65317� � � � � 0� � � � � 0

2� � � � � 9� � � � � 0


� � � �  2� � �  2156 TM� � � 65317� � � � � 0� � � � � 0

3� � � � � 3� � � � � 0


� � � �  2� � �  2040 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 1� � � � � 0


� � � �  2� � �  3243 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 3� � � � � 0


� � � �  2� � �  3101 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  3056 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  2712 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  2487 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  2259 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  2132 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  2111 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  1934 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  1763 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  3525 TM� � � 65317� � � � � 0� � � � � 0

6� � � � � 9� � � � � 0


� � � �  2� � �  3742 TM� � � 65317� � � � � 0� � � � � 0

6� � � �  15� � � � � 0


� � � �  2� � �  3161 TM� � � 65317� � � � � 0� � � � � 0

6� � � �  18� � � � � 0

� � � � 2� � � 2980 TM� � � 65317� � � � � 0� � � � � 0
6� � � � 19� � � � � 0

65317 is a table which has no parents

We are running 10.2.0.5

Cheers

--
LSC


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

Received on Tue Feb 28 2012 - 00:52:59 CST