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 theplan evolutionof one or more plans for a
specified SQL statement. The input parameters can be aSQL 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 isTIME_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 usingACCEPT_SQL_PLAN_BASELINEfor 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 aCLOB. Input parameters include the task
name and section of the report to include.
Reference: Oracle Database SQL Tuning Guide 12c, Managing SQL Plan Baselines
B
B
B – 2, 1, 4, 3, 5
B
B
How is OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE affect the process here? should it be set to TRUE? Thanks.
By setting it to FALSE, it is not collecting new plan baselines automatically. That is forcing you to capture manually .
No correct answer, should be 1,2,4,3,5
B is correct
https://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL94653
you can’t set parameters for task if it’s not existed yet. Create task first.
B is correct.
By logic, create task, set parameters
B is correct, but in real world you should report_evolve_taks BEFORE implement_evolve_task… So it should be 2, 1, 4, 5, 3
New question
Examine these parameters that are set for your Oracle 12c database instance:
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
Examine these steps:
1. Set the evolve task parameters.
2. Create the evolve task by using the DBMS_SPM.CREATE_EVOVLE_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 MINIMUN number of steps in the correct order to manually evolve SQL plans.
A. 2, 4, 5
B. 2, 1, 4, 3, 5
C. 1, 2, 3, 4, 5
D. 1, 2, 4, 5
A is correct