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
Correct Answer: D
Explanation/Reference:
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 acceptable performance 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
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
