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() ).
