With bind peeking the Optimizer peeks at the values of user-defined bind variables on the first execution of a query (during hard parse). The Optimizer determines the execution plan based on the initial value of bind variables.
On subsequent invocations of the query, no peeking takes place, so the original execution plan is used by all future executions, even if the value of the bind variables change.
The presence of a histogram on the column used in the expression with the bind variable may cause a different execution plan to be generated for the statement depending the initial value of the bind variable being peeked.
This could cause unpredictable query performance because the execution plan varies depending on the values of the bind variables on its first invocation.
It is possible you may run into this issue in Oracle Database 10g due to the change in the default behavior in DBMS_STATS even though you never experienced it in Oracle9i. If this is the case then you can re-gather statistics on the table without histograms or change the default value of method_opt parameter.
As a last resort you can disable bind peeking by setting the parameter _optim_peek_user_binds to false.
On subsequent invocations of the query, no peeking takes place, so the original execution plan is used by all future executions, even if the value of the bind variables change.
The presence of a histogram on the column used in the expression with the bind variable may cause a different execution plan to be generated for the statement depending the initial value of the bind variable being peeked.
This could cause unpredictable query performance because the execution plan varies depending on the values of the bind variables on its first invocation.
It is possible you may run into this issue in Oracle Database 10g due to the change in the default behavior in DBMS_STATS even though you never experienced it in Oracle9i. If this is the case then you can re-gather statistics on the table without histograms or change the default value of method_opt parameter.
As a last resort you can disable bind peeking by setting the parameter _optim_peek_user_binds to false.
