1Z0-117 Question #87: Real Exam Question with Answer & Explanation

Question

Options

• AA new plan cannot be evolved because SYS_SQL_bbedc41f554c408 is accepted.
• BPlan SYS_SQL_PLAN_bbdc741f554c408 will always be used by the optimizer for the query.
• CA new plan must be evolved using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function before
• DPlan SYS_SQL_bbedc741a57b5fc2 can be used by the optimizer if the cost of the query is less than
• EPlan SYS_SQL_PLAN_bbedc741f554c408 will not be used until it is fixed by using the DBMS_SPM.

Explanation

• 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. SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9') * 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 * fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.

No community discussion yet for this question.