In older versions of Oracle, Column histograms and bind variables were mutually exclusive. To use the histogram, the optimizer needed to know the value of the variables at parse time, but bind variable 'HID" the value until execute time.
In 10g, The "BIND VARIABLE PEEKING" method was introduced, in which Oracle peeked at the first bind variable provided before deciding an execution plan. Although this often let to better plans, it also introduced a randomness into query execution that caused a lot of headaches. Some SQL's would randomly use and index depending on which bind variable happened to be the first one supplied by the application. Oracle 11g attempts to overcome this problem by employing "Adaptive cursor sharing" This involves generating a separate execution plan for a SQL statement where the different values of a bind variable might justify different plans. Such a SQL is called bind sensitive. Bind Sensitive SQL's might be represented by multiple cursors with separate plans. The appropriate plan is chosen when the SQL is executed with new bind variables. |