Hi all,
Looking for a nudge in the right direction on a puzzling problem with blocking locks in our EBS database...
We occasionally will experience blocking locks in our EBS 12.1.3 database (11.2.0.4 RAC on OEL5). I can easily determine the INST_ID and SID holding the lock with this:
select final_blocking_instance, final_blocking_session from gv$session where lockwait is not null;
and can use these results to find the specific object being blocked by querying gv$locked_object where session_id = xxx and inst_id = xxx. These blocker sessions are typically inactive with an event of "SQL*Net message from client" - so they seem to be users connected to the database who simply need to commit or rollback their transactions. Usually when I see this type of behavior, the specific query involved in the unresolved transaction is easily found by taking the SQL_ID (or sometimes the PREV_SQL_ID) value from gv$session and querying gv$sql. I can then use this info to better understand what the users are trying to accomplish and help them understand what they need to change to avoid the blocking problem.
But recently we have integrated the Oracle Content Management add-on and it appears to have added (I suspect indirectly) triggers to many of the EBS forms our users work in. Now we are experiencing these "idle form blocking locks" - and the value of SQL_ID is null and PREV_SQL_ID's related sql_text is
SELECT * FROM AXF_COMMANDS_SYN WHERE FORMID = :b1 AND ( MENUTYPE = 'SPECIAL' OR MENUTYPE = 'ZOOMANDSPECIAL' ) AND AXF_COMMANDS_SYN.SPECIAL IS NOT NULL
though I can see from both gv$locked_object and the text of the SQL being executed by the blocked sessions that the actual lock being held by the blocker session is on an object (RA_CUSTOMER_TRX_ALL for example) that doesn't appear to be at all related to AXF_COMMANDS_SYN. The AXF schema was added as part of the content management deployment.
So my thought is that another (previous) open cursor held by the session is the actual unresolved SQL that is holding the lock. Is there a way to determine the SQL_ID of this query "indirectly" from gv$locked_object / gv$transaction, gv$open_cursor, etc? I can determine which session is the problem and which user we need to talk to - but I would really like to find the exact SQL that's involved. I'm also concerned with determining whether the new behavior is somehow contributing to the blocking lock problems or just "a red herring" - and I believe finding out which SQL statement is holding the lock will help me move forward with that investigation. I'm pretty sure I could find this info by tracing the blocking session - but the problem happens infrequently and I'm not able to determine which session to trace until its too late... :-)
Any recommendations would be appreciated and apologies for the long post...