Re: v$sql_bind_capture – is it buggy?
Date: Tue, 3 Jun 2008 19:00:11 -0700 (PDT)
Message-ID: <d7b3c8e7-f0b7-4d5e-bd6f-5aab59bb74d6@a1g2000hsb.googlegroups.com>
On Jun 3, 6:21 pm, Yong Huang <yong..._at_yahoo.com> wrote:
> On Jun 2, 9:22 pm, zigzag..._at_yahoo.com wrote:
>
>
>
>
>
> > I am using Oracle 10.2.0.3 on Windows 2003. I am using
> > V$sql_bind_capture to find values of bind vars used in my query.
> > When I look at the description of v$sql_bind_capture:
> > SQL> desc v$sql_bind_capture;
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > ADDRESS RAW(4)
> > HASH_VALUE NUMBER
> > SQL_ID VARCHAR2(13)
> > CHILD_ADDRESS RAW(4)
> > CHILD_NUMBER NUMBER
> > NAME VARCHAR2(30)
> > POSITION NUMBER
> > DUP_POSITION NUMBER
> > DATATYPE NUMBER
> > DATATYPE_STRING VARCHAR2(15)
> > CHARACTER_SID NUMBER
> > PRECISION NUMBER
> > SCALE NUMBER
> > MAX_LENGTH NUMBER
> > WAS_CAPTURED VARCHAR2(3)
> > LAST_CAPTURED DATE
> > VALUE_STRING VARCHAR2(4000)
> > VALUE_ANYDATA SYS.ANYDATA
>
> > It has a column was_captured. My query has 206 bind vars, I see it
> > only has value YES for this field for ~40 bind vars. Rest of the
> > variables. Its value is NO, as a result VALUE_STRING is not shown
> > (i.e., null).
>
> > Why is not capturing all the bind variables. Where can I get values of
> > all bind variables? I do not want to do sql trace with bind variables,
> > I was hoping some v$ provides me all the values.
>
> Hi,
>
> In addition to the limitation listed in the Reference manual, bind
> data capture is controlled by other factors. Either statistics_level
> needs to be typical (or all) or _cursor_bind_capture_area_size has to
> be manually set to non-zero (it becomes zero if you lower
> statistics_level). 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. Bind data capture happens during a cursor 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, and bind
> variables in the select list are ignored. There may be other factors I
> haven't figured out that cause the bind data capture to not happen.
>
> Yong Huang- Hide quoted text -
>
> - Show quoted text -
Young:
Most useful answer. Thanks.
Received on Tue Jun 03 2008 - 21:00:11 CDT