An application supplied by a new vendor is being deployed and the SQL statements have plan
baselines provided by the supplier. The plans have been loaded from a SQL tuning set. You
require the optimizer to use these baselines, but allow better plans to used, should any be created.
Which two tasks would you perform to achieve this?
A.
Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.
B.
Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.
C.
Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.
D.
Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.
E.
Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.
Explanation:
A: OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL
plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL
plan baseline for the SQL statement being compiled. If one is found in SQL Management B
ase, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
D: EVOLVE_SQL_PLAN_BASELINE Function
This function evolves SQL plan baselines associated with one or more SQL statements. A SQL
plan baseline is evolved when one or more of its non-accepted plans is changed to an accepted
plan or plans. If interrogated by the user (parameter verify = ‘YES’), the execution performance of
each non-accepted plan is compared against the performance of a plan chosen from the
associated SQL plan baseline. If the non-accepted plan performance is found to be better than
SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided
such action is permitted by the user (parameter commit = ‘YES’).
Incorrect:
B: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES enables or disables the automatic
recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for
such statements.
C: ALTER_SQL_PLAN_BASELINE Function
This function changes an attribute of a single plan or all plans associated with a SQL statement
using the attribute name/value format.
should be A,B
New application deployment: The deployment of a new application module means the
introduction of new SQL statements into the system. The software vendor can ship the
application software along with the appropriate SQL plan baselines for the new SQL
being introduced. Because of the plan baselines, the new SQL statements will initially run with the plans that are known to give good performance under a standard test configuration. However, if the customer system configuration is very different from the test configuration, the plan baselines can be evolved over time to produce better performance.
You can use the automatic SQL plan capture after manual loading to make sure that only better plans will be used for your applications in the future.
Note: assume that OPTIMIZER_USE_SQL_PLAN_BASELINES is set to TRUE.
Yes, I also agree, correct answer should be A & B
A+D
Oracle White Paper June 2013: SQL Plan Management with Oracle Database 12c.
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES instructs optimizer automatically creates SQL plan baseline for ANY repeatable SQL statement provided it doesn’t already have one.
If SQL plan baseline already exists for the statement the new better plan will be added independently of OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES as unacceptable.
From Oracle Database 12c onwards, the original SPM evolve function (DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE) has been deprecated in favor of a new API that calls the
SPM evolve advisor.