Forum Stats

  • 3,741,218 Users
  • 2,248,393 Discussions
  • 7,861,681 Comments

Discussions

How to find which SQL is holding a lock when its not v$session.sql_id or v$session.prev_sql_id

Nels Quinn
Nels Quinn Member Posts: 11
edited Oct 22, 2015 1:06PM in General Database Discussions

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...









Jonathan LewisNels Quinn

Best Answer

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,755 Blue Diamond
    edited Oct 21, 2015 10:47PM Accepted Answer

    A session may issue a DML followed by 5 queries without issuing a commit.

    Another session may be attempting a DML when the 5th query of the above session is running.  Querying V$session for the first session's SQL_ID or PREV_SQL_ID wouldn't tell you the first DML SQL.

    You could walk backwards through V$ACTIVE_SESSION_HISTORY  (if you have the Diagnostic Pack licence) and determine all the SQL_IDs of that first session if they did get captured in V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY.  (Warning : Not all SQLs are captured -- particularly short-running SQLs may not get captured)

    Hemant K Chitale

    Nels Quinn

Answers

  • Unknown
    edited Oct 21, 2015 6:39PM

    SELECT Decode(request, 0, 'Holder: ',

      'Waiter: ')

      ||vl.sid sess,

      status,

      id1,

      id2,

      lmode,

      request,

      vl.TYPE

    FROM v$lock vl,

      v$session vs

    WHERE ( id1, id2, vl.TYPE ) IN (SELECT id1,

      id2,

      TYPE

      FROM v$lock

      WHERE request > 0)

      AND vl.sid = vs.sid

    ORDER BY id1,

      request

  • Nels Quinn
    Nels Quinn Member Posts: 11
    edited Oct 21, 2015 7:04PM

    Thanks sol.beach.  I appreciate the reply. 

    But this doesn't seem to help answer my question...  It does tell me which sid is the blocker and who's getting blocked - (which is good) but I'm already getting that from gv$session...  What I'm not able to determine is the actual SQL statement causing the lock.  In most cases - I would simply get that information from the SQL_ID / PREV_SQL_ID columns of gv$session - but they don't have what I need in this case.

    Thanks all the same for your time...

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,755 Blue Diamond
    edited Oct 21, 2015 10:47PM Accepted Answer

    A session may issue a DML followed by 5 queries without issuing a commit.

    Another session may be attempting a DML when the 5th query of the above session is running.  Querying V$session for the first session's SQL_ID or PREV_SQL_ID wouldn't tell you the first DML SQL.

    You could walk backwards through V$ACTIVE_SESSION_HISTORY  (if you have the Diagnostic Pack licence) and determine all the SQL_IDs of that first session if they did get captured in V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY.  (Warning : Not all SQLs are captured -- particularly short-running SQLs may not get captured)

    Hemant K Chitale

    Nels Quinn
  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited Oct 22, 2015 8:18AM

    There is no guaranteed way.

    You may get lucky.

    You can find the details of the transaction holding the lock, use the XID of that transaction to find sampled activity in ASH.

    The statement responsible may or may not have been captured - depends on how long it took to run.

    For summary and collection of links (mainly from Doug Burns's series of posts on the matter) see:

    Data Virtualizer » Finding the blocking SQL in a lock wait

    Jonathan LewisNels QuinnNels Quinn
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,678 Gold Crown
    edited Oct 22, 2015 7:05AM

    Dom,

    Good choice of link - adnd there's an entertaining discussion in the comments that follow it. (Okay, about 50% from me, including a link to a simple demonstration of why it's not possible to guarantee that any search for the specific SQL will find it:  https://jonathanlewis.wordpress.com/2009/04/19/locking-sql/ )

    Regards

    Jonathan Lewis

    Nels Quinn
  • Nels Quinn
    Nels Quinn Member Posts: 11
    edited Oct 22, 2015 11:27AM

    Thanks to all for the great feedback and advice.  I will try out the ASH strategy at the next occurrence. I had been making semi-educated guesses by pulling all the session's open cursors in GV$OPEN_CURSOR and looking for those potentially involving the locked object and not doing simple SELECTs...  I think the ASH approach will be much more effective.

    It's a least reassuring to know that I'm a) not missing something obvious and b) not alone in my bafflement.

    Thanks again!

    Nels

  • jgarry
    jgarry Member Posts: 13,842
    edited Oct 22, 2015 12:49PM

    Somehow this makes me think about the cop at the side of the road, seeing a speeder.  He has no idea why or how long the person has been speeding, and most of the time that isn't important and he doesn't want to hear about it.  Even though sometimes the initial cause does make a difference in how he should respond, ie "The Baby Is Coming Out Right Now!"

  • Nels Quinn
    Nels Quinn Member Posts: 11
    edited Oct 22, 2015 1:06PM

    That does seem to be an apt analogy...

This discussion has been closed.