首页 » ORACLE 9i-23c » Tuning enq: TM – contention with foreign key (外键引起的队列)

Tuning enq: TM – contention with foreign key (外键引起的队列)

TM – Enqueue contention 与Table Manipulation相关的入队争用，可以在使用需要锁定表的重组活动显式锁定表时看到。

ID1 ID2的含义

ID1  :  0(LGWR)  or object_number， 即DBA_OBJECTS.OBJECT_ID

ID2:   0 for a normal table / partition lock ; 1 for a partition-wait lock.

常见原因:

A. Due to Missing Foreign Key (FK) index on the FK constraint columns in the Child tables

通常会发生此事件的等待，因为包含外键约束的列上缺少索引。 在这种情况下，Oracle在DELETE，INSERT和UPDATE语句期间被迫在子表上获取TM锁。

B. Enq: TM – contention’ Wait Event (TM Enqueue Contention) Using Parallel DML :

TM locks are Table-level locks that protect the table from being modified mid transaction.

A parallel DML operation’s lock requirement is very different from serial DML. The available locks are controlled by the parameter ‘DML_Locks’.

If you are seeing heavy TM lock contention on objects while using Parallel DML, you should ensure that the number of DML locks specified is realistic for the concurrent workload.

C. Enq: TM – contention’ Wait Event Occurs During ANALYZE INDEX VALIDATE STRUCTURE :

TM (Table Manipulation) enqueue is acquired during : ANALYZE INDEX VALIDATE STRUCTURE If an index is being analyzed using the ANALYZE INDEX VALIDATE STRUCTURE command while a DML operation on the underlying table is being attempted (requiring a TM lock to be placed), then contention on “enq: TM – contention” can occur.
The ANALYZE INDEX command acquires a TM enqueue in share mode on the underlying table; this will block other sessions when they attempt to place a TM lock that is incompatible with a share-mode lock.

 

SQL>select s.command from v$lock l, v$session s where l.sid = s.sid and l.block = 1 and l.type=’TM’;

演示第一种情况

出门滑雪前，不要忘了给你的外键列加上索引！！有时为了数据的完整性不得不加外键，当然是其它从应用程序中控制完整性，如果有外键而忽略了索引，很可能你的数据库会看到上面的负载图，看下面的两个实验

sys@ANBOB> conn anbob/anbob
Connected.
anbob@ANBOB> create table team(id int,name varchar2(20));
Table created.

anbob@ANBOB> insert into team values(1,'HR');
1 row created.

anbob@ANBOB> insert into team values(2,'IBG');
1 row created.

anbob@ANBOB> insert into team values(3,'OSC');
1 row created.

anbob@ANBOB> commit;
Commit complete.

anbob@ANBOB> create table memb(id int,name varchar2(20),tid int,
  2  constraint fk_mem_team_id foreign key (tid)  references team(id) on delete cascade);
constraint fk_mem_team_id foreign key (tid)  references team(id) on delete cascade)
                                                             *
ERROR at line 2:
ORA-02270: no matching unique or primary key for this column-list

anbob@ANBOB> alter table team modify id primary key;
Table altered.

anbob@ANBOB> create table memb(id int,name varchar2(20),tid int,
  2  constraint fk_mem_team_id foreign key (tid)  references team(id) on delete cascade); 
Table created.

anbob@ANBOB> select table_name,index_name from user_indexes where TABLE_name='TEAM';
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
TEAM                           SYS_C005168

anbob@ANBOB> insert into memb values(1,'anbob',3);
1 row created.

anbob@ANBOB> insert into memb values(2,'adm',1);
1 row created.

anbob@ANBOB> insert into memb values(3,'sal',2);
1 row created.

anbob@ANBOB> commit;
Commit complete.

anbob@ANBOB> delete team where id=1;
1 row deleted.

anbob@ANBOB> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
                2317

anbob@ANBOB>

########新开一个session 2

[oracle@aix ~]$ sqlplus anbob/anbob
SQL*Plus: Release 10.2.0.1.0 - Production on ????????? 6??? 16 17:21:30 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

anbob@ANBOB> delete team where id=2; 
 挂 起

再新开一个session 3

[oracle@aix ~]$ sqlplus anbob/anbob
SQL*Plus: Release 10.2.0.1.0 - Production on ????????? 6??? 16 17:22:13 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

anbob@ANBOB> insert into memb values(10,'jack',3); 
挂起

##########新开一个session 4 ,查看一下lock

sys@ANBOB> col event for a30
sys@ANBOB> /

       SID USERNAME      BLOCKER EVENT                          TY      LMODE    REQUEST OBJECT_NAM OBJECT_TYPE
---------- ---------- ---------- ------------------------------ -- ---------- ---------- ---------- -------------------
       141 ANBOB             131 enq: TM - contention           TM          3          0 TEAM       TABLE
       133 ANBOB             141 enq: TM - contention           TM          2          0 TEAM       TABLE
       131 ANBOB                 SQL*Net message from client    TX          6          0
       131 ANBOB                 SQL*Net message from client    TM          3          0 MEMB       TABLE
       141 ANBOB             131 enq: TM - contention           TM          0          5 MEMB       TABLE
       133 ANBOB             141 enq: TM - contention           TM          0          3 MEMB       TABLE
       131 ANBOB                 SQL*Net message from client    TM          3          0 TEAM       TABLE
...
       166                       rdbms ipc message              CF          2          0
       161                       smon timer                     TS          3          0 I_OBJ#     INDEX
       168                       rdbms ipc message              MR          4          0 I_OBJ#     INDEX

18 rows selected.



anbob@ANBOB> create index idx_mem_tid on memb(tid);


Index created.


解决方法在子表父字段建索引后，就不再影响其它的insert了


实验2


session1

anbob@ANBOB> insert into team values(4,'HR');
1 row created.
session 2

SQL> insert into memb values(13,'rain',5);
insert into memb values(13,'rain',5)
*
ERROR at line 1:
ORA-02291: integrity constraint (ANBOB.FK_MEM_TEAM_ID) violated - parent key not found

SQL> insert into memb values(13,'rain',4); 又hang 住了



session 3查一下当前的等待


sys@ANBOB> select sid,serial#,event,audsid from v$session where username='ANBOB';

SID SERIAL# EVENT AUDSID
---------- ---------- ------------------------------ ----------
143 3675 enq: TX - row lock contention 2584
154 875 SQL*Net message from client 2597



回到session 1 ctrl+c 取消操作，查看一下当前的audsid ,确认sid143等待是它


SQL> insert into memb values(13,'rain',4);
insert into memb values(13,'rain',4)
*
ERROR at line 1:
ORA-02291: integrity constraint (ANBOB.FK_MEM_TEAM_ID) violated - parent key
not found

SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
2584



The following script will show all unindexed columns from foreign key constraints for a specific user and it can also be customized to include only the one table :


SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
AND c.owner = upper('&&owner') and cc.owner = upper('&&owner')
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
AND c.owner = upper('&&owner') and cc.owner = upper('&&owner')
)
ORDER BY table_name, column_position;


        

            打赏
            
        
	







, , 








				





		
	
目前这篇文章有1条评论(Rss)评论关闭。

	

    
   
  1. 
     
    
      
    
                  Reggie Triska | #1
        
     2011-12-21 at 06:52  
      
        
    
      
    
          
      	 
    
	 
    
      
    Excellent goods from you, man. I have understand your stuff previous to and you are just extremely great. I actually like what you have acquired here, really like what you’re stating and the way in which you say it. You make it enjoyable and you still take care of to keep it wise. I cant wait to read far more from you. This is actually a tremendous web site.
    
      
    
      
    

     
       

    2. 





 








上一篇： 


下一篇：