Saturday 15 February 2014

Evolving SQL Plan Baselines and Adjusting 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 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

19c Multitenant RAC- Default service disables parallelism

We recently upgraded one of our RAC databases from 11.2 to 19.10 on Exadata. And to everyone's surprise, almost everything on the system...