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() ).
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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC 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 UTC Jul 1,2009 |