首页 » ORACLE 9i-23ai » Tuning enq: TM – contention with foreign key (外键引起的队列)
Tuning enq: TM – contention with foreign key (外键引起的队列)
TM – Enqueue contention 与Table Manipulation相关的入队争用，可以在使用需要锁定表的重组活动显式锁定表时看到。
SQL> @sed "enq: TM" Show wait event descriptions matching %enq: TM%.. EVENT# EVENT_NAME WAIT_CLASS PARAMETER1 PARAMETER2 PARAMETER3 ENQUEUE_NAME REQ_REASON REQ_DESCRIPTION ------ ------------------------------------------------------- -------------------- ------------------------- ------------------------- ------------------------- ------------------------------ -------------------------------- ---------------------------------------------------------------------------------------------------- 235 enq: TM - contention Application name|mode object # table/partition DML contention Synchronizes accesses to an object
常见原因:
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')
--------------------
391
anbob@ANBOB>
########新开一个session 2 sid=1163
[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 sid=16
[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
SQL> @ase
last_call
USERNAME SID EVENT MACHINE MODULE STATUS et SEQ# SQL_ID WAI_SECINW ROW_WAIT_OBJ# SQLTEXT BS CH# OSUSER HEX
---------- ---------- -------------------- ---------- -------------------- ------- --------- ---------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
ANBOB 16 enq: TM - contention 11g-node2 SQL*Plus ACTIVE 65 44 faqsbu42vyx39 0:65 110073 insert into memb values(10,'j : 0 oracle
ANBOB 1163 enq: TM - contention 11g-node2 SQL*Plus ACTIVE 108 35 2gc8m05j8njp5 0:108 110073 delete team where id=2 2:391 0 oracle
-- session 1
SQL> @lock sid=391
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
391 TM 3 Row-X (SX) 0 None 110073 0 74 2 --- row exclusive lock (RX—mode 3)
391 AE 4 Share 0 None 100 0 163 2
391 TX 6 Exclusive 0 None 197197849 6093 74 2
391 TM 3 Row-X (SX) 0 None 110071 0 74 2
SQL> @oid 110071
owner object_name object_type SUBOBJECT_NAME CREATED LAST_DDL_TIME status DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ------------------- ------------------- --------- --------------
ANBOB TEAM TABLE 2010-08-06 20:55:48 2010-08-06 20:56:10 VALID 110071
SQL> @oid 110073
owner object_name object_type SUBOBJECT_NAME CREATED LAST_DDL_TIME status DATA_OBJECT_ID
------------------------- ------------------------------ ------------------ ------------------------------ ------------------- ------------------- --------- --------------
ANBOB MEMB TABLE 2010-08-06 20:56:30 2010-08-06 20:56:30 VALID 110073
-- session 2
SQL> @lock sid=1163
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
1163 TM 0 None 5 S/Row-X (SSX) 110073 0 42 0 --- share row exclusive lock (SRX – mode 5)
1163 AE 4 Share 0 None 100 0 52 2
1163 TM 3 Row-X (SX) 0 None 110071 0 42 2
-- session 3
SQL> @lock sid=16
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
16 TM 0 None 3 Row-X (SX) 110073 0 32 0
16 AE 4 Share 0 None 100 0 43 2
16 TM 3 Row-X (SX) 0 None 110071 0 32 2
优化，外键创建索引
anbob@ANBOB> create index idx_mem_tid on memb(tid);
Index created.
# session 1 retry delete
SQL> @lock sid=391;
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
391 TM 3 Row-X (SX) 0 None 110073 0 16 2
391 AE 4 Share 0 None 100 0 907 2
391 TX 6 Exclusive 0 None 197001249 6100 16 2
391 TM 3 Row-X (SX) 0 None 110071 0 16 2
# session 2
SQL> @lock sid=1163
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
1163 TM 3 Row-X (SX) 0 None 110073 0 53 2
1163 AE 4 Share 0 None 100 0 744 2
1163 TM 3 Row-X (SX) 0 None 110071 0 53 2
1163 TX 6 Exclusive 0 None 196870164 6097 53 2
# session 3
SQL> @lock sid=16
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
16 TM 3 Row-X (SX) 0 None 110073 0 7 2
16 AE 4 Share 0 None 100 0 744 2
16 TX 6 Exclusive 0 None 196739091 6168 7 2
16 TM 3 Row-X (SX) 0 None 110071 0 7 2
Note:
解决方法在子表父字段建索引后，就不再影响其它的insert了. 另外注意开始delete team（父表）时，从v$session看到的ROW_WAIT_OBJ# 或ASH中的current obj都不是当前表，而是mem(子表)
另外注意，如果不在外键创建索引，此时KILL 了session 1, session 2依旧会等待，但session 3可以继续，但session 1改为等待session3,下面演示.
SQL> drop index idx_mem_tid;
SQL> @ase
last_call
USERNAME SID EVENT MACHINE MODULE STATUS et SEQ# SQL_ID WAI_SECINW ROW_WAIT_OBJ# SQLTEXT BS CH# OSUSER HEX
---------- ---------- -------------------- ---------- -------------------- ------- --------- ---------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
ANBOB 393 enq: TM - contention 11g-node2 SQL*Plus ACTIVE 26 33 f58znv6bg873z 0:27 110073 insert into memb values(10,'ja : 0 oracle
ANBOB 1163 enq: TM - contention 11g-node2 SQL*Plus ACTIVE 32 33 2gc8m05j8njp5 0:33 110073 delete team where id=2 2:391 0 oracle
SQL> alter system kill session '391,24433' -- ANBOB@11g-node2 (sqlplus@11g-node2 (TNS V1-V3));
System altered.
SQL> @ase
last_call
USERNAME SID EVENT MACHINE MODULE STATUS et SEQ# SQL_ID WAI_SECINW ROW_WAIT_OBJ# SQLTEXT BS CH# OSUSER HEX
---------- ---------- -------------------- ---------- -------------------- ------- --------- ---------- --------------- ---------- ------------- ------------------------------ ---------- ---- ---------- ----------
ANBOB 1163 enq: TM - contention 11g-node2 SQL*Plus ACTIVE 261 34 2gc8m05j8njp5 0:213 -1 delete team where id=2 2:16 0 oracle 2000003
ANBOB 391 SQL*Net message from 11g-node2 SQL*Plus KILLED 266 28 0:266 -1 : oracle
-- session 3
SQL> @lock sid=16
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
16 AE 4 Share 0 None 100 0 579 2
16 TM 3 Row-X (SX) 0 None 110071 0 70 2
16 TX 6 Exclusive 0 None 197132290 6115 27 2
16 TM 3 Row-X (SX) 0 None 110073 0 27 1
-- session 2
SQL> @lock sid=1163
SID TYPE LMODE MODE_HELD REQUEST MODE_REQUESTED LOCK_ID1 LOCK_ID2 CTIME BLOCK
------- ---- ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
1163 AE 4 Share 0 None 100 0 619 2
1163 TM 3 Row-X (SX) 0 None 110071 0 93 2
1163 TX 6 Exclusive 0 None 196935707 6091 44 2
1163 TM 3 Row-X (SX) 5 S/Row-X (SSX) 110073 0 44 1
Note:
KILL SESSION 1后，session 3拿到了mode 3的 子表lock, 但是session 2不是未请求到，直到session 3结束事务。
查找父表的级联影响？
如上面的案例，是因为操作了父表，子表缺少索引，而在子表加了MODE 3 TM LOCK, 其它会话请求子表mode 5 TM LOCK 堵塞。有时1个子表有多个父表，需要确认一下哪些父表的事务可能会影响子表，产生TM enq.
为了更好区分，我再增加1个父表，但是在子表FK列创建索引，以区分。
SQL> create table t_type(id int primary key,name varchar2(10)); Table created. SQL> alter table anbob.memb add type_id int; Table altered. SQL> alter table memb add constraint fk_type foreign key(type_id) references t_type(id) on delete cascade; Table altered. SQL> create index idx_memb_type on memb(type_id); Index created.
列出子表的所有父表.
SQL> select
co.owner c_owner,
co.table_name c_table_name,
cc.column_name c_column_name,
co.constraint_name cons_constraint_name,
co.constraint_type,
cpc.table_name p_table,
cpc.column_name p_column,
co.r_constraint_name p_pk,
cc.position,
co.status,
co.validated
from
dba_constraints co,
dba_cons_columns cc,
DBA_CONS_COLUMNS cpc
where
co.owner = cc.owner
and co.table_name = cc.table_name
and co.constraint_name = cc.constraint_name
and co.constraint_type='R'
and co.r_constraint_name=cpc.constraint_name
and co.r_owner=cpc.owner
and co.TABLE_NAME = 'MEMB';
C_OWNER C_TABLE_NAME C_COLUMN_NAME CONSTRAINT_NAME CO P_TABLE P_COLUMN P_PK POSITION STATUS VALIDATED
---------- -------------------- -------------------- ------------------------------ -- -------------------- -------------------- -------------------- ---------- ---------------- --------------------------
ANBOB MEMB TID FK_MEM_TEAM_ID R TEAM ID SYS_C0011522 1 ENABLED VALIDATED
ANBOB MEMB TYPE_ID FK_TYPE R T_TYPE ID SYS_C0011526 1 ENABLED VALIDATED
子表FK 缺少索引
SQL>SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM dba_constraints c, dba_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
and c.owner=cc.owner
AND c.constraint_type = 'R'
AND c.owner = upper('ANBOB') and cc.owner = upper('ANBOB')
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM dba_indexes i, dba_ind_columns ic
WHERE i.index_name = ic.index_name
AND I.owner =upper('ANBOB') and IC.table_owner =upper('ANBOB')
)
ORDER BY table_name, column_position;
TABLE_NAME COLUMN_NAME COLUMN_POSITION
-------------------- ------------------------------ ---------------
MEMB TID 1
查找哪些父表指定子表FK缺失索引，会导致子表enq: TM
with p_all as
( SELECT co.owner c_owner, co.table_name c_table_name, cc.column_name c_column_name, co.constraint_name cons_constraint_name, co.constraint_type, cpc.table_name p_table, cpc.column_name p_column, co.r_constraint_name p_pk, cc.position, co.status, co.validated
FROM dba_constraints co, dba_cons_columns cc, DBA_CONS_COLUMNS cpc
WHERE co.owner = cc.owner
AND co.table_name = cc.table_name
AND co.constraint_name = cc.constraint_name
AND co.constraint_type='R'
AND co.r_constraint_name=cpc.constraint_name
AND co.r_owner=cpc.owner
AND co.owner='ANBOB'
AND co.TABLE_NAME = 'MEMB'),
fk_noindex AS
( SELECT c.table_name, cc.column_name, cc.position column_position
FROM dba_constraints c, dba_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.owner=cc.owner
AND c.constraint_type = 'R'
AND c.owner = upper('ANBOB')
AND cc.owner = upper('ANBOB')
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM dba_indexes i, dba_ind_columns ic
WHERE i.index_name = ic.index_name
AND I.owner =upper('ANBOB')
AND IC.table_owner =upper('ANBOB'))
SELECT p.P_TABLE,P.cons_constraint_name,P.c_table_name,P.C_COLUMN_NAME
FROM p_all p, fk_noindex f
WHERE f.table_name=p.c_table_name
AND f.column_name=p.c_column_name;
P_TABLE CONSTRAINT_NAME C_TABLE_NAME C_COLUMN_NAME
-------------------- ------------------------------ -------------------- --------------------
TEAM FK_MEM_TEAM_ID MEMB TID
Note:
虽然memb表有两个父表team ,t_type, 但是type_id FK列创建了索引，所以仅显示TID fk列无索引。
实验2
显示主外键时，如果父表记录未提交，子表是等待enq tx，而不是提示无父键发现错误ORA-02291。
# 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
列出缺失外键索引的表
--
prompt FK constraints without index on child table
select
acc.OWNER "Owner",
acc.CONSTRAINT_NAME "Constraint",
acc.table_name "Table",
acc.COLUMN_NAME "Column",
acc.POSITION "Position"
from
dba_cons_columns acc, dba_constraints ac
where
ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME and ac.CONSTRAINT_TYPE = 'R'
and acc.OWNER not in ( 'ANONYMOUS', 'AURORA$', 'AURORA', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'DVF', 'DVSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORAWSM', 'ORDPLUGINS', 'ORDSYS', 'OSE', 'OUTLN', 'PERFSTAT', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'REPADMIN', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TRACESVR', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST', 'WKUSER', 'WMSYS', 'XDB','APEX_030200','GSMADMIN_INTERNAL','ORDDATA' )
and acc.OWNER = ac.OWNER
and not exists ( select 'TRUE' from dba_ind_columns b
where b.TABLE_OWNER = acc.OWNER
and b.TABLE_NAME = acc.TABLE_NAME
and b.COLUMN_NAME = acc.COLUMN_NAME
and b.COLUMN_POSITION = acc.POSITION)
union all
select ' ',null,null,null,null from dual
order by 1
;
Script to Check for Foreign Key Locking Issues for a Specific User (Doc ID 1019527.6)
Script to check for Missing Indexes for Foreign Keys (Doc ID 16428.1)
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。