determining bind values in deadlock situations

From: Barun, Vlado <>
Date: Fri, 20 Feb 2009 20:33:51 -0500
Message-ID: <>

Environment: RAC/ASM on Solaris 10

I'm trying to find an efficient way to get the bind values for statements that are involved in a deadlock situation.

So far, I'm able to get the bind values for statements that are part of the session that encounters the ora-60 by setting event='name errorstack level 2, forever'.

The problem is forcing an errorstack on the other session that is involved in the deadlock, the one that does not get the ora-60.

I have come with several possibilities, but none of them seem to be even efficient or provide the correct bind values:

  1. Write some code that parses the lmd0 trace, finds the session of interest and dump an errorstack. Problem: requires writing a lmd0 parser.
  2. Enable sql level auditing on the tables of interest. Problem: captures huge number of statements and that could cause other issues (ie storage, performance overhead,...).
  3. Enable 10046 trace database wide Problem: same as #2
  4. AFTER SERVERERROR trigger that queries v$sql_bind_capture on when ora 60 is raised Problem: bind values in v$sql_bind_capture are captured only every 15 minutes (default setting for _cursor_bind_capture_interval)
  5. dump errorstack for all sessions Problem: Don't know how to do that except for looping through gv$session and dumping error stacks one session at a time...

I have opened an SR, but the analyst is no help, he didn't even know that errorstack level 2 contains bind values...


Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations Jewelry Television
Mobile: 865 335 7652

Received on Fri Feb 20 2009 - 19:33:51 CST

Original text of this message