QUESTION 60、Adaptive plans

Which three statements are true about adaptive SQL plan management?
A. It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
B. The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.
C. It adds new, bettor plans automatically as fixed plans to the baseline.
D. The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
E. The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.
Correct Answer: ADE

With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time.
Note:
- The optimizer is able to adapt plans on the fly by predetermining multiple subplans for portions of the plan.
- Adaptive plans, introduced in Oracle Database 12c, enable the optimizer to defer the final plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan) with statistics collectors so that it can detect at runtime, if its cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.

 

哪三条关于自适应SQL计划管理的陈述是正确的?

a当未被接受的计划比现有的已接受的计划执行得更好时，它会以综合模式自动执行验证或演进未被接受的计划。

b优化器总是使用固定计划，如果固定计划存在于计划基线中。

C.它自动添加新的下注计划作为固定计划到基线。

d未被接受的计划会自动被接受，如果它们比现有的被接受的计划执行得更好，优化器就会使用它们。

e在夜间维护窗口期间，SQL计划基线中未被接受的计划会在综合模式下自动演进，并生成一个持久的验证报告。

正确答案:ADE

 

使用自适应SQL计划管理，dba不再需要手动运行未被接受的计划的验证或演进过程。当自动SQL调优处于综合模式时，它会在夜间维护窗口期间为所有计划未被接受的SQL语句运行一个验证或演进过程。如果未接受的计划比SQL计划基线中现有的接受计划(或多个计划)执行得更好，那么该计划将自动被接受，并为优化器提供可用性。验证完成后，将生成一个持久的报告，详细描述未接受计划的执行情况与已接受计划的执行情况。因为演进过程现在是一个自动任务，dba还可以在结束时安排他们自己的演进工作。

注意:

-优化器能够通过预先确定多个子计划的计划的部分，在飞行中调整计划。

自适应计划(Adaptive plans)是在Oracle Database 12c中引入的，它使优化器能够将一个语句的最终计划决定推迟到执行时。优化器使用统计收集器检测其选择的计划(默认计划)，以便在运行时检测其基数估计值是否与计划中操作所看到的实际行数有很大差异。如果存在显著差异，那么plan或它的一部分将被自动调整，以避免在第一次执行SQL语句时性能欠佳。