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 Childtables
通常会发生此事件的等待，因为包含外键约束的列上缺少索引。 在这种情况下，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;
打赏
微信扫一扫，打赏作者吧～
扫一扫
700
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
