Which three features work together, to allow a SQL statement to have different cursors for the
same statement based on different selectivity ranges?
A.
Bind Variable Peeking
B.
SQL Plan Baselines
C.
Adaptive Cursor Sharing
D.
Bind variable used in a SQL statement
E.
Literals in a SQL statement
Explanation:
* In bind variable peeking (also known as bind peeking), the optimizer looks at the
value in a bind variable when the database performs a hard parse of a statement.
When a query uses literals, the optimizer can use the literal values to find the best plan. However,
when a query uses bind variables, the optimizer must select the best plan without the presence of
literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer
can determine the selectivity of a WHERE clause condition as if literals had been used, thereby
improving the plan.
C: Oracle 11g/12g uses Adaptive Cursor Sharing to solve this problem by allowing the server to
compare the effectiveness of execution plans between executions with different bind variable
values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to
use alternate execution plans for the same statement. This functionality requires no additional
configuration.
What do you think of D instead of E???
Sure E is not even a correct answer.
a,c,d
acd
ACE
It can’t be E – it isn’t the same statement if you are using literals.
ACD is correct
i`m not sure that E is deffinetly wrong answer.
“Which three features work together, to allow a SQL statement to have different cursors”
so if your data is skewed than putting literals will generate different cursors.
ABC
Which three FEATURES work together…
https://blogs.oracle.com/optimizer/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact
The plans that are chosen by ACS impact the number of child cursors and number of hard parses that you will see for a particular sequence of bind values. Thus, the presence of a SQL plan baseline that constrain the possible plans can cause a different number of child cursors and hard parses. Without SPM in the picture, a bind value that falls in-between may generate an additional hard parse and a new child cursor (and possibly a new plan).
ACE