We are using version 11.2.0.4 of oracle. Today we got complain from users stating that the application is just getting Hung. In database we see there were lot of session blocking each other, and the wait were "row lock contention" all over. So we killed the session to get the application back into work. but what we see there is a plsql procedure containing multiple UPDATE/DELETE statements and some of those are happening in cursor LOOP. And what i find from dba_hist_active_session_history there are 4 sql statements called inside that procedure which were running for 1hr+ which used to finish in minutes. And three of them were UPDATE and another one was "DELETE" and all were showing as waiting on "rowlock contention". and i tried to get back and see what those blocking session were doing, and it seems to me same queries were blocking each other.
But then i am not finding the start of the issue. I am trying to get to the root by tracking those blocking session , so that i would be able to know, if any specific query initially took longer paralyzing the application by creating a chain effect of "row lock contention". Is there anyway i can track the root/trigger point of this issue or the query which initially took time so the UPDATE/DELTE keep hold of the rows rather committing the transaction in faster time, and other request from users were just keep on waiting on "row lock contention" causing the application hung/crash.