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 74j1zd36h5n96 localhost.localdomain
387 MW_SYS 74j1zd36h5n96 localhost.localdomain
602 MW_SYS 74j1zd36h5n96 localhost.localdomain
626 MW_SYS 74j1zd36h5n96 localhost.localdomain
674 MW_SYS 74j1zd36h5n96 localhost.localdomain
1106 MW_SYS 74j1zd36h5n96 localhost.localdomain
1441 MW_SYS 74j1zd36h5n96 localhost.localdomain
1730 MW_SYS 74j1zd36h5n96 localhost.localdomain
1946 MW_SYS 74j1zd36h5n96 localhost.localdomain
1969 MW_SYS 74j1zd36h5n96 localhost.localdomain
1993 MW_SYS 74j1zd36h5n96 localhost.localdomain
1994 MW_SYS 74j1zd36h5n96 localhost.localdomain
2041 MW_SYS 74j1zd36h5n96 localhost.localdomain
2042 MW_SYS 74j1zd36h5n96 localhost.localdomain
2066 MW_SYS 74j1zd36h5n96 localhost.localdomain
2067 MW_SYS 74j1zd36h5n96 localhost.localdomain
2091 MW_SYS 74j1zd36h5n96 localhost.localdomain
2114 MW_SYS 74j1zd36h5n96 localhost.localdomain
2137 MW_SYS 74j1zd36h5n96 localhost.localdomain
2138 MW_SYS 74j1zd36h5n96 localhost.localdomain
2161 MW_SYS 74j1zd36h5n96 localhost.localdomain
2162 MW_SYS 74j1zd36h5n96 localhost.localdomain
2209 MW_SYS 74j1zd36h5n96 localhost.localdomain
2233 MW_SYS 74j1zd36h5n96 localhost.localdomain
24 rows selected.

4、查看SQL语句74j1zd36h5n96文本信息

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语句74j1zd36h5n96的执行计划

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 74j1zd36h5n96, 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语句74j1zd36h5n96要保留的数据量

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语句74j1zd36h5n96要删除的数据量

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语句删除的数据没有交集。