TIP 76#: How to find peeked value of bind variables

Oracle usually recommends to be sensitive in using bind variables. Starting Oralce 9i, Oracle introduced a concept of bind variable peeking. This means that the first time a SQL statement containing bind predicates is parsed, the optimizer will look at the value of the bind variable and use that value for creating the execution plan for the query.
This plan is then stored and used for all future executions regardless of changed bind values. If the initial bind value is not representative of the other values that are provided in future executions of the query, then, although the access path is good for the intial value, future executions may perform poorly. This may result in suboptimal query performance for some iterations of the query.

As I said, bind variable peeking is TRUE by default in 9.0 and above. This feature could be disabled with setting _OPTIM_PEEK_USER_BINDS to FALSE.

In this post, I am trying to demonstrate how to find the peeked value for bind variable, This helps in the case of poor performance to see if Oracle execute query based on the right value. (Sample was run in 10.2.0.3)

Test 1
In this test, a query with bind variable was run against test table.
When bind_data column in v$sql is not null,SQL has bind variables.
v$sql_bind_capture is used to find which bind value has been peeked by Oracle.



Test 2
In this test, new value (100) was passed to the same query but as it is shown Oracle still has -1 as a peek value.


Test 3
In this test, shared pool was flushed from test table. This forced Oracle to peek again the value. This time it peeked 100.