Oracle Scratchpad

July 24, 2008

Bind Capture

Filed under: Troubleshooting — Jonathan Lewis @ 6:30 am BST Jul 24,2008

If you’ve ever looked at view v$sql_bind_capture, you’ve probably seen that (at least in 10.2) it allows you to see the actual values for bind variables that have been used with an SQL statement.

For a long time I had assumed that this view was capturing the bind values used when the optimizer peeked at the variables when it wanted to produce an execution plan.

But I’ve just seen a note on the OTN forums from Dion Cho that highlighted my error. The note describes a couple of parameters that affect the way that bind capture behaves – including a parameter to change the “interval (in seconds) between two bind capture for a cursor”.

Sure enough, by using an ‘alter system’  call to change the “_cursor_bind_capture_interval” to a (performance-threatening) 10 seconds on a small test system, I found that the values in v$sql_bind_capture would change fairly regularly as I re-executed a given query with constantly changing input bind values. (With no change to the “peeked_binds” output from dbms_xplan.display_cursor() ).

22 Comments »

  1. I think that what you mean, the values displayed with dbms.display_cursor(), the values
    of peeked binds used by optimizer to generate execution plan are in the column bind_data
    of V$SQL, but it is RAW data (how to decode it?). do you agree?

    Regards,
    Cristian

    Comment by cristiancudizio — July 24, 2008 @ 10:48 am BST Jul 24,2008 | Reply

  2. Cristian,

    Correct. I had assumed that v$sql_bind_capture was just a viewable version of the column v$sql.bind_data in just the same way that V$sql_optimizer_env is a viewable form of the column v$sql.optimizer_env.

    Maybe I’d better go back and rethink optimizer_env as well – just in case.

    Comment by Jonathan Lewis — July 24, 2008 @ 11:22 am BST Jul 24,2008 | Reply

  3. I’ve made a test on a 10.2.0.3 and i’ve observed that v$sql.bind_data changes over time, so it is as you said, v$sql_bind_capture is a viewable version of v$sql.bind_data.

    Regards,
    Cristian

    Comment by cristiancudizio — July 24, 2008 @ 1:01 pm BST Jul 24,2008 | Reply

  4. Cristian,

    That makes life more confusing. In comment #2, I was trying to explain that my earlier assumption that they were the same had just been proved wrong.

    But now it seems you’re saying that they are the same – which means the bind_data column is NOT the set of bind values used for optimisation.

    Is it possible that the bind_data was changing only when the statement was re-optimized; or does bind_data always change as v$sql_bind_data changes even when you don’t re-optimize ?

    Comment by Jonathan Lewis — July 24, 2008 @ 1:15 pm BST Jul 24,2008 | Reply

  5. as some one said “life is that” :)

    I’m observing a particular query on our development
    database. From v$sql, if i’m not wrong, it seems not reoptimised:

    LOADED_VERSIONS LAST_LOAD_TIME FIRST_LOAD_TIME INVALIDATIONS
    ————— ——————- ——————- ————-
    1 2008-07-24/10:19:32 2008-07-23/15:41:06 0

    With select * from table(dbms_xplan.display_cursor(‘8j9q64g6ctqbq’,null,’+peeked_binds’));
    i see always the same bind peeked value:
    Peeked Binds (identified by position):
    ————————————–

    1 – :1 (NUMBER): 12599372

    From V$sql_plan.other_xml (as suggested from you a while ago) i see the same:
    c40d3c5e49

    Then
    SYSTEM@perseo10 > select substr(value_string,1,40)
    2 from v$sql_bind_capture
    3 where sql_id=’8j9q64g6ctqbq’;

    SUBSTR(VALUE_STRING,1,40)
    ——————————————————————————
    12599373

    SYSTEM@perseo10 > SELECT dbms_sqltune.extract_bind(bind_data,1).value_string
    2 FROM V$SQL WHERE SQL_ID=’8j9q64g6ctqbq’;

    DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING
    —————————————————-
    12599373

    And i’ve seen that using DBMS_STATS.CONVERT_RAW_VALUE last part of v$sql.bind_data
    the number 12599373:
    BEDA0A2003004888807C000101F0021605C40D3C5E4A) ,0000000931020071778589000000rs0121041712599373

    Do i miss something?

    Comment by cristiancudizio — July 24, 2008 @ 1:51 pm BST Jul 24,2008 | Reply

  6. i forgot to say one thing: it make sense that peeked bind variable values used for generation of the execution plan stay on v$sql_plan because there may be more execution plans for the same sql statement, do you agree?

    Thanks
    Cristian

    Comment by cristiancudizio — July 24, 2008 @ 2:08 pm BST Jul 24,2008 | Reply

  7. I think the bind_data in v$sql is used as inputs for profile creation, perhaps when dbms_sqltune is executing a tuning task. The reason I think this may be the case is because there is an extract_binds function in the dbms_sqltune package which will convert the raw data to a readable format. Of course I stumbled on that function after spending the better part of a week deciphering the raw data. The peeked binds are stored in other_xml in v$sql_plan.

    Comment by John Darrah — July 24, 2008 @ 2:47 pm BST Jul 24,2008 | Reply

  8. My investigation on 10046 trace:
    – Peeked binds value of dbms_xplan.display_cursor is extracted from v$sql_plan.other_xml
    – Captured bind value is extraced from X$KQLFBC view.

    I understand that “captured bind” and “peeked bind” have no relationship.
    Capture is capture, peeking is peeking. No relationship.
    Oracle still captures bind value at specified interval whether bind peeking is enabled or not.

    Comment by Dion Cho — July 25, 2008 @ 1:04 am BST Jul 25,2008 | Reply

  9. […] Lewis and his readers throw some light a finer point of bind capture, on his Oracle Scratchpad. “For a long time I had assumed that [v$sql_bind_capture] was […]

    Pingback by Log Buffer #108: A Carnival of the Vanities for DBAs — August 1, 2008 @ 5:04 pm BST Aug 1,2008 | Reply

  10. […] riguardo scrisse un post anche Jonathan Lewis il quale rivelava di aver confuso il contenuto di quella vista con i valori […]

    Pingback by Bind Variables e V$SQL_BIND_CAPTURE « Oracle and other — July 1, 2009 @ 9:11 am BST Jul 1,2009 | Reply

  11. seems like v$sql.bind_data is more reliable than v$sql_plan.other_xml

    variable x varchar2(10);
exec :X := 'X';
col f_sqlid new_value v_sqlid
col f_sqlcn new_value v_sqlcn
col f_binddata new_value v_binddata

select * from dual where dummy=:x;

select
       PREV_SQL_ID f_sqlid, 
       PREV_CHILD_NUMBER  f_sqlcn 
from v$session 
where sid = (  Select  sid from v$mystat where rownum=1 ) ;

select bind_data f_binddata from v$sql 
where sql_id = '&v_sqlid' 
and child_number = &v_sqlcn
/


SELECT * FROM TABLE ( DBMS_SQLTUNE.EXTRACT_BINDS ( '&v_binddata' )) ;

NAME                             POSITION DUP_POSITION   DATATYPE
------------------------------ ---------- ------------ ----------
DATATYPE_STRING CHARACTER_SID  PRECISION      SCALE MAX_LENGTH LAST_CAPT
--------------- ------------- ---------- ---------- ---------- ---------
VALUE_STRING
--------------------------------------------------------------------------------
VALUE_ANYDATA()
--------------------------------------------------------------------------------
                                        1                       1
VARCHAR2(32)              178                               32 01-OCT-10
NULL
ANYDATA()

SELECT xmltype(other_xml) AS xmlval
FROM v$sql_plan
WHERE
sql_id = '&v_sqlid' AND
child_number = '&v_sqlcn' AND
other_xml IS NOT NULL
/

XMLVAL
--------------------------------------------------------------------------------
10.2.0.1272002086

    I see the bind data in v$sql but not in v$sql_plan.other_xml

    Comment by Kyle Hailey — October 1, 2010 @ 9:51 pm BST Oct 1,2010 | Reply

    • Kyle,

      The XML tags in your final output have made WordPress destroy the results – but when I ran your script on 10.2.0.1 my XML output included (with some editing):

      [sourecode]
      <peeked_binds><bind nam=”:X” pos=”1″ dty=”1″ csi=”178″ frm=”1″ mxl=”32″>58</bind></peeked_binds>
      [/sourcecode]

      As you can see, the ‘X’ is there as 58 (hex).

      Your comment arrived at a good moment, though, because it reminded me of the options available for capturing bind variables – which I need to do the next time a visit a particular client.

      Here’s an interesting follow-up, though, on what information is stored where:

      exec :X := 'Y'

select :X from dual where dummy = :X;

      Some code will use the same bind variable several times – and even use the same variable in the select list and the where clause. In 10.2.0.1 here are three different result from trying to get the value of :X

      v$sql.bind_data
BIND_DATA
------------------------------------

1 row selected.

dbms_xplan.display_cursor()
Peeked Binds (identified by position):
--------------------------------------
   2 - :X (VARCHAR2(30), CSID=178, Primary=1)

v$sql_bind_capture

  POSITION DUP_POSITION NAME  VALUE_STRING
---------- ------------ ----- ------------
         1              :X
         2            1 :X

      In this case v$sql.bind_data breaks completely – and the other two don’t help us to get values.

      Comment by Jonathan Lewis — October 2, 2010 @ 10:39 am BST Oct 2,2010 | Reply

  12. Thanks for the followup – yesterday, when I ran the script on the work database
    v$sql.bind_data had the values and v$sql_plan.other_xml didn’t. Maybe I was making some mistake because now when I run it on my laptop from home both methods work. Will have to do some more testing.
    We are adding to add bind variable capture to DB Optimizer and the first priority is to show peeked values but also interested in providing fall back methods to gather bind variable values and a “nice to have” feature would be to show recently used values that aren’t necessarily the peeked values.
    1) v$sql_plan.other_xml – peeked values (plus added benefit of having the hints so one could re-instantiate the plan should the statement’s plan change in the future)
    2) v$sql.bind_data – seemed like from the test above that this was more dependable, but still wondering if this is always the peeked bind values, or recently used values
    3) v$sql_bind_data – recently used values, only updated every 900 seconds, and just looking at it right after running a query, it doesn’t seem to be filled in immediately (like maybe it’s filled in upon first execution and the not updated for another 900 seconds) but again maybe I’m making a mistake

    select b.sql_text, a.bind_vars, c.datatype, c.value
from v$sql_cursor a, v$sql b, v$sql_bind_data c
where b.address = a.parent_handle
  and a.curno = c.cursor_num
  and b.sql_id= '&v_sqlid'
  and b.child_number = &v_sqlcn
/

    Comment by Kyle Hailey — October 2, 2010 @ 8:55 pm BST Oct 2,2010 | Reply

  13. We have a query that runs behind a UI search screen based on several different criteria that the end user might enter with up to 28 bind variables. Most often only 8 of them are used (7 of them are auto populated, with end user entering only last_name). When I used dbms_xplan.display_cursor with +peeked_binds format, it doesn’t show all 8 bind values. e.g. it doesn’t show last_name criterion – which is the most important one. And I know that the user entered last_name. v$sql_bind_capture and dba_hist_sqlbind shows the last_name value. But dbms_xplan.display_cursor doesn’t. Have you come across this? Why wouldn’t it show all the binds?

    Comment by DBA — October 3, 2011 @ 3:11 pm BST Oct 3,2011 | Reply

    • DBA,

      “Why wouldn’t it show all the binds?”
      I don’t know – I have seen the same phenomenon, but I haven’t had time to investigate it. It may be related to bind variable declaration sizes for character or NLS variables, though. I think I may also have seen the problem with binds of type timestamp.

      Comment by Jonathan Lewis — October 4, 2011 @ 3:23 pm BST Oct 4,2011 | Reply

    • dba_hist_sql_bind_metadata shows the bind variable of last_name datatype_string as VARCHAR2(32).
      2 questions:
      1) Do you know if there is a limit on how many bind values the Peeked Binds section of the plan shows?
      2) Is it possible for the optimizer to not peek at all 8 bind values but just peek at 7? This is a stupid question, but who knows.

      Comment by DBA — October 4, 2011 @ 7:00 pm BST Oct 4,2011 | Reply

  14. OTHER_XML…

    Some features in this post require a Diagnostics Pack license.Just a small tip you might find makes things a little easier for you one day when trying to work out the underlying cause of a SQL execution plan change that leads to degraded performance af…

    Trackback by Doug's Oracle Blog — July 14, 2012 @ 7:00 am BST Jul 14,2012 | Reply

  15. […] 在不停的变化，而此时通过dbms_xplan peeking binds 则不会产生变化,这也验证了jonathanlewis的这篇文章。 这篇文章的主旨为 “Capture binds always […]

    Pingback by Capture binds always actually ? | louis liu 的镜像blog — August 28, 2012 @ 7:53 am BST Aug 28,2012 | Reply

  16. […] 在不停的变化，而此时通过dbms_xplan peeking binds 则不会产生变化,这也验证了jonathanlewis的这篇文章。 这篇文章的主旨为 “Capture binds always […]

    Pingback by VMCD.ORG » Blog Archive » Capture binds always actually ? — August 28, 2012 @ 10:06 am BST Aug 28,2012 | Reply

  17. […] extracting bind variables is something I’ve meant to look into but not spent much time. See https://jonathanlewis.wordpress.com/2008/07/24/bind-capture/ […]

    Pingback by http://dboptimizer.com/tools-2/ | xfang — December 28, 2012 @ 2:30 pm GMT Dec 28,2012 | Reply

  18. […] to get bind variables other than tracing, but you can look at some example values using this method https://jonathanlewis.wordpress.com/2008/07/24/bind-capture/ […]

    Pingback by Oracle intermittent performance problem — August 7, 2023 @ 1:19 am BST Aug 7,2023 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.