Hi Tom,
This is regarding locking issue in database. One fine Monday morning client asked me to check slowness in the system reported by customer on Saturday.

ASH report looks like as below for issue period.

Blocking Sid (Inst) % Activity Event Caused % Event User Program
1132, 3397( 2) 9.12 enq: TX - row lock contention 9.12 ** NOT FOUND ** BLOCKING SESSION NOT FOUND


From report its clear that there was some row lock contention for almost 2 hours(same update query was present in AWR for almost 2 hours)
So I run below query on table (DBA_HIST_ACTIVE_SESS_HISTORY) to check further:

SELECT DISTINCT A.SQL_ID,TO_CHAR(A.SAMPLE_TIME,'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,A.BLOCKING_SESSION,D.OBJECT_NAME
FROM DBA_HIST_ACTIVE_SESS_HISTORY A, GV$SQL S, DBA_OBJECTS D
WHERE A.SQL_ID=S.SQL_ID
AND BLOCKING_SESSION IS NOT NULL
AND A.USER_ID <> 0
AND A.CURRENT_OBJ# = D.OBJECT_ID
AND A.SAMPLE_TIME BETWEEN
TO_TIMESTAMP('06.11.2021 13:00:00', 'dd.mm.yyyy hh24:mi:ss') AND
TO_TIMESTAMP('06.11.2021 16:40:59', 'dd.mm.yyyy hh24:mi:ss')
AND A.EVENT = 'enq: TX - row lock contention'
ORDER BY SAMPLE_TIME DESC;

and output confirmed the same that blocking session is 1132 (as shown in ASH report). Now when I queried the same table with session Id 1132, sample output is below:

session Id sql_id
1132 aykcpznxu0k8c
1132 cgj479fjtszs0
1132 8ryy5pw5gjbn6
1132 0pcgv72dynnfv
1132 2npxyk4vq7575

So here I am stuck and my question is as below:

1. Since multiple sqls are running with same session id, how to uniquely identify the blocking sql.

2. All above sqls with session id 1132 are select statement. So can a select statement cause lock if query runs for 30 minute and in between underlying data changes.

Thanks