AWR "enq: TX - row lock contention" Wait Event

=============================
General
=============================
Checking AWR, Oracle is spending a lot of time in "enq: TX - row lock contention" waits.
How to troubleshoot?

                                                   Avg        %Total

                                   %Tim Total Wait wait Waits Call

Event                        Waits  out   Time (s) (ms) /txn  Time

---------------------------- ----- ---- ---------- ---- ----- ------

enq: TX - row lock contentio    21    0         85 4049   0.0     .2

1. Get the sessions that are in waiting state.
2. Get the blocking session of these sessions.
3. See what the blocking session is running

=============================
To get the history SQLs in waiting state:

=============================

Get summary for last intervals

SELECT LOCKS_SUMMARY.locks, 

       LOCKS_SUMMARY.snap_id, 

       LOCKS_SUMMARY.sample_time, 

       LOCKS_SUMMARY.sql_id, 

       SQLAREA.sql_fulltext

  FROM V$SQLAREA SQLAREA, 

(SELECT COUNT(*) as locks, 

       snap_id, TO_CHAR(sample_time,'YYYYMMDD hh24') sample_time, 

       sql_id

  FROM dba_hist_active_sess_history hist

 WHERE wait_class != 'Idle' 

   AND event = 'enq: TX - row lock contention'

GROUP BY snap_id, TO_CHAR(sample_time,'YYYYMMDD hh24'),sql_id

 ORDER BY snap_id DESC,  COUNT(*) DESC

  ) LOCKS_SUMMARY

WHERE SQLAREA.sql_id = LOCKS_SUMMARY.sql_id

ORDER BY 2 desc, 1 desc, 3;

Get details for last intervals

SELECT snap_id, sample_time, session_id,  

       SQLAREA.sql_id, 

       SQLAREA.sql_text

  FROM dba_hist_active_sess_history hist, 

       v$sqlarea SQLAREA

 WHERE wait_class != 'Idle' 

   AND event = 'enq: TX - row lock contention'

   AND SQLAREA.sql_id = hist.sql_id

 ORDER BY snap_id DESC, sample_time DESC;

 

Get details for last interval

SELECT snap_id, session_type, sql_id, event, program , machine, port

  FROM dba_hist_active_sess_history

 WHERE snap_id = (SELECT MAX(snap_id) FROM DBA_HIST_SNAPSHOT)

   AND event = 'enq: TX - row lock contention';

=============================
To get the current SQLs in waiting state:
============================= 
set linesize 140
set pagesize 1000

SELECT SESSIONS.sid, 

       SESSIONS.sql_id, 

       SUBSTR(SQLS.sql_text ,1,30) as sql_text,

       SESSIONS.program, 

       SESSIONS.schemaname, 

       SESSIONS.machine, 

       SESSIONS.port

  FROM V$SESSION SESSIONS,

       V$SQL SQLS 

 WHERE SESSIONS.sid IN 

       (SELECT sid  

          FROM V$SESSION 

         WHERE state in ('WAITING') 

           AND wait_class != 'Idle' 

           AND event='enq: TX - row lock contention'

   AND schemaname IS NOT NULL

        )

   AND  SQLS.sql_id = SESSIONS.sql_id;

============================= 
To get the Blocking session:
============================= 

SELECT blocking_session, 

       sid, 

       serial#, 

       wait_class, 

       seconds_in_wait 
  FROM V$SESSION 
 WHERE blocking_session is not NULL 
ORDER BY blocking_session;

Output is missing, but blocking session was 401.

============================= 
See what blocking session is running:
============================= 
SELECT USERNAME, SQL_ID FROM V$SESSION WHERE blocking_session=401;

USERNAME                       SQL_ID
------------------------------ -------------
MY_USER                        d94vtj1qqdvct


SELECT SQL_TEXT FROM V$SQLAREA
 WHERE sql_id = 'd94vtj1qqdvct';

SQL_TEXT
-------------------------------------------------------------------------------------
MERGE into SGA_W_SUBSCRIBER o using (select :1  MSISDN from dual) o1 on (o.MSISDN = o1.MSISDN) when matched then updateset o.IMSI= :2 ,o.DYNAMIC_COMMUNITIES =:3 ,o.CURRENT_COUNTRY= :4 ,o.CURRENT_NETWORK_ID = :5  , o.CURRENT_VLR = :6  ,o.CURRENT_MSC = :7  , o.TS_FIRST_REGISTRATION = :8  ,o.TS_E_LAST_LU = :9  , o.IS_ROAMING = :10 , o.TS_LAST_ROAMER_EVENT = :11 , o.CURRENT_SGSN = :12   ,o.NON_PURGEABLE_COUNT = :13 , o.PREVIOUS_VLR=:14 ,o.PREVIOUS_SGSN=:15 , o.TS_E_LAST_CANCEL=:16 , o.TS_E_LAST_PURGE=:17 , o.TS_E_LAST_SRI4SM_REQ=:18 , o.TS_VISIT_START=:19 , o.TS_VISIT_END=:20 , o.TS_LAST_MODIFIED=:21 , o.MAP_CATEGORY=:22 , o.MAP_CAMEL_PHASE=:23 , o.MAP_CAMEL_SERVICE_KEY=:24 , o.MAP_BARRING_INDICATION=:25 , o.MAP_BARRING_INDICATION_TS=:26 , o.MAP_FTN_BUSY=:27 , o.MAP_FTN_BUSY_TS=:28 , o.MAP_FTN_BUSY_TEL_SERVICE=:29 , o.MAP_FTN_BUSY_STATUS=:30 , o.MAP_FTN_NOREPLY=:31 , o.MAP_FTN_NOREPLY_TS=:32 , o.MAP_FTN_NOREPLY_TEL_SERVICE=:33 , o.MAP_FTN_NOREPLY_STATUS=:34 , o.MAP_FTN_NOREPLY_TIMEOUT=:

============================= 
See locks by sql_id:
============================= 

1st

SELECT sample_id,

       TO_CHAR(sample_time,'YYYYMMDD hh24:mi') as sample_min,

       LOCKS_HIST.session_id as locked_sid,

       LOCKS_HIST.session_serial# as locked_serial,

       LOCKS_HIST.sql_id,

       LOCKS_HIST.sql_exec_start,

       LOCKS_HIST.event,

       LOCKS_HIST.session_state,

       LOCKS_HIST.blocking_session,

       LOCKS_HIST.program,

       LOCKS_HIST.module, 

       LOCKS_HIST.machine,

       LOCKS_HIST.port,

       LOCKS_HIST.session_state,

       LOCKS_HIST.blocking_session,

       LOCKS_HIST.blocking_session_serial#

  FROM DBA_HIST_ACTIVE_SESS_HISTORY LOCKS_HIST

 WHERE sql_id = '2xkrv2tcm98sn'

   AND sample_time > TRUNC(SYSDATE);

2nd.
Take blocking_sessions from 1st SQL

SELECT LOCKS_HIST.program,

       LOCKS_HIST.module, 

       LOCKS_HIST.machine,

       LOCKS_HIST.port

  FROM DBA_HIST_ACTIVE_SESS_HISTORY LOCKS_HIST 

 WHERE session_id IN (2331, 1152, 969, 2331);

3rd

SELECT CURRENT_SESSIONS.program,

       CURRENT_SESSIONS.module, 

       CURRENT_SESSIONS.machine,

       CURRENT_SESSIONS.port

  FROM V$SESSION CURRENT_SESSIONS 

WHERE sid IN (2331, 1152, 969, 2331);