The following parameter are set for your Oracle 12c database instance:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
You want to manage the SQL plan evolution task manually. Examine the following steps:
1. Set the evolve task parameters.
2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function.
3. Implement the recommendations in the task by using the
DBMS_SPM.IMPLEMENT_EVOLVE_TASK function.
4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function.
5. Report the task outcome by using the DBMS_SPM.REPORT_EVOLVE_TASK function.
Identify the correct sequence of steps:
A.
2, 4, 5
B.
2, 1, 4, 3, 5
C.
1, 2, 3, 4, 5
D.
1, 2, 4, 5
Explanation:
* Evolving SQL Plan Baselines
*
2. Create the evolve task by using the DBMS_SPM.CREATE_EVOLVE_TASK function. This function creates
an advisor task to prepare the plan evolution of one or more plans for a specified SQL statement. The input
parameters can be a SQL handle, plan name or a list of plan names, time limit, task name, and description.
1. Set the evolve task parameters.
SET_EVOLVE_TASK_PARAMETER
This function updates the value of an evolve task parameter. In this release, the only valid parameter is
TIME_LIMIT.
4. Execute the evolve task by using the DBMS_SPM.EXECUTE_EVOLVE_TASK function. This function
executes an evolution task. The input parameters can be the task name, execution name, and execution
description. If not specified, the advisor generates the name, which is returned by the function.
3: IMPLEMENT_EVOLVE_TASK
This function implements all recommendations for an evolve task. Essentially, this function is equivalent to
using ACCEPT_SQL_PLAN_BASELINE for all recommended plans. Input parameters include task name, plan
name, owner name, and execution name.
5. Report the task outcome by using the DBMS_SPM_EVOLVE_TASK function. This function displays the
results of an evolve task as a CLOB. Input parameters include the task name and section of the report to
include.
Oracle Database SQL Tuning Guide 12c, Managing SQL Plan Baselines
