Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Nels QuinnOct 21 2015 — edited Oct 22 2015

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...
This post has been answered by Hemant K Chitale on Oct 21 2015
Jump to Answer

Comments

unknown-951199

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

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

unknown-951199

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+identify+sql+holding+lock

Hemant K Chitale
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

Marked as Answer by Nels Quinn · Sep 27 2020
1 person found this helpful
Dom Brooks

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

2 people found this helpful
Jonathan Lewis

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

1 person found this helpful
Nels Quinn

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

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

That does seem to be an apt analogy...

1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 19 2015
Added on Oct 21 2015
#general-database-discussions
9 comments
85,373 views