Blocking Locks
Oracle 9.2.0.8
Our database is used as backend for IIS Servers. Several times per week we experience Blocking locks situation when we could have around 300 – 400 session waiting. Most time when somebody fire up some Update or Delete process. Every time when I check dba_waiters table during this it gives me 10 – 20 holding session. After killing them all things go back to normal.
My question how do I know which one out of this 10 – 20 session is first or real offender. The reason I asked that sometimes after checking SQL’s from several holding sessions they different. And I am not sure which one is the real issue. If I could find this I would be able to go back to developers and see if they missed commit or something in code.