Home » Oracle » 1z0-117 » Which two tasks would you perform to achieve this?
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.
Correct Answer: AD
Explanation/Reference:
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.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
