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:55 am, 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