oracle 11.2.0.4 rac集群等待事件enq: TM - contention

    近期，一金融客户oracle 11.2.0.4 rac集群delete不当导致等待事件enq: TM - contention严重引起大范围会话堆积，记录的相关分析工作如下。 1、登录集群任意节点，查看集群全局等待事件 SQL> select event,count(*) from gv$session where wait_class<>'Idle' group by event;  EVENT                                         COUNT(*)  ---------------------------------------------------------------- ----------  enq: TM - contention                  24 2、查看等待事件在两个节点的分布 节点1： EVENT COUNT(*)  ---------------------------------------------------------------- ----------  SQL*Net message from client 79  rdbms ipc message 64  enq: TM - contention 22  gcs remote message 10  Space Manager: slave idle wait 5  wait for unread message on broadcast channel 4  DIAG idle wait 4  class slave wait 3  Streams AQ: waiting for time management or cleanup tasks 2  Streams AQ: qmn coordinator idle wait 2  PX Deq: Execution Msg 2  VKTM Logical Idle Wait 2  GCR sleep 2  Streams AQ: qmn slave idle wait 2  smon timer 2  pmon timer 2  ges remote message 2  ASM background timer 2  PING 2  PX Deq: Execute Reply 1 节点2： EVENT COUNT(*)  ---------------------------------------------------------------- ----------  SQL*Net message from client 49  rdbms ipc message 32  gcs remote message 5  wait for unread message on broadcast channel 2  class slave wait 2  DIAG idle wait 2  Space Manager: slave idle wait 2  enq: TM - contention 2  smon timer 1  PING 1  Streams AQ: qmn slave idle wait 1  SQL*Net message to client 1  GCR sleep 1  ges remote message 1  VKTM Logical Idle Wait 1  Streams AQ: qmn coordinator idle wait 1  ASM background timer 1  pmon timer 1  Streams AQ: waiting for time management or cleanup tasks 1 19 rows selected. 3、查看引起enq: TM - contention等待事件的会话信息        SID USERNAME SQL_ID MODULE MACHINE PROGRAM  ---------- ---------- ------------- ---------- ---------------------------------------------------------------- ------------------------------------------------  363 MW_SYS 74j1zd36h4n96 localhost.localdomain  387 MW_SYS 74j1zd36h4n96 localhost.localdomain  602 MW_SYS 74j1zd36h4n96 localhost.localdomain  626 MW_SYS 74j1zd36h4n96 localhost.localdomain  674 MW_SYS 74j1zd36h4n96 localhost.localdomain  1106 MW_SYS 74j1zd36h4n96 localhost.localdomain  1441 MW_SYS 74j1zd36h4n96 localhost.localdomain  1730 MW_SYS 74j1zd36h4n96 localhost.localdomain  1946 MW_SYS 74j1zd36h4n96 localhost.localdomain  1969 MW_SYS 74j1zd36h4n96 localhost.localdomain  1993 MW_SYS 74j1zd36h4n96 localhost.localdomain  1994 MW_SYS 74j1zd36h4n96 localhost.localdomain  2041 MW_SYS 74j1zd36h4n96 localhost.localdomain  2042 MW_SYS 74j1zd36h4n96 localhost.localdomain  2066 MW_SYS 74j1zd36h4n96 localhost.localdomain  2067 MW_SYS 74j1zd36h4n96 localhost.localdomain  2091 MW_SYS 74j1zd36h4n96 localhost.localdomain  2114 MW_SYS 74j1zd36h4n96 localhost.localdomain  2137 MW_SYS 74j1zd36h4n96 localhost.localdomain  2138 MW_SYS 74j1zd36h4n96 localhost.localdomain  2161 MW_SYS 74j1zd36h4n96 localhost.localdomain  2162 MW_SYS 74j1zd36h4n96 localhost.localdomain  2209 MW_SYS 74j1zd36h4n96 localhost.localdomain  2233 MW_SYS 74j1zd36h4n96 localhost.localdomain  24 rows selected. 4、查看SQL语句74j1zd36h4n96文本信息 SQL_FULLTEXT  --------------------------------------------------------------------------------  delete from mw_sys.mwt_is_user m where m.user_id not in('0D3C0ACD-C95B-42DA-A5DE  -70F019DAB52B','8697D72E-C77B-4C70-A434-ED7D488ADC36'); 5、查看SQL语句74j1zd36h4n96的执行计划 PLAN_TABLE_OUTPUT  -------------------------------------------------------------------------------------------------------------- SQL_ID 74j1zd36h4n96, child number 0  -------------------------------------  delete from mw_sys.mwt_is_user m where m.user_id not  in('0D3C0ACD-C95B-42DA-A5DE-70F019DAB52B','8697D72E-C77B-4C70-A434-ED7D4  88ADC36')  Plan hash value: 3271715000  ----------------------------------------------------------------------------------  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |  ----------------------------------------------------------------------------------  | 0 | DELETE STATEMENT | | | | 210 (100)| |  | 1 | DELETE | MWT_IS_USER | | | | |  |* 2 | TABLE ACCESS FULL| MWT_IS_USER | 18739 | 951K| 210 (1)| 00:00:03 |  ----------------------------------------------------------------------------------  Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------  1 - DEL$1  2 - DEL$1 / M@DEL$1  Outline Data  -------------  /*+  BEGIN_OUTLINE_DATA  IGNORE_OPTIM_EMBEDDED_HINTS  OPTIMIZER_FEATURES_ENABLE('11.2.0.4')  DB_VERSION('11.2.0.4')  OPT_PARAM('_bloom_filter_enabled' 'false')  OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')  OPT_PARAM('_bloom_pruning_enabled' 'false')  OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')  OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')  OPT_PARAM('_optimizer_use_feedback' 'false')  ALL_ROWS  OUTLINE_LEAF(@"DEL$1")  FULL(@"DEL$1" "M"@"DEL$1")  END_OUTLINE_DATA  */  Predicate Information (identified by operation id):  ---------------------------------------------------  2 - filter(("M"."USER_ID"<>'0D3C0ACD-C95B-42DA-A5DE-70F019DAB52B' AND  "M"."USER_ID"<>'8697D72E-C77B-4C70-A434-ED7D488ADC36'))  Column Projection Information (identified by operation id):  -----------------------------------------------------------  2 - (cmp=2; cpy=2,3) "M".ROWID[ROWID,10], "M"."USER_ID"[CHARACTER,36],  "M"."USER_NAME"[VARCHAR2,64], "USER_ISSYSTEM"[CHARACTER,1],  "USER_STATUS"[CHARACTER,1]  55 rows selected. 6、查询SQL语句74j1zd36h4n96要保留的数据量 SQL> select count(*) from mw_sys.mwt_is_user where user_id ='0D3C0ACD-C95B-42DA-A5DE-70F019DAB52B';  COUNT(*)  ----------  1  SQL>  SQL> select count(*) from mw_sys.mwt_is_user where user_id ='8697D72E-C77B-4C70-A434-ED7D488ADC36';  COUNT(*)  ----------  1 7、查看SQL语句74j1zd36h4n96要删除的数据量 SQL> select count(*) from mw_sys.mwt_is_user m where m.user_id not in('0D3C0ACD-C95B-42DA-A5DE  -70F019DAB52B','8697D72E-C77B-4C70-A434-ED7D488ADC36'); 2  COUNT(*)  ----------  18740 8、问题    由以上信息可知，24个会话分布在两个节点上通知对同一张表执行相同的delete操作，而且删除的数据量是大范围的，只保留2条记录， 因此导致严重的enq:TM锁。 9、建议     delete操作分批执行，且控制会话间执行的delete语句删除的数据没有交集。