The following parameter values are set for the instance:
OPTIMIZER_CAPTURE_SQL_BASELINE = FALSE
OPTIMIZER_USESQL_PLAN_BASELINE = TRUE
The SQL plan baseline for a SQL statement contains an accepted plan.
You want to add a new plan automatically as an accepted plan to the existing SQL plan baseline.
Examine the following tasks.
1. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE parameter to TRUE.
2. Evolve the new plan.
3. Fix the existing accepted plan.
4. Manually load the new plan.
Identify the task(s) that must be performed to accomplish this.
A.
1, 2, and 3
B.
4 and 3
C.
1, 4, and 3
D.
Only 4
E.
1, 2, 4, and 3
F.
1 and 2
Explanation:
Manual Plan Loading
Manual plan loading can be used in conjunction with, or as an alternative to automatic plan
capture. The load operations are performed using the DBMS_SPM package, which allows SQL
plan baselines to be loaded from SQL tuning sets or from specific SQL statements in the cursor
cache. Manually loaded statements are flagged as accepted by default. If a SQL plan baseline is
present for a SQL statement, the plan is added to the baseline, otherwise a new baseline is
created.
Note:
* The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default
value is FALSE, determines if the system should automatically capture SQL plan baselines. When
set to TRUE, the system records a plan history for SQL statements. The first plan for a specific
statement is automatically flagged as accepted. Alternative plans generated after this point are not
used until it is verified they do not cause performance degradations. Plans with acceptableperformance are added to the SQL plan baseline during the evolution phase.
* Managing SQL plan baselines involves three phases:
Capturing SQL Plan Baselines
Selecting SQL Plan Baselines
Evolving SQL Plan Baselines
* Evolving SQL Plan Baselines
Evolving a SQL plan baseline is the process by which the optimizer determines if non-accepted
plans in the baseline should be accepted. As mentioned previously, manually loaded plans are
automatically marked as accepted, so manual loading forces the evolving process. When plans
are loaded automatically, the baselines are evolved using the EVOLVE_SQL_PLAN_BASELINE
function, which returns a CLOB reporting its results.
Reference: SQL Plan Management in Oracle Database 11g Release 1
Answer F.
Step 1.
There are two ways to load SQL plan baselines.
On the fly capture: Uses automatic plan capture by setting the
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. This
parameter is set to FALSE by default. Setting it to TRUE turns on automatic recognition of repeatable SQL statements and automatic creation of plan history for such statements.
Step 2.
When the optimizer finds a new plan for a SQL statement, the plan is added to the plan history as a nonaccepted plan. The plan will not be accepted into the SQL plan baseline until it is verified for performance relative to the SQL plan baseline performance. Verification means a nonaccepted plan does not cause a performance regression (either manually or automatically). The verification of a nonaccepted plan consists of comparing its performance to the performance of one plan selected from the SQL plan baseline and ensuring that it delivers better performance.
There are two ways to evolve SQL plan baselines:
• By using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function
• By running SQL Tuning Advisor
D sounds more correct because we will do this for one specific statement without impact for other statements.
We can do this also like it is in answer F, but in this case sql baseline plans will be created also for other sql statements which are in the cache.
Also we don’t have guarantee that sql statement, which we are interested in, is in the cache.
“You want to add a new plan automatically as an accepted plan to the existing SQL plan baseline.” – that points to “D” as manually loaded plans are accepted by default