How to Evolve a SQL Plan Baseline and Adjust the Acceptance Threshold

Non-accepted plans can be verified by executing the evolve_sql_plan_baseline function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. The execution is performed using peeked bind values from the non-accepted plan.

If the non-accepted plan's performance is better that the original(default threshold of 1.5x), the plan will be accepted and added to the SQL plan baseline.

Example

Consider the following example for SQL_HANDLE SQL_0f8c8f355212ed9a. This query has a single Non-accepted plan with plan_name: SQL_PLAN_0z34g6p915vcu22368d7d

SQL> select sql_handle,plan_name,enabled,accepted from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
SQL_0f8c8f355212ed9a           SQL_PLAN_0z34g6p915vcu22368d7d YES NO
SQL_0f8c8f355212ed9a           SQL_PLAN_0z34g6p915vcu6ddd367b YES YES

To evolve this non-accepted plan, use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE:

SQL> var report clob; 

SQL> exec :report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle=> 'SQL_0f8c8f355212ed9a', verify=>'YES', commit=>'YES' );

PL/SQL procedure successfully completed

We can see a report of the activity as follows:

SQL> print :report
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_0f8c8f355212ed9a
  PLAN_NAME  = SQL_PLAN_0z34g6p915vcu22368d7d
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_0z34g6p915vcu22368d7d
-----------------------------------
  Plan was verified: Time used .06 seconds.
  Plan failed performance criterion: 1.29 times better than baseline plan.
 
                                   Baseline Plan        Test Plan           Stats Ratio
                                   -------------      ---------            -------------
  Execution Status:                  COMPLETE          COMPLETE
  Rows Processed:                      10                  10
  Elapsed Time(ms):                    3.434             .101                   34
  CPU Time(ms):                        3.555             .1111                  32.03
  Buffer Gets:                         19                  15                   1.27
  Disk Reads:                          0                    0
  Direct Writes:                       0                    0
  Fetches:                             0                    0
  Executions:                          1                    1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1
Number of plans accepted: 0

The new plan is 1.29 times better than baseline plan (1.29 is computed as a function of buffer gets and CPU time). This means that the plan was not accepted since it did not meet the 1.5x (150%)  improvement threshold (i.e. a plan has to be at least 1.5 times better for the SPM to accept it. This threshold of 1.5 is controlled by a hidden parameter "_plan_verify_improvement_margin". 150 is the default value.

If we set this parameter to 120:

SQL> ALTER SYSTEM SET "_plan_verify_improvement_margin"=120;

and evolve the plan again:

SQL> var report clob; 

SQL> exec :report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle=> 'SQL_0f8c8f355212ed9a', verify=>'YES', commit=>'YES' );

PL/SQL procedure successfully completed

The report shows:

SQL> print :report
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_0f8c8f355212ed9a
  PLAN_NAME  = SQL_PLAN_0z34g6p915vcu22368d7d
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_0z34g6p915vcu22368d7d
-----------------------------------
  Plan was verified: Time used .06 seconds.
  Plan passed performance criterion: 1.29 times better than baseline plan.
  Plan was changed to an accepted plan.
 
                       Baseline Plan      Test Plan     Stats Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:               10            10
  Elapsed Time(ms):             3.434         .101            34
  CPU Time(ms):                 3.555         .1111           32.03
  Buffer Gets:                  19            15              1.27
  Disk Reads:                   0             0
  Direct Writes:                0             0
  Fetches:                      0             0
  Executions:                   1             1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1
Number of plans accepted: 1

SPM evolution now automatically accepts SQL_PLAN_0z34g6p915vcu22368d7d because we have adjusted acceptance threshold to 1.2.