Shulou(Shulou.com)06/01 Report--

Recently, a financial customer oracle 11.2.0.4 rac cluster delete improperly led to a waiting event enq: TM-contention seriously caused a large-scale session accumulation, the related analysis of the record is as follows.

1. Log in to any node of the cluster and view the cluster global wait event

SQL > select event,count (*) from gv$session where wait_class'Idle' group by event

EVENT COUNT (*)

-

Enq: TM-contention 24

2. View the distribution of waiting events on two nodes

Node 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

Node 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. View the session information that caused the enq: TM-contention wait event

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. View the 74j1zd36h5n96 text information of SQL statements

SQL_FULLTEXT

Delete from mw_sys.mwt_is_user m where m.user_id not in ('0D3C0ACD-C95B-42DA-A5DE

-70F019DAB52Bao Magazine 8697D72EmurC77Bmur4C70lyA434lyED7D488ADC36')

5. View the execution plan of the SQL statement 74j1zd36h5n96

PLAN_TABLE_OUTPUT

-

SQL_ID 74j1zd36h5n96, child number 0

-

Delete from mw_sys.mwt_is_user m where m.user_id not

In ('0D3C0ACDMui C95B Mustang 42DAMui A5DEMY70F019DAB52B019DAB52BLY 8697D72ELY C77BMY 4C70Mui A434lifED7D4

88ADC36')

Plan hash value: 3271715000

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | DELETE STATEMENT | 210100 | | |

| | 1 | DELETE | MWT_IS_USER |

| | * 2 | TABLE ACCESS FULL | MWT_IS_USER | 18739 | 951K | 210K (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" '0D3C0ACDMY C95BMY 42DALIY A5DELY 70F019DAB52B' AND

"M". "USER_ID" '8697D72EmurC77BMuI4C70MuthA434MurED7D488ADC36')

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. Query the amount of data to be retained by the SQL statement 74j1zd36h5n96

SQL > select count (*) from mw_sys.mwt_is_user where user_id = '0D3C0ACDMY C95B Mel 42DALY A5DELY 70F019DAB52B'

COUNT (*)

-

one

SQL >

SQL > select count (*) from mw_sys.mwt_is_user where user_id = '8697D72ElyC77B, 4C70lyA434, ED7D488ADC36'

COUNT (*)

-

one

7. View the amount of data to be deleted by the SQL statement 74j1zd36h5n96

SQL > select count (*) from mw_sys.mwt_is_user m where m.user_id not in ('0D3C0ACD-C95B-42DA-A5DE

-70F019DAB52Baimyo Magnum 8697D72EmurC77BMush4C70lle A434Lay ED7D488ADC36'); 2

COUNT (*)

-

18740

8. Question

As can be seen from the above information, 24 sessions are distributed on two nodes to notify the same table to perform the same delete operation, and the amount of data deleted is large, leaving only 2 records.

This results in a serious enq:TM lock.

9. Suggestions

Delete operations are executed in batches, and data deleted by delete statements that control execution between sessions is not intersected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.