oracle 等待事件enq: TM – contention 处理

先说结论： enq: TM - contention 这个等待事件出现一般都有下面几种情况： 1 子表没有索引，这个最常见。 2 并行 DML 3 如果发生在 RAC 集群里，gc问题也可能造成相关等待事件 4 利用lock table 主动获取TM锁 ## 第一部分：子表没有索引 测试 创建测试表： CREATE TABLE T2001     (     supplier_id     number(10)     not null,         supplier_name     varchar2(50)     not null,         contact_name     varchar2(50),             CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)     ); INSERT INTO T2001 VALUES (1, 'Supplier 1', 'Contact 1'); INSERT INTO T2001 VALUES (2, 'Supplier 2', 'Contact 2'); INSERT INTO T2001 VALUES (3, 'Supplier 3', 'Contact 3'); INSERT INTO T2001 VALUES (4, 'Supplier 4', 'Contact 4'); INSERT INTO T2001 VALUES (5, 'Supplier 5', 'Contact 5'); COMMIT; CREATE TABLE T4001     (     product_id     number(10)     not null,         product_name    varchar2(50)    not null,         supplier_id     number(10)     not null,         CONSTRAINT fk_supplier           FOREIGN KEY (supplier_id)          REFERENCES T2001(supplier_id)          ON DELETE CASCADE     ); INSERT INTO T4001 VALUES (1, 'Product 1', 1); INSERT INTO T4001 VALUES (2, 'Product 2', 1); INSERT INTO T4001 VALUES (3, 'Product 3', 2); INSERT INTO T4001 VALUES (4, 'Product 4', 4); COMMIT; 测试1  session 1: DELETE T2001 WHERE supplier_id = 1;                --不提交 session 2: DELETE T2001 WHERE supplier_id = 2;               -- (现象HANG住) session 3: DELETE T4001 WHERE product_id = 2;                 --(现象HANG住) 杀掉会话1 后先执行的是会话3,2等待会话3执行完后再执行。 查询锁和阻塞情况： SQL>  select INST_ID,        SID,        TYPE,        ID1,        ID2,        LMODE,        REQUEST,        CTIME,        BLOCK,        DECODE(BLOCK, 0, '', 'blocker') blocker,        DECODE(request, 0, '', 'waiter') waiter   from gv$lock  where (ID1, ID2, TYPESQL>   2    3    4    5    6    7    8    9   10   11   12   13  ) in        (select ID1, ID2, TYPE from gv$lock where request > 0)  order by blocker; 14   15      INST_ID   SID TY ID1    ID2     LMODE    REQUEST   CTIME      BLOCK BLOCKER WAITER ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ------- ------ 1    11 TM   10316098      0 3    0     108 1 blocker 1   382 TM   10316098      0 0    3      82 0    waiter 1   136 TM   10316098      0 0    5      89 0    waiter SQL> col spid format a10 col PROCESS format a10 col machine format a25 col  PROGRAM format a25 col type format a10 SELECT p.spid,s.sid,s.MACHINE,s.PROCESS,s.PROGRAM,s.type,s.BLOCKING_SESSION   from gv$process p, gv$session s  where p.ADDR = s.PADDR    AND s.sid in        (select c.sid           from (select distinct a.blocking_instance, a.BLOCKING_SESSION                   from gv$session a                  where blocking_session is not null) b           join gv$session c             on b.blocking_instance = c.SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10  INST_ID            and b.blocking_session = c.SID and c.BLOCKING_SESSION is null and c.BLOCKING_SESSION_STATUS != 'VALID'            );       11   12   SPID   SID MACHINE PROCESS    PROGRAM      TYPE BLOCKING_SESSION ---------- ---------- ------------------------- ---------- ------------------------- ---------- ---------------- 27267    11 zc 27266    sqlplus@zc (TNS V1-V3)    USER 测试2： session 1: DELETE T4001 WHERE product_id = 2;                --不提交 session 2: DELETE T2001 WHERE supplier_id = 2;               (现象HANG住) 阻塞情况： select INST_ID,        SID,        TYPE,        ID1,        ID2,        LMODE,        REQUEST,        CTIME,        BLOCK,        DECODE(BLOCK, 0, '', 'blocker') blocker,        DECODE(request, 0, '', 'waiter') waiter   from gv$lock  where (ID1, ID2, TYPE) in        (select ID1, ID2, TYPE from gv$lock where request > 0)  order by blocker; SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15      INST_ID   SID TYPE ID1    ID2     LMODE    REQUEST   CTIME      BLOCK BLOCKER WAITER ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ------ 1    11 TM    10316098      0 3    0      19 1 blocker 1   136 TM    10316098      0 0    5      12 0    waiter SQL>  SQL>  col spid format a10 col PROCESS format a10 col machine format a25 col  PROGRAM format a25 col type format a10 SELECT p.spid,s.sid,s.MACHINE,s.PROCESS,s.PROGRAM,s.type,s.BLOCKING_SESSION   from gv$process p, gv$session s  where p.ADDR = s.PADDR    AND s.sSQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4  id in        (select c.sid           from (select distinct a.blocking_instance, a.BLOCKING_SESSION                   from gv$session a                  where blocking_session is not null) b           join gv$session c             on b.blocking_instance = c  5    6    7    8    9   10  .INST_ID            and b.blocking_session = c.SID and c.BLOCKING_SESSION is null and c.BLOCKING_SESSION_STATUS != 'VALID'            );   11   12   SPID   SID MACHINE PROCESS    PROGRAM      TYPE BLOCKING_SESSION ---------- ---------- ------------------------- ---------- ------------------------- ---------- ---------------- 27267    11 zc 27266    sqlplus@zc (TNS V1-V3)    USER 测试3  create index T40011 on T4001(supplier_id); session 1: DELETE T4001 WHERE product_id = 2;                --不提交 session 2: DELETE T2001 WHERE supplier_id = 2;               正常删除 ## 第二部分 并行insert   开启多个session执行下面的sql  SQL> insert /*+append no logging*/ into test select * from test ; 1162896 rows created.   查询阻塞情况： SQL> select INST_ID,        SID,        TYPE,        ID1,        ID2,        LMODE,        REQUEST,        CTIME,        BLOCK,        DECODE(BLOCK, 0, '', 'blocker') blocker,        DECODE(request, 0, '', 'waiter') waiter   from gv$lock  where (ID1, ID2, TYPE)  2    3    4    5    6    7    8    9   10   11   12   13   in        (select ID1, ID2, TYPE from gv$lock where request > 0)  order by blocker;  14   15      INST_ID   SID TYPE ID1    ID2     LMODE    REQUEST   CTIME      BLOCK BLOCKER WAITER ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------- ------ 1    11 TM       73885      0 6    0      33 1 blocker 1   382 TM       73885      0 0    6      18 0    waiter 1   136 TM       73885      0 0    6      19 0    waiter SQL>  SQL>  select sid,event from v$session where sid in (382,136);        SID EVENT ---------- ----------------------------------------------------------------        136 enq: TM - contention        382 enq: TM - contention       ## 第三部分：利用lock table 主动获取TM锁 或者异常的 DDL  利用lock table 主动获取TM锁        利用lock table...语句有意获取TM锁时，可能发生TM锁争用。        session 1: update test set object_id=1  where owner='ZC';    session 2: lock table test in exclusive mode;        如上所示，会话1 上因update以sub-exclusive模式获得TM锁的状态下，会话2利用lock table命令试图以exclusive模式获得TM锁，如果发生争用，则等待enq:TM-contention事件。      SQL> select INST_ID,        SID,        TYPE,        ID1,        ID2,        LMODE,        REQUEST,        CTIME,        BLOCK,        DECODE(BLOCK, 0, '', 'blocker') blocker,        DECODE(request, 0, '', 'waiter') waiter   from gv$lock  where (ID1, ID2, TYPE)  2    3    4    5    6    7    8    9   10   11   12   13   in        (select ID1, ID2, TYPE from gv$lock where request > 0)  order by blocker;  14   15      INST_ID   SID TY ID1    ID2     LMODE    REQUEST   CTIME      BLOCK BLOCKER WAITER ---------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ------- ------ 1    11 TM      73885      0 3    0      93 1 blocker 1   136 TM      73885      0 0    6      86 0    waiter SQL> select sid,event from v$session where sid=136;         SID EVENT ---------- ----------------------------------------------------------------        136 enq: TM - contention ## 第三部分：解决方案 如果是第一种情况，根据下面的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 = 'T4001'; 列出某个用户下所有缺少索引的子表  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('ZC')            and cc.owner = upper('ZC')         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('ZC')            and IC.table_owner = upper('ZC'))  ORDER BY table_name, column_position; 列出所有的缺失外键索引的表 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 ;     第二种情况： 建议避免并行的insert 类型的dml  第三种情况： 建议关闭DRM或者分析私网是否存在问题   第四种情况： 避免相关操作   参考：  WAITEVENT: "enq: TM - contention" Reference Note (Doc ID 1980175.1) Resolving Issues Where 'enq: TM - contention' Waits are Occurring (Doc ID 1905174.1) enq: TX - contention when _lm_drm_disable is set to 2(Doc ID 2818792.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)