Hi Alireza,

The nightly SPM evolve task runs as part of the SQL Tuning task during the maintenance windows. By default, the task is given just 1 hour to complete with the SQL Tuning Advisor going first.

When the SPM evolve task begins, it compiles an ordered list of the unaccepted plans, to be examined during each maintenance window, using the following criteria:

LAST_VERIFIED
LAST_EXECUTED
COST
TIME OF CREATION

Plans that are unaccepted and have a value of NULL for their LAST_VERIFIED column go to the top of the list. Out of that group, plans are ordered based on the value in the LAST_EXECUTED column, then on the COST of the plans and finally on TIME OF CREATION, with the oldest ones going first.

In order to prevent a particular plan from ever being added to the evolve task list you need to do two things:

• Have a non-NULL value in the LAST_VERIFIED column
• Mark the plan as ACCEPTED

Depending on whether or not you consider the plan in question to be a good plan or bad plan will determine what approach you should take to prevent the plan from being added to the evolve task list.

If you don’t know if it’s a good plan or not, the safest approach would be to manually run the evolve task for the plan using the DBMS_SPM.EXECUTE_EVOLVE_TASK function. By manually running the task, you will remove the maintenance window time constraints and you can verify if the unaccepted plan is better than the existing plan or not.

Once the task completes the LAST_VERIFIED column will be updated and therefore no longer NULL. If the plan is accepted after the evolve task it will never become a candidate for the task again.

If however, the plan is not accepted after the task completes, then the plan will not be considered for the nightly evolve task for at least 30 days and then only if it’s only then if the SQL statement is active (LAST_EXECUTED attribute has been updated).


If you know that the problematic plan really is a good plan that you want to have accepted, you can manually accept the plan without having to run the verification part of the evolve process by using the old DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function, with the VERIFY parameter set to NO. This will instantly accepted the new plan and update the LAST_VERIFIED column, which will remove the statement from the evolve task list forever.

variable evol_out clob
execute :evol_out := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_edcedb30afeb8caa',
                                                            plan_name=>'SQL_PLAN_fvmqv62ryr35ac47b6be0'
                                                             verify=>’NO’);

select :evol_out from dual;

If you know for sure the problematic plan is a bad plan that you will never want used than you can take a two-step approach:

1. Manually accept the plan without verification using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function as described above
2. Then immediately mark the plan disabled by using the ALTER_SQL_PLAN_BASELINE Function as shown below

variable cnt number

exec :cnt := dbms_spm.alter_sql_plan_baseline( -
                 sql_handle        => 'SQL_293ebc02a81d5606', -
                 plan_name         => 'SQL_PLAN_2kgpw0an1uph654bc8843', -
                 attribute_name    => 'ENABLED', -
                 attribute_value   => 'NO');