QUESTION 46-/Bind Variable Peeking/Adaptive Cursor Sharing/ Literals in a SQL statement

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
Correct Answer: ACE
Section: (none)

* 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.

哪三个特性协同工作，允许SQL语句基于不同的选择性范围为同一语句使用不同的游标?

A.绑定可变的窥视

B.SQL计划基线

C.自适应光标共享

D.在SQL语句中使用的变量

E.SQL语句中的文字

正确答案:王牌

部分:(一)

*在bind variable peeking(也称为bind peeking)中，当数据库对语句进行硬解析时，优化器会查看绑定变量中的值。

当查询使用文字时，优化器可以使用文字值来找到最佳计划。但是，当查询使用绑定变量时，优化器必须在SQL文本中不存在文字的情况下选择最佳计划。这项任务可能极其困难。通过查看绑定值，优化器可以确定WHERE子句条件的选择性，就像使用了文字一样，从而改进计划。

C: Oracle 11g/12g使用自适应游标共享来解决这个问题，它允许服务器在不同的绑定变量值之间比较执行计划的有效性。如果它注意到次优计划，它就允许某些绑定变量值或值范围为同一语句使用替代执行计划。此功能不需要额外配置。