This discussion is archived
12 Replies Latest reply: Sep 28, 2013 3:08 AM by user1087205 RSS

v$sql_bind_capture – is it buggy?

user632098 Newbie
Currently Being Moderated
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.
  • 1. Re: v$sql_bind_capture – is it buggy?
    damorgan Oracle ACE Director
    Currently Being Moderated
    .... I'm sorry. I am still trying to fathom how any well designed query can have 206 bind variables.

    At face value your statement would, I think, lead a sane architect or DBA to examine the design, the business case, and the possible use of psychotomimetics.

    Can you explain how this could possibly be a good idea?

    Something is "buggy" here I'll agree. But I don't think it is Oracle's sql_bind_capture view.

    Thanks.
  • 2. Re: v$sql_bind_capture – is it buggy?
    user632098 Newbie
    Currently Being Moderated
    I did not write the query, it is from a third party package. There is
    defintely some porblem with bind capture in Oracle otherwise so many values will not be captured. I saw some bugs related to this in 10.1.0.x so it has been buggy.
  • 3. Re: v$sql_bind_capture – is it buggy?
    damorgan Oracle ACE Director
    Currently Being Moderated
    Buggy means "not performing as designed." Unless you have a copy of Oracle's internal specification for this view I think your comment ill advised.

    If this query is part of a third-party package then it would be really helpful if you told us the name of the vendor, the name of the software, etc. (including version number) so others can look into this too.

    Personally ... I'd be on the phone with the vendor.

    I don't see how this is Oracle's issue. Your software appears to need triage.
  • 4. Re: v$sql_bind_capture – is it buggy?
    user632098 Newbie
    Currently Being Moderated
    I have created a ticket with Metalink. Hopefully, they will look into the issue instead
    of pointing the blame on the software. If something is designed well, it will work with 10 vaiables as well as with 1000 variables. That is not the case with v$sql_bind_capture. It is that simple.
  • 5. Re: v$sql_bind_capture – is it buggy?
    user632098 Newbie
    Currently Being Moderated
    I have seen in this forum "
    Queries against v$sql_bind_capture do not show bind names
    for duplicate bind variables " which is a bug.

    In my case, I do not have duplicate bind variables, i.e., same bind variable is not used multiple times in some of the bind vars I checked, so it is a different bug.
  • 6. Re: v$sql_bind_capture – is it buggy?
    damorgan Oracle ACE Director
    Currently Being Moderated
    Please post what you recieve from Support Services. But I see no reason why Oracle would design sql_bind_capture to hold an infinite number of bind variable values any more than I see any reason why Oracle should allow cascading triggers (50 recursions and they, thankfully, blow it up).

    The fact that someone can write something poorly does not mean Oracle should support their bad design.

    But still ... it will be interesting to see what they tell you.
  • 7. Re: v$sql_bind_capture – is it buggy?
    601585 Oracle ACE
    Currently Being Moderated
    You need to increase the value of "_cursor_bind_capture_area_size", like

    alter system set "_cursor_bind_capture_area_size" = 999;

    But 999 is maxvalue and it does not guarantee that all your bind values are captured.
    As dmorgan said it right, you don't want to flood your shared pool with enormous bind values.

    Dion Cho

    PS) Not sure about the unit of "size" here, may be Byte or something.

    Typo...
    Message was edited by:
    Dion_Cho
  • 8. Re: v$sql_bind_capture – is it buggy?
    561093 Oracle ACE
    Currently Being Moderated
    Well, its for sure its NOT a bug.
    Why is not capturing all the bind variables.
    You did not mention in which part are the bind variable listed. Are they part of SELECT list or inside WHERE clause.

    Oracle will not capture any bind value when listed in SELECT statement. Only bind variables in the WHERE clause are captured.
    Where can I get values of all bind variables?
    By tracing or from the application (for all listed in SELECT part) !!!
    I do not want to do sql trace with bind variables, I was hoping some v$ provides me all
    the values.
    then ignore the bind variables. Why are you so much concerned about the bind variables.


    Below is a small demo proving that Oracle will only capture bind values used in the WHERE clause and all the other bind variables under SELECT list are ignored.
    SQL> conn scott/tiger
    Connected.
    SQL> variable a number;
    SQL> variable b number;
    SQL> variable x number;
    SQL> exec :a :=0; :b := 0; :x := 7369;

    PL/SQL procedure successfully completed.

    SQL> select job, nvl(empno, :a), nvl(comm, :b) from emp where empno = :x;

    JOB       NVL(EMPNO,:A) NVL(COMM,:B)
    --------- ------------- ------------
    CLERK              7369            0

    SQL> select sql_id, sql_text from v$sql where sql_text like 'select job, nvl(empno,%';

    SQL_ID
    -------------
    SQL_TEXT
    ----------------------------------------------------------------------------------------------------
    3d1079n1gakc1
    select job, nvl(empno, :a), nvl(comm, :b) from emp where empno = :x


    SQL> column name format a10
    SQL> column VALUE_STRING format a20
    SQL> select name, position, datatype_string, last_captured, value_string from v$sql_bind_capture
      2  where sql_id = '3d1079n1gakc1' order by position;

    NAME         POSITION DATATYPE_STRING LAST_CAP VALUE_STRING
    ---------- ---------- --------------- -------- --------------------
    :A                  1 NUMBER
    :B                  2 NUMBER
    :X 3 NUMBER 03/06/08 7369

    SQL>
  • 9. Re: v$sql_bind_capture – is it buggy?
    user632098 Newbie
    Currently Being Moderated
    Thanks. The value of 400 for the hidden parameter is most likely the cause. I need to increase it 10000 (unit is butes as per Oracle manual).
  • 10. Re: v$sql_bind_capture – is it buggy?
    puthranv Newbie
    Currently Being Moderated
    Well Oracle says it's not a bug and that's how they have been designed and do not show all the bind values in the view v$sql_bind_capture, A detailed testcase is here

    http://www.puthranv.com/2012/08/find-bind-variable-values-in-oracle.html

    Regards
    puthranv
  • 11. Re: v$sql_bind_capture – is it buggy?
    javeedkaleem dba Newbie
    Currently Being Moderated
    want to check the bind variables used in the query

    check this script

    prompt " Search the bind value using SQL_id"*
    COL NAME FOR A60*
    COL VALUE_STRING FOR A60*
    SET PAGES 200*
    SET LINES 150*
    select name , VALUE_STRING from v$sql_bind_capture where sql_id='&enter_SQL_ID';*


    Best Regards
    Kaleem.
  • 12. Re: v$sql_bind_capture – is it buggy?
    user1087205 Newbie
    Currently Being Moderated

    The question about why v$sql_bind_capture captures some, but not all bind variables, is a perfectly natural that comes up a lot in the forums.