=============================
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);
