Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alok.

Asked: November 17, 2021 - 12:39 pm UTC

Last updated: November 18, 2021 - 5:17 am UTC

Version: 11.2.0.4

Viewed 100+ times

You Asked

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

and Connor said...

A select statement wont cause a lock *unless* it is SELECT FOR UPDATE.

But here's the issue - you don't have to *active* to be a blocker, and it could easily be the case that you will not be present in active session history at all.

Example:

Session 1:
- update table set col = 1 where primary_key = [value]

That will run in microseconds and will most likely never appear in v$ash because its instantaneous

Session 2
- update table set other_col = 1 0

This session will be stuck on session 1, you will see session 2 in ASH over and over and over (because its stuck) but there will never be an entry for session 1's SQL because it will never be captured.

In particular, session 1 may now be running hundreds of SELECTs, all of which *might* be in ASH, but they have no bearing on that update statement which has been left hanging.

You *might* be able to get some inferences by looking at V$TRANSACTION and comparing START_DATE to LAST_ACTIVE in V$SQL.





More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database