I'm running a java application with on premise Oracle 19.c.
From time to time my application slows down and starts timing out due to session being blocked in oracle. I'm trying to investigate this I'd appreciate any more insights to what could be the problem.
I've ran:
select sid, blocking_session, sql_text from v$session s, v$sql q
where sid in (select sid from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));
received the following result:
|SID|BLOCKING_SESSION|SQL_TEXT |
|---|----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|157|177 |SELECT T55439.id, T55439.VERSION, T55439.CREATE_DATE, T55439.UPDATE_DATE, T55439.END_DATE, T55439.START_DATE, T55439.STATUS, T55439.IDENTIFICATION, T55439.PASSWORD, T55439.SESSION_ID, T55439.ACCESSED, T55439.ATTEMPTS, T55439.ON_BEHALF_OF, T55439.USER_AGENT, T55439.REMOTE_ADDR, T55439.role FROM SESSION_RECORD T55439 WHERE T55439.IDENTIFICATION = :1 AND T55439.END_DATE IS NULL ORDER BY T55439.CREATE_DATE ASC |
|158|177 |SELECT T55558.id, T55558.VERSION, T55558.CREATE_DATE, T55558.UPDATE_DATE, T55558.END_DATE, T55558.START_DATE, T55558.STATUS, T55558.IDENTIFICATION, T55558.PASSWORD, T55558.SESSION_ID, T55558.ACCESSED, T55558.ATTEMPTS, T55558.ON_BEHALF_OF, T55558.USER_AGENT, T55558.REMOTE_ADDR, T55558.role FROM SESSION_RECORD T55558 WHERE T55558.IDENTIFICATION = :1 AND T55558.END_DATE IS NULL ORDER BY T55558.CREATE_DATE ASC |
|179|177 |SELECT T51047.id, T51047.VERSION, T51047.CREATE_DATE, T51047.UPDATE_DATE, T51047.END_DATE, T51047.START_DATE, T51047.STATUS, T51047.IDENTIFICATION, T51047.PASSWORD, T51047.SESSION_ID, T51047.ACCESSED, T51047.ATTEMPTS, T51047.ON_BEHALF_OF, T51047.USER_AGENT, T51047.REMOTE_ADDR, T51047.role FROM SESSION_RECORD T51047 WHERE T51047.IDENTIFICATION = :1 AND T51047.END_DATE IS NULL ORDER BY T51047.CREATE_DATE ASC |
|183|177 |SELECT * FROM SESSION_RECORD WHERE id = :1 |
|183|177 |UPDATE SESSION_RECORD T55474 SET T55474.VERSION = T55474.VERSION + 1, T55474.CREATE_DATE = :1 , T55474.UPDATE_DATE = :2 , T55474.END_DATE = :3 , T55474.START_DATE = :4 , T55474.STATUS = :5 , T55474.IDENTIFICATION = :6 , T55474.PASSWORD = :7 , T55474.SESSION_ID = :8 , T55474.ACCESSED = :9 , T55474.ATTEMPTS = :10 , T55474.ON_BEHALF_OF = :11 , T55474.USER_AGENT = :12 , T55474.REMOTE_ADDR = :13 , T55474.role = :14 WHERE T55474.id = :15 AND T55474.VERSION = :16 |
|158|177 |UPDATE SESSION_RECORD T55559 SET T55559.VERSION = T55559.VERSION + 1, T55559.CREATE_DATE = :1 , T55559.UPDATE_DATE = :2 , T55559.END_DATE = :3 , T55559.START_DATE = :4 , T55559.STATUS = :5 , T55559.IDENTIFICATION = :6 , T55559.PASSWORD = :7 , T55559.SESSION_ID = :8 , T55559.ACCESSED = :9 , T55559.ATTEMPTS = :10 , T55559.ON_BEHALF_OF = :11 , T55559.USER_AGENT = :12 , T55559.REMOTE_ADDR = :13 , T55559.role = :14 WHERE T55559.id = :15 AND T55559.VERSION = :16 |
|157|177 |UPDATE SESSION_RECORD T55440 SET T55440.VERSION = T55440.VERSION + 1, T55440.CREATE_DATE = :1 , T55440.UPDATE_DATE = :2 , T55440.END_DATE = :3 , T55440.START_DATE = :4 , T55440.STATUS = :5 , T55440.IDENTIFICATION = :6 , T55440.PASSWORD = :7 , T55440.SESSION_ID = :8 , T55440.ACCESSED = :9 , T55440.ATTEMPTS = :10 , T55440.ON_BEHALF_OF = :11 , T55440.USER_AGENT = :12 , T55440.REMOTE_ADDR = :13 , T55440.role = :14 WHERE T55440.id = :15 AND T55440.VERSION = :16 |
|161|177 |UPDATE SESSION_RECORD T55521 SET T55521.VERSION = T55521.VERSION + 1, T55521.CREATE_DATE = :1 , T55521.UPDATE_DATE = :2 , T55521.END_DATE = :3 , T55521.START_DATE = :4 , T55521.STATUS = :5 , T55521.IDENTIFICATION = :6 , T55521.PASSWORD = :7 , T55521.SESSION_ID = :8 , T55521.ACCESSED = :9 , T55521.ATTEMPTS = :10 , T55521.ON_BEHALF_OF = :11 , T55521.USER_AGENT = :12 , T55521.REMOTE_ADDR = :13 , T55521.role = :14 WHERE T55521.id = :15 AND T55521.VERSION = :16 |
|179|177 |UPDATE SESSION_RECORD T51048 SET T51048.VERSION = T51048.VERSION + 1, T51048.CREATE_DATE = :1 , T51048.UPDATE_DATE = :2 , T51048.END_DATE = :3 , T51048.START_DATE = :4 , T51048.STATUS = :5 , T51048.IDENTIFICATION = :6 , T51048.PASSWORD = :7 , T51048.SESSION_ID = :8 , T51048.ACCESSED = :9 , T51048.ATTEMPTS = :10 , T51048.ON_BEHALF_OF = :11 , T51048.USER_AGENT = :12 , T51048.REMOTE_ADDR = :13 , T51048.role = :14 WHERE T51048.id = :15 AND T51048.VERSION = :16 |
|161|177 |SELECT T55520.id, T55520.VERSION, T55520.CREATE_DATE, T55520.UPDATE_DATE, T55520.END_DATE, T55520.START_DATE, T55520.STATUS, T55520.IDENTIFICATION, T55520.PASSWORD, T55520.SESSION_ID, T55520.ACCESSED, T55520.ATTEMPTS, T55520.ON_BEHALF_OF, T55520.USER_AGENT, T55520.REMOTE_ADDR, T55520.role FROM SESSION_RECORD T55520 WHERE T55520.IDENTIFICATION = :1 AND T55520.END_DATE IS NULL ORDER BY T55520.CREATE_DATE ASC |
I ran
select sid, sql_text from v$session s, v$sql q
where sid in (select blocking_session from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));
the result was:
|SID|SQL_TEXT |
|---|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|177|SELECT T55560.CREATE_DATE, T55560.CREATOR_USER_ID, T55560.UPDATE_DATE, T55560.UPDATOR_USER_ID, T55560.VERSION, T55560.END_DATE, T55560.START_DATE, T55560.IS_OBSOLETE, T55560.TIME_STAMP, T55560.id, T55560.USER_PASSWORD, T55560.USER_NAME, T55560.LAST_PASSWORD_CHANGE, T55560.IS_ACTIVE, T55560.SEND_TYPE, T55560.UNIQUE_DETAIL, T55560.ALLOWED_SESSIONS, T55560.MANAGER_USER_ID, T55560.PERSON_ID, T55560.LOCALE FROM EMI_USER T55560 WHERE 1=1 AND T55560.IS_OBSOLETE = :1 AND T55560.USER_NAME LIKE :2 AND T55560.SEND_TYPE = :3 |
It seems that all the sessions are blocked by session 177, however session 177 is idle and was a select so should not be holding any locks.
So what I'm asking is how can it be that a select statement blocked all these other statements? What should I be looking for?
Thanks.