Re: v$sql_bind_capture is it buggy?

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 03 Jun 2008 09:55:56 -0700
Message-ID: <1212512155.223650@bubbleator.drizzle.com>


fitzjarrell_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/dynviews_2114.htm#REFRN30310
>
> 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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jun 03 2008 - 11:55:56 CDT

Original text of this message