Re: v$sql_bind_capture – is it buggy?

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 Received on Tue Jun 03 2008 - 17:21:14 CDT