Re: TM Contention (locks type 2, 3, 6)
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-lReceived on Tue Feb 28 2012 - 00:52:59 CST