RE: determining bind values in deadlock situations

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: "Barun, Vlado" <Vlado.Barun@xxxxxxx>
  • Date: Tue, 3 Mar 2009 14:23:54 -0800 (PST)

Thanks for that patch number. Bug 4441119 says it's fixed in 10.2.0.4. 
We use 10.2.0.4 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 <Vlado.Barun@xxxxxxx> wrote:

> From: Barun, Vlado <Vlado.Barun@xxxxxxx>
> Subject: RE: determining bind values in deadlock situations
> To: "yong321@xxxxxxxxx" <yong321@xxxxxxxxx>
> Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
> 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: vlado.barun@xxxxxxx
> 
> 
> -----Original Message-----
> From: Yong Huang [mailto:yong321@xxxxxxxxx] 
> Sent: Tuesday, March 03, 2009 1:19 PM
> To: Barun, Vlado
> Cc: oracle-l@xxxxxxxxxxxxx
> 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


      
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2009