QUESTION 101-optimizer_dynamic_sampling

Examine the parameters for your database instance:
NAME TYPE VALUES
----------------------------------------- ---------------------- -------------------
optimizer_adaptive_reporting_only boolean FALSE
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling interger 2
optimizer_features_enable string 12.1.0.1
Which three statements are true about the process of automatic optimization by using cardinality feedback?
A. The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.
A.如果优化器的估计和执行统计数据存在巨大差异，优化器会在SQL语句的后续执行过程中自动更改计划。
B. The optimizer can re optimize a query only once using cardinality feedback.
B.优化器只能使用基数反馈重新优化查询一次。
C. The optimizer enables monitoring for cardinality feedback after the first execution of a query.
C.优化器允许在第一次执行查询之后监视基数反馈。
D. The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.
D.如果启用了动态采样和多列统计，优化器不会监视基数反馈。
E. After the optimizer identifies a query as a re-optimization candidate, statistics collected by the collectors are submitted to the optimizer.
E。优化器将查询标识为重新优化候选查询后，收集器收集的统计信息将提交给优化器。
Correct Answer: ACD
Section: (none)

C: During the first execution of a SQL statement, an execution plan is generated as usual.
D: if multi-column statistics are not present for the relevant combination of columns, the optimizer can fall back on cardinality feedback.
(not B)* Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve plans for repeated executions.
optimizer_dynamic_sampling
optimizer_features_enable
* dynamic sampling or multi-column statistics allow the optimizer to more accurately estimate selectivity of conjunctive predicates. Note:
* OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer. Range of values. 0 to 10
* Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly. The optimizer may misestimate cardinalities for a variety of reasons, such as missing or inaccurate statistics, or complex predicates. Whatever the reason for the misestimate, cardinality feedback may be able to help.

 

C：在SQL语句的第一次执行期间，执行计划照常生成。
D：如果相关的列组合不存在多列统计信息，那么优化器可以依靠基数反馈。
（不是B）*基数反馈。该特性在11.2中默认启用，旨在改进重复执行的计划。
优化器\动态\采样
优化器功能启用
*动态采样或多列统计允许优化器更准确地估计合取谓词的选择性。注：
*优化器动态采样控制优化器执行的动态采样级别。值的范围。0到10
*在Oracle数据库11gR2中引入了基数反馈。此功能的目的是自动改进重复执行的查询的计划，对于这些查询，优化器无法正确估计计划中的基数。优化器可能由于各种原因错误估计基数，例如缺少或不准确的统计数据，或复杂的谓词。不管错误估计的原因是什么，基数反馈可能会有所帮助。