Thanks for the question, Joe.
Asked: March 28, 2017 - 3:18 am UTC
Last updated: March 28, 2017 - 12:21 pm UTC
Version: 11.2.0.4
Viewed 10K+ times! This question is 
You Asked
Hi Team,
As we known 'enq: TX - row lock contention' often happened during a DBA lifetime, and Tom used to say that 'The number one cause of deadlocks in the Oracle database, in my experience, is unindexed foreign keys. The number two cause is bitmap indexes on tables subject to concurrent updates.'.
Well, we could find the troublemaker out via dynamic views(v$lock, v$session, v$locked_object, v$process etc.) or AWR report row lock segments, but I stll have some doubts:
1) Is deadlock recognized as kind of 'enq: TX - row lock contention' in AWR?
2) If we want to figure out which SQL or PL/SQL caused database DML blocked a few days ago (rather than right this moment), but the suspect SQL or PL/SQL is not being captured by AWR (hourly) report, what shall we do to find it out quickly? Any system view could help or shall we records these via script?
3) In certain bad coding client-server senario, if the client hangs(say windows client program) and the terminal could do nothing but wait, it may cause session DML blocked if the PMON did not release resource in time. We used to set the DCD in sqlnet to eliminate the session who is hanged. However, the minimal value of DCD is 1 minite, to a busy DB, 1 minite is quite a long time, what's your advice to minimize this kind of impact?
Any advice is appreciated.
and Connor said...
Well, a couple of things here in play.
There's deadlocks (ie, no-one will escape, and hence we'll terminate the active statement on one of the sessions), and just being blocked on a lock.
If its a deadlock, we'll dump a tracefile with all sort of details in it.
If its just a plain blocking lock, then we're not going to capture anything to trace files. However, if you are licensed for it, you'll most probably see it in DBA_HIST_ACTIVE_SESS_HISTORY, which is a sampled subset of the data you see in V$ACTIVE_SESSION_HISTORY.
For (3), there is a resource manager plan you can use to be more proactive. You can set a plan that includes the MAX_IDLE_BLOCKER_TIME parameter which will kill a session that is idle but is blocking another session. (You might need to be on 12c for that one)
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment