Oracle Read - Oracle Blogs Aggregator

Here are two simple test cases which demonstrate how Bind Variable Peeking can cause the wrong execution plan to be used.In this table TEST_BINDVAR_PEEKING, I have only 3 values for COUNTRY_OF_BIRTH but ‘IN’ accounts for 10 of every 12 rows. Therefore, with a Histogram on COUNTRY_OF_BIRTH, a query for ‘IN’ should use a FullTableScan while a query for ‘FR’ should use an IndexRangeScan.
In the first set of tests, I run a query with a bind variable. At the first, execution, the bind variable is set to ‘FR’ so Oracle, “peeking” into the value, selects an IndexRangeScan. [...]