博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5780538.html
Oracle中的enq: TM - contention 等待事件，是事务中锁表，属于表级锁的争用。如果数据库发生，是很严重的事情。一般引起TM-contention事件的情况如下：
(1)索引外键(foreign key)使用不当会导致TM锁争用等。
(2)DML与DDL之间的TM争用。
(3)LOCK TABLE 引起的TM锁争用。
(4)执行Direct LOAD/ insert/*+*append/ into /Parallel Load等操作时引起的TM锁争用。
(5)分析表统计信息或validate structure时获取TM锁可能会阻塞事务。
1.重现等待事件
SQL> create table type_parent as select distinct object_type from dba_objects;Table created.
SQL>create table info_child
2 as
3 select object_id, object_type, object_name,owner,data_object_id
4 from all_objects;
Table created.
SQL> alter table type_parent add constraint pk_type_parent primary key (object_type);
Table altered.
SQL>
SQL> alter table info_child add constraint pk_info_child primary key (object_id);
Table altered.
SQL> alter table info_child add constraint fk_child_type_parent
2 foreign key (object_type) references type_parent on delete cascade;
Table altered.
SQL>
SQL>
SQL> select count(*), object_type from info_child group by object_type;
COUNT(*) OBJECT_TYPE
---------- -------------------
1 EDITION
106 INDEX PARTITION
32 TABLE SUBPARTITION
2 CONSUMER GROUP
223 SEQUENCE
107 TABLE PARTITION
3 SCHEDULE
1 RULE
309 JAVA DATA
160 PROCEDURE
55 OPERATOR
2 DESTINATION
9 WINDOW
4 SCHEDULER GROUP
1320 PACKAGE
1259 PACKAGE BODY
186 LIBRARY
19 PROGRAM
19 RULE SET
7 CONTEXT
237 TYPE BODY
837 JAVA RESOURCE
53 XML SCHEMA
621 TRIGGER
14 JOB CLASS
3 DIRECTORY
1 MATERIALIZED VIEW
2863 TABLE
3843 INDEX
28110 SYNONYM
5143 VIEW
303 FUNCTION
23165 JAVA CLASS
2 JAVA SOURCE
9 INDEXTYPE
10 CLUSTER
2872 TYPE
14 JOB
12 EVALUATION CONTEXT
39 rows selected.
现在开始重现事件：
session 1
SQL> select sid from v$mystat where rownum < 2;
SID
----------
125
SQL>
SQL> delete from type_parent where object_type = 'SYNONYM';
1 row deleted.
session 2 --hang
SQL> delete from type_parent where object_type = 'OPERATOR';
session 3 --hang
SQL> INSERT INTO type_parent VALUES ('dbwatcher');
检查等待事件：
SQL> @event_numbers
EVENT COUNT(*)
---------------------------------------------------------------- ----------
asynch descriptor resize 1
SQL*Net message from client 1
pmon timer 1
rdbms ipc message 12
smon timer 1
enq: TM - contention 2
Streams AQ: qmn slave idle wait 1
Space Manager: slave idle wait 5
VKTM Logical Idle Wait 1
DIAG idle wait 2
12 rows selected.
有2个enq: TM - contention 等待事件
2.检查和处理思路
找到TM争用的数据库对象，id1作为数据库对象object_id来确认数据库争用的到底是什么对象，之后我们可以确定具体如何减少这些争用，具体到SQL。
SID BLOCKER EVENT TYPE LMODE REQUEST OBJECT_NAME OBJECT_TYPE
---- ---------- ------------------------------ ---------- ---------- ---------- ------------------------------ -------------------
125 SQL*Net message from client AE 4 0 ORA$BASE EDITION
125 SQL*Net message from client TM 3 0 type_parent TABLE
125 SQL*Net message from client TM 3 0 info_child TABLE
125 SQL*Net message from client TO 3 0 SDO_GEOR_DDL__TABLE$$ TABLE
125 SQL*Net message from client TX 6 0
142 125 enq: TM - contention AE 4 0 ORA$BASE EDITION
142 125 enq: TM - contention TM 3 0 type_parent TABLE
142 125 enq: TM - contention TM 0 5 info_child TABLE
145 142 enq: TM - contention AE 4 0 ORA$BASE EDITION
145 142 enq: TM - contention TM 3 0 type_parent TABLE
145 142 enq: TM - contention TM 0 3 info_child TABLE
11 rows selected.
观察lock的情况
SID SPID TY LMODE REQUEST CTIME BLOCK SUM MODE_HELD MODE_REQUESTED OBJECT_NAME OWNER USERNAME
---- ------------------------ -- ---------- ---------- ---------- ---------- ---- --------------- --------------- ----------------------------
125 2758 TM 3 0 273 1 1 Row-X (SX) S/Row-X (SSX) info_child TEST TEST
125 2758 TM 3 0 273 1 1 Row-X (SX) Row-X (SX) info_child TEST TEST
SQL>@locker
lock lock
holder holder lock lock request blocked
username session id SERIAL# type id1 id2 mode mode BLOCK session id
------------------ ----------- ---------- ------ ----------- ----------- --------- --------- ---------- ----------
SYS 139 11 AE 100 0 4 0 0
TEST 125 7 AE 100 0 4 0 0
TEST 125 7 TM 75662 0 3 0 0
TEST 125 7 TM 75663 0 3 0 1 145
TEST 125 7 TM 75663 0 3 0 1 142
TEST 125 7 TO 68045 1 3 0 0
TEST 125 7 TX 524319 693 6 0 0
TEST 142 17 AE 100 0 4 0 0
TEST 142 17 TM 75662 0 3 0 0
TEST 145 55 AE 100 0 4 0 0
检查锁相关语句阻塞和等待
@locksql
BLOCKERS WAITERS
--------------------------------------------- ---------------------------------------------
bloackers (125:7-TEST)-delete from type_parent waiters (145:55-TEST)-INSERT INTO type_parent
where object_type = 'SYNONYM' VALUES ('dbseek')
bloackers (125:7-TEST)-delete from type_parent waiters (142:17-TEST)-delete from type_parent
where object_type = 'SYNONYM' where object_type = 'OPERATOR'
检查表，发现表info_child有外键，但是没有外键索引
CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME
------------------------------ - ------------------------------ ---------- --------------------
FK_CHILD_type_parent R info_child TEST PK_type_parent
PK_info_child P info_child
检查索引
SQL> select index_name,table_name,index_type from user_indexes where table_name in('type_parent','info_child');
INDEX_NAME TABLE_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SUPPLIER_PK SUPPLIER NORMAL
FK_SUPPLIER PRODUCT NORMAL
PK_type_parent type_parent NORMAL
PK_info_child info_child NORMAL
3.解决办法---对子表外键创建索引
SQL> create index fk_child_type on info_child(object_type)
--The end！