RE: determining bind values in deadlock situations

From: Yong Huang <>
Date: Tue, 3 Mar 2009 14:23:54 -0800 (PST)
Message-ID: <>

Thanks for that patch number. Bug 4441119 says it's fixed in We use on x86_64 Linux. Indeed, the SQLs are shown in the trace file even if the sessions involved in the deadlock are on the same instance. But the bind variables are still not shown if the SQLs use bind variables.

Nothing beats level 4 10046 trace when you need bind variable values. There're many limitations about v$sql_bind_capture. Here's a list of them I'm aware of:

V$SQL_BIND_CAPTURE only captures bind variable values during a hard parse, a soft parse that creates a new child cursor, or if the last capture was _CURSOR_BIND_CAPTURE_INTERVAL seconds or longer ago, column type is not LONG or LOB, and bind variables in the select list are ignored. If it's not the case no values are captured at all, it's also possible _CURSOR_BIND_CAPTURE_AREA_SIZE needs to be increased.

Yong Huang

  • On Tue, 3/3/09, Barun, Vlado <> wrote:

> From: Barun, Vlado <>
> Subject: RE: determining bind values in deadlock situations
> To: "" <>
> Cc: "" <>
> Date: Tuesday, March 3, 2009, 3:39 PM
> To get all the SQL's apply patch 4441119.
> V$sql_bind_capture only captures bind values every 15
> minutes for a statement, so in situations where the bind
> values change frequently, this is no use...
> Regards,
> Vlado Barun, M.Sc.
> Sr. Manager, Database Engineering and Operations
> Jewelry Television
> Mobile: 865 335 7652
> Email:
> -----Original Message-----
> From: Yong Huang []
> Sent: Tuesday, March 03, 2009 1:19 PM
> To: Barun, Vlado
> Cc:
> Subject: RE: determining bind values in deadlock situations
> > There is no trace file generated for a deadlock in
> udump on any of my RAC nodes...
> Vlado,
> In RAC, you need to check lmd trace files in bdump for the
> deadlock trace.
> Bind variable values are not available in the trace. In
> fact, if the involved sessions are all on the same node,
> even the SQL is not in the trace. If you do get the SQL, you
> may be able to find the SQL in v$sql% view(s), and may find
> the bind variable value in v$sql_bind_capture.
> Yong Huang

Received on Tue Mar 03 2009 - 16:23:54 CST

Original text of this message