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@xxxxxxxxx> To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx> 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l