Hi, experts
Yesterday afternoon our production system (oracle db server 11.2.0.4.0 for Linux x86_64) has produced a large amount of wait event 'enq: TX - row lock contention'. Please take a look at my several screenshots as follows,
To restore business as soon as possible, afterwards I manually killed all of the OS processes that produced row lock contention immediately. The following is that command I executed on Linux,
select 'kill -9 '||pro.spid from v$session ses,v$process pro where ses.paddr=pro.addr and ses.event='enq: TX - row lock contention';
Now I wanna detailedly and seriously analyze (or try to trouble-shooting) this wait event "enq: TX - row lock contention".
Therefore I've done some operation steps below,
SQL> set linesize 200
SQL> set pagesize 80
SQL> col name for a35
SQL> col parameter1 for a10
SQL> col parameter2 for a15
SQL> col parameter3 for a10
SQL> col wait_class for a15
SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name
2 where name = 'enq: TX - row lock contention';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
----------------------------------- ---------- --------------- ---------- ---------------
enq: TX - row lock contention name|mode usn<<16 | slot sequence Application
SQL> select p1,p2,p3,count(*) from dba_hist_active_sess_history where event = 'enq: TX - row lock contention'
2 and sample_time between to_date('2018-06-04 14:25:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-06-04 15:00:00','yyyy-mm-dd hh24:mi:ss')
3 group by p1,p2,p3;
P1 P2 P3 COUNT(\*)
---------- ---------- ---------- ----------
1415053318 520552481 119254 637908
SQL> set linesize 200
SQL> set pagesize 80
SQL> select sql_id,p1,p2,p3,count(*) from dba_hist_active_sess_history where event = 'enq: TX - row lock contention'
2 and sample_time between to_date('2018-06-04 14:25:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-06-04 15:00:00','yyyy-mm-dd hh24:mi:ss')
3 group by sql_id,p1,p2,p3
4 order by count(*) desc,sql_id;
SQL_ID P1 P2 P3 COUNT(*)
-------------------------- ---------- ---------- ---------- ----------
63954ds96t1f7 1415053318 520552481 119254 595737
22k24vv4ympzt 1415053318 520552481 119254 37401
cby0uf10phk6s 1415053318 520552481 119254 1169
136232yf7z8dx 1415053318 520552481 119254 919
aykqhhdaj2jzf 1415053318 520552481 119254 428
btq5r9uuyt6uz 1415053318 520552481 119254 402
gvabkmzyn0b5b 1415053318 520552481 119254 313
cwh6q2r5w91nm 1415053318 520552481 119254 244
6gvybub3khmwv 1415053318 520552481 119254 207
fvuprmvsd0nah 1415053318 520552481 119254 198
b0x6hj9j6tkwp 1415053318 520552481 119254 158
5fpgrfk0zn731 1415053318 520552481 119254 139
4247tzrpqxyfy 1415053318 520552481 119254 116
9mdz97fybw44f 1415053318 520552481 119254 106
5z0kdxr784w0f 1415053318 520552481 119254 102
d4n09t7xwa60f 1415053318 520552481 119254 78
a9tbydrg7yac8 1415053318 520552481 119254 60
99bnmc0h62qc5 1415053318 520552481 119254 44
62b3h3f8bry9u 1415053318 520552481 119254 35
afb1jdqkuuafn 1415053318 520552481 119254 34
9x35f91jfazjt 1415053318 520552481 119254 18
21 rows selected.
SQL> SELECT D.SQL_ID,CHR(BITAND(P1, -16777216) / 16777215) || CHR(BITAND(P1, 16711680) / 65535) "Lock",
2 BITAND(P1, 65535) "Mode", COUNT(1),COUNT(DISTINCT d.session_id)
3 FROM DBA_HIST_ACTIVE_SESS_HISTORY D
4 WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2018-06-04 14:25:00', 'YYYY-MM-DD HH24:MI:SS') AND
5 TO_DATE('2018-06-04 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
6 AND D.EVENT = 'enq: TX - row lock contention'
GROUP BY D.SQL_ID,(CHR(BITAND(P1, -16777216) / 16777215) || CHR(BITAND(P1, 16711680) / 65535)),(BITAND(P1, 65535))
8 ORDER BY COUNT(1) DESC,COUNT(DISTINCT d.session_id) DESC;
SQL_ID Lock Mode COUNT(1) COUNT(DISTINCTD.SESSION_ID)
-------------------------- ---------------- ---------- ---------- ---------------------------
63954ds96t1f7 TX 6 595737 8802
22k24vv4ympzt TX 6 37401 477
cby0uf10phk6s TX 6 1169 10
136232yf7z8dx TX 6 919 7
aykqhhdaj2jzf TX 6 428 4
btq5r9uuyt6uz TX 6 402 3
gvabkmzyn0b5b TX 6 313 3
cwh6q2r5w91nm TX 6 244 2
6gvybub3khmwv TX 6 207 5
fvuprmvsd0nah TX 6 198 2
b0x6hj9j6tkwp TX 6 158 1
5fpgrfk0zn731 TX 6 139 1
4247tzrpqxyfy TX 6 116 2
9mdz97fybw44f TX 6 106 4
5z0kdxr784w0f TX 6 102 1
d4n09t7xwa60f TX 6 78 1
a9tbydrg7yac8 TX 6 60 4
99bnmc0h62qc5 TX 6 44 1
62b3h3f8bry9u TX 6 35 1
afb1jdqkuuafn TX 6 34 1
9x35f91jfazjt TX 6 18 1
21 rows selected.
And next, how to do it continuously for me ?
Very appreciate you if any help.
Best Regards
Quanwen Zhao