TM Contention (locks type 2, 3, 6)

From: LS Cheng <exriscer_at_gmail.com>
Date: Tue, 28 Feb 2012 00:40:44 +0100
Message-ID: <CAJ2-Qb-_nstVHsVPV1h_LJ+rjebROKMTW6smgWODxEwPg7Gspw_at_mail.gmail.com>

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 Received on Mon Feb 27 2012 - 17:40:44 CST