What is Bind Variable Peeking?

Bind Variable peeking is when optimizer looks into the literal behind the behind variable. When a query with bind variable is submitted to Oracle, and the parsed representation of that query is already present in the library cache, then optimzer simply uses the already present plan, regardless of the value behind the variable.

Now with literals optimizer could make much more informed decision about the best way to access the data (i.e the best execution plan). For example, if historgram is present on the column of the table, then the optimizer already knows as how frequently or rarily a value is present in that column, and with literal in query, optimizer could use the best access path, but in case of bind variable it cannot do that because it doesn't know the value. So in case of bind variable it uses bind variable peeking to know the value. But when it uses the bind variable peeking and gets an access path, it always uses the same plan for the subsequent similar queries with bind variables up to 10g, though it has grown more intelligent in the 11g.

But as a general tip, you might want to use bind variables in the queries of OLTP and literals in the queries of data warehouse. Because in OLTP, more often than not, the parsing time is as crucial as execution time, while in DW the parsing time is very little as compared to the execution time given the high volume of data.