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 of the non-accepted plan.
If the non-accepted plan's performance is better (threshold of 1.5), the function will make it accepted, thus adding it to the SQL plan baseline.
Consider the following.
There is one Non-accepted plan for SQL_HANDLE SQL_0f8c8f355212ed9a
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
We will now evolve this non-accpeted plan.
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
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 plan was not accepted because it did not meet the threshold criterion of 1.5. 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
Now let us set this parameter to 120 and evolve the plan again.
SQL> ALTER SYSTEM SET "_plan_verify_improvement_margin"=120;
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
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.
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 YES
SQL_0f8c8f355212ed9a SQL_PLAN_0z34g6p915vcu6ddd367b YES YES
The execution is performed using peeked bind values of the non-accepted plan.
If the non-accepted plan's performance is better (threshold of 1.5), the function will make it accepted, thus adding it to the SQL plan baseline.
Consider the following.
There is one Non-accepted plan for SQL_HANDLE SQL_0f8c8f355212ed9a
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
We will now evolve this non-accpeted plan.
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
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 plan was not accepted because it did not meet the threshold criterion of 1.5. 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
Now let us set this parameter to 120 and evolve the plan again.
SQL> ALTER SYSTEM SET "_plan_verify_improvement_margin"=120;
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
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.
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 YES
SQL_0f8c8f355212ed9a SQL_PLAN_0z34g6p915vcu6ddd367b YES YES
