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.
Explanation:
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 inCOMPREHENSIVE 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.
Reference: SQL Plan Management with Oracle Database 12c
A d and E???
I think B is right…
BDE shoulde be the correct answer
ADE
ABE
I think that “COMPREHENSIVE mode” is the key in the question, because of this D is incorrect in my opinion.
I agree.
Also if SPM has a fixed plan (fixed=YES), then it will always use it. It does not care even if it finds any better plan. All better plans are kept in the SPM for evolution.
AED – OK
2.2.4.2 Adaptive SQL Plan Management
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.
Unaccepted plans in a SQL plan baseline are automatically evolved during the nightly maintenance window and a persistent verification report is generated which means a DBA no longer has to manual evolve plans and they can go back days or weeks later and review what plans were evolved during each of the nightly maintenance windows.
https://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT205
D is not true – non-accepated plans in SPM need to be evolved before they can be accepated.
All new plans are added as non-accepted to the SPM.
This is as of 11g. On 12c, database evolves non-accepted plans during the maintenance window.
ADE
ADE
Unaccepted plans in a SQL plan baseline are automatically evolved during the nightly maintenance window and a persistent verification report is generated which means a DBA no longer has to manual evolve plans and they can go back days or weeks later and review what plans were evolved during each of the nightly maintenance windows. Hence, E is correct.
SQL Plan Management (SPM) provides such a framework that allows for complete controlled
plan evolution. With SPM the optimizer automatically manages execution plans and ensures
that only known or verified plans are used. When a new plan is found for a SQL statement, it
will not be used until it has been verified to perform better than the current plan.
SQL plan management (SPM) ensures that runtime performance will never degrade due to the change
of an execution plan. To guarantee this, only accepted execution plans are used; any plan evolution that
does occur, is tracked and evaluated at a later point in time, and only accepted if the new plan shows a
noticeable improvement in runtime.
Plan capture:
Creation of SQL plan baselines that store accepted execution plans for all relevant SQL
statements. SQL plan baselines are stored in the SQL Management Base in the SYSAUX
tablespace.
2. Plan selection:
Ensures only accepted execution plans are used for statements with a SQL plan baseline and
records any new execution plans found for a statement as unaccepted plans in the SQL plan
baseline.
3. Plan evolution:
Evaluate all unaccepted execution plans for a given statement, with only plans that show a
performance improvement becoming accepted plans in the SQL plan baseline.
A SQL plan baseline is created for the repeatable statements, which
includes all of the information needed by the optimizer to reproduce the current cost-based execution
plan for the statement, such as the SQL text, outline, bind variable values, and compilation
environment. This initial plan will be automatically marked as accepted. If some time in the future a
new plan is found for this SQL statement, the execution plan will be added to the SQL plan baseline
but will be marked unaccepted.
Note also that the first plan captured for each statement is
automatically accepted, even if it isn’t the most performant plan. Automatic plan capture should
therefore only be enabled when the default plans generated for critical SQL statements are performing
as expected.
Regardless of which method you use to initially create a SQL plan baseline, any subsequent new plan
found for that SQL statement will be added to the plan baseline as an unaccepted plan. This behavior
is not dependent on the initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_ BASELINES and
will occur even if this parameter is set to FALSE (the default). These newly added plans will not be used
until the plan has been verified to perform better than the best existing accepted plan in the SQL plan
baseline.
Ref: http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf
Based on these, I would go with A, D, E.
A is not true – there is no “nightly maintenance window”,
B is true, optimizer always use fixed plans,
C is not true,
D is true,
E is true,
so I would go with B, D, E
if you say A there is no “nightly maintenance window”, then D is same there is no “nightly maintenance window”.
My choice – ABE
ADE
ABE
For B and E see:
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf
B:
One or more plans in the SQL plan baseline can be marked as fixed. Fixed plans indicate to the optimizer that they are preferred. The optimizer will select the fixed plan with the lowest cost unless none of the fixed plans are reproducible
. In that case, the optimizer will cost the remaining (non-fixed) plans in the SQL plan baselines, and select the one with the lowest cost.
E:
From Oracle Database 12c onward, automatic plan evolution is done by the SPM Evolve Advisor. The SPM Evolve Advisor is an AutoTask (SYS_AUTO_SPM_EVOLVE_TASK), which operates during the nightly maintenance window and automatically runs the evolve process for unaccepted plans in SPM. The AutoTask ranks all unaccepted plans in SPM (newly found plans ranking highest) and then runs the evolve process for as many plans as possible before the maintenance window end.
Yes but the answer B says always uses fixed plans. It does not always use fixed per your explanation
BDE
BDE is correct
A – Doesn’t make sense
B- Is correct per oracle documentation
https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL618
D & E are correct.
Ans : BDE
BDE
BDE seems more logical according to the shared documents
ADE
ADE
I think ADE.
B incorrect because “One or more plans in the SQL plan baseline can be marked as fixed. Fixed plans indicate to the optimizer that they are preferred. The optimizer will select the fixed plan with the lowest cost unless none of the fixed plans are reproducible.” (http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf)
C incorrect definitely.
True.