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() ).
Oracle Scratchpad 
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
[…] 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 |
[…] 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 |
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.1272002086I 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 |
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:
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 :XIn 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 |
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
Comment by Kyle Hailey — October 2, 2010 @ 8:55 pm BST Oct 2,2010 |
Hello Kyle,
Have you been able to figure out “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” ?
I am asking because I write a sql to retrieve “peeked” and “passed” bind variable per execution that rely on v$sql_plan.other_xml (see http://bdrouvot.wordpress.com/2013/04/29/bind-variable-peeking-retrieve-peeked-and-passed-values-per-execution-in-oracle-11-2/) but I would like to display more “peeked datatype values” thanks to : dbms_sqltune.extract_bind
Thx
Bertrand
Comment by bdrouvot — April 30, 2013 @ 7:31 am BST Apr 30,2013 |
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 |
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 |
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 |
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 |
[…] 在不停的变化，而此时通过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 |
[…] 在不停的变化，而此时通过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 |
[…] 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 |