Re: v$sql_bind_capture – is it buggy?

From: <zigzagdna_at_yahoo.com>
Date: Tue, 3 Jun 2008 18:59:25 -0700 (PDT)
Message-ID: <182804c1-9263-4cce-9c14-e01e0e715053@c65g2000hsa.googlegroups.com>


On Jun 3, 9:55am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Jun 2, 9:22pm, 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.
>
> No, it isn't. I would take the time to read up on the view rather
> than speculating on whether or not your lack of knowledge makes this
> object 'buggy' and waiting for someone to respond to your question:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynvie...
>
> From the documentation:
>
> "Bind data
>
> One of the bind values used for the bind variable during a past
> execution of its associated SQL statement. Bind values are not always
> captured for this view. Bind values are displayed by this view only
> when the type of the bind variable is simple (this excludes LONG, LOB,
> and ADT datatypes) and when the bind variable is used in the WHERE or
> HAVING clauses of the SQL statement."
>
> Bind capture is also disabled when STATISTICS_LEVEL = BASIC.
>
> Funny how you have the time to wait for someone ELSE to read the
> documentation and report back to you.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

I had read this documenation and none of it applies to my case. I supect reason is there is a limitaion of how much data can be captured with bind vars. _cursor_bind_capture_interval (default value of this hidden parameter is 400, so when one has too many bind vars, they are not captured). I do not see this documented in manauals. Received on Tue Jun 03 2008 - 20:59:25 CDT

Original text of this message