Home -> Mailing Lists -> Oracle-L -> Re: TM Contention (locks type 2, 3, 6)

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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 28 Feb 2012 07:50:30 -0000
Message-ID: <07CD5CD8DFD44E0F8C94ECF2B88ADE1F_at_Primary> 






TM/2 is standard in your version of Oracle at the opposite end of an RI 
constraint when you modify the data so inserting into a child will produce 
a mode 2 on the parent.


TM/3 is the standard mode for DML on a table



Mode 6 means someone has issued


    lock table in exclusive mode


    create index


or


    insert /*+ append */ select



Perhaps some developer somewhere read that inserts go faster if you use /*+ 
append */ and included a couple in the latest release.
I would start by searching v$sqlstats where upper(sql_text) like %APPEND%





Regards



Jonathan Lewis


http://jonathanlewis.wordpress.com


Oracle Core (Apress 2011)


http://www.apress.com/9781430239543




    
  
  •   Original Message ----- 
From: "LS Cheng" <exriscer_at_gmail.com>
To: "Oracle Mailinglist" <oracle-l_at_freelists.org>
Sent: Monday, February 27, 2012 11:40 PM
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








No virus found in this message.


Checked by AVG - www.avg.com


Version: 2012.0.1913 / Virus Database: 2114/4835 - Release Date: 02/27/12




--



http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 28 2012 - 01:50:30 CST












Original text of this message