近期，一金融客户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语句删除的数据没有交集。
