Prior to Oracle Database 11g when a SQL statement with bind variables was issued for the first time it would be hard parsed and a cost-based plan would be generated. In order to generate the most accurate plan possible the optimizer would 'peek' at the actual literal value of the bind variable and generate a plan based on this value. All subsequent executions of this statement would use this execution plan. This approach worked fine for situations where regardless of the bind value the same plan would be generated. However, it didn't work when there was a data skew in the column which the bind variable referred to. In that case depending on the value passed at the initial hard-parse you would get a plan that would suit most values that would be past but not all or if you were very unlucky you would get a plan that would suit only a small number of values and not suit the majority. One execution plan was not always enough to guarantee good performance.
With Oracle Database 11g the optimizer has the ability to peek at the literal value each time the statement is executed and generate multiple plans for a single statement subsequent bind values require different plans. This way we can use the plan that is best suited to each value.
So how exactly does it work? Just like prior release the optimizer will peek at the first literal value for the bind at hard-parse and generate a cost based plan, based on that value. Then the database will observe the cursor for a while, to see what type of values are passed and if a different plan would be better for some of these values. If the plan does need to be recalculated, the cursor is marked as 'Bind-Sensitive' and we specify a selectivity range for our current plan. As the statement continues to be executed we keep checking the selectivity of the new bind values with that of the existing range. If the new values selectivity falls within the existing range it will use the original plan. But if it's selectivity is greatly different from the original range, a new plan will be generated and the cursor will be marked 'Bind-Aware'. 'Bind-Aware' means there are multiple plans for this statement depending on the selectivity value of the bind variable.
The benefit of using multiple execution plans outweighs the parse time and memory usage overhead.
A new view V$SQL_CS_STATISTICS summarizes the information that the optimizer collects to make this decision. For a sample of executions, it keeps track of rows processed, buffer gets, and CPU time. The use of this feature may lead to more than 1 child cursor for query with bind variables, but there is a limit to the number of child cursor optimizer generates.
With Oracle Database 11g the optimizer has the ability to peek at the literal value each time the statement is executed and generate multiple plans for a single statement subsequent bind values require different plans. This way we can use the plan that is best suited to each value.
So how exactly does it work? Just like prior release the optimizer will peek at the first literal value for the bind at hard-parse and generate a cost based plan, based on that value. Then the database will observe the cursor for a while, to see what type of values are passed and if a different plan would be better for some of these values. If the plan does need to be recalculated, the cursor is marked as 'Bind-Sensitive' and we specify a selectivity range for our current plan. As the statement continues to be executed we keep checking the selectivity of the new bind values with that of the existing range. If the new values selectivity falls within the existing range it will use the original plan. But if it's selectivity is greatly different from the original range, a new plan will be generated and the cursor will be marked 'Bind-Aware'. 'Bind-Aware' means there are multiple plans for this statement depending on the selectivity value of the bind variable.
The benefit of using multiple execution plans outweighs the parse time and memory usage overhead.
A new view V$SQL_CS_STATISTICS summarizes the information that the optimizer collects to make this decision. For a sample of executions, it keeps track of rows processed, buffer gets, and CPU time. The use of this feature may lead to more than 1 child cursor for query with bind variables, but there is a limit to the number of child cursor optimizer generates.
