您好,登錄后才能下訂單哦!
近期,一金融客戶oracle 11.2.0.4 rac集群delete不當(dāng)導(dǎo)致等待事件enq: TM - contention嚴(yán)重引起大范圍會(huì)話堆積,記錄的相關(guān)分析工作如下。
1、登錄集群任意節(jié)點(diǎn),查看集群全局等待事件
SQL> select event,count(*) from gv$session where wait_class<>'Idle' group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
enq: TM - contention 24
2、查看等待事件在兩個(gè)節(jié)點(diǎn)的分布
節(jié)點(diǎn)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
節(jié)點(diǎn)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等待事件的會(huì)話信息
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語(yǔ)句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語(yǔ)句74j1zd36h5n96的執(zhí)行計(jì)劃
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語(yǔ)句74j1zd36h5n96要保留的數(shù)據(jù)量
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語(yǔ)句74j1zd36h5n96要?jiǎng)h除的數(shù)據(jù)量
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、問(wèn)題
由以上信息可知,24個(gè)會(huì)話分布在兩個(gè)節(jié)點(diǎn)上通知對(duì)同一張表執(zhí)行相同的delete操作,而且刪除的數(shù)據(jù)量是大范圍的,只保留2條記錄,
因此導(dǎo)致嚴(yán)重的enq:TM鎖。
9、建議
delete操作分批執(zhí)行,且控制會(huì)話間執(zhí)行的delete語(yǔ)句刪除的數(shù)據(jù)沒有交集。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。