Re: Unable to detect blocking session

On 2018/02/21 09:32, Sandra Becker wrote:

> What we are trying to understand is why we were unable to see that the user's sqlplus session was blocking either through EM or through queries looking at gv$session and gv$session_blockers. We found the application locks by joining gv$lock, gv$session and sys.dbms_lock_allocated. Any ideas why we could see the blocking or suggestions on what we can look at so we don't miss it again?

EM12 has problems with blocking lock detection, as it relies on the target's buggy DBA_OUTSTANDING_ALERTS view (or something related). I've had a chain of several SRs open regarding the entries found in that view for DB 11.2.

EM13 (at least EM13R2) thankfully ignores this and goes directly against "V$LOCK" (not "GV$LOCK", at least according to https://docs.oracle.com/cd/E73210_01/EMDBM/GUID-A530335C-B818-423E-83CE-613B263C242F.htm#oracle_database_userblock ).

So, I would hope that if you're running EM13, that you should see something. Was this a TX lock? And, if so, were your thresholds and collection schedules set for "User Locks"->"Maximum Blocked DB Time (seconds)"->"TX"?

Rich

--
http://www.freelists.org/webpage/oracle-l