Re: v$sql_bind_capture – is it buggy?

From: <fitzjarrell_at_cox.net>
Date: Tue, 3 Jun 2008 11:00:45 -0700 (PDT)
Message-ID: <feba95c7-6476-44b9-b1f3-dbd548a740a3@d77g2000hsb.googlegroups.com>


On Jun 3, 11:55am, DA Morgan <damor..._at_psoug.org> wrote:
> fitzjarr..._at_cox.net 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.
>
> > 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
>
> Worse than that. The only reason the posting was made here is that the
> OP didn't like the answers in the OTN forums where, I should point out,
> I pointed to the fact that any query with 206 bind variables has a
> much larger issue than whether the values are being capture.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

I guess he wants the query to ... ummm ... 'scale' ...

Of course with 206 bind variables that would tip the scales pretty quickly.

David Fitzjarrell Received on Tue Jun 03 2008 - 13:00:45 CDT

Original text of this message