Automatic SQL Plan Management – Slight Change with 19.4.0

 

Slight Change

When you compare the settings of SQL Plan Management between Oracle 19.3 and 19.4, you’ll see the difference:

column parameter_value format a45
column parameter_name format a25
set pages 200

SELECT PARAMETER_NAME, PARAMETER_VALUE
  FROM   DBA_ADVISOR_PARAMETERS
  WHERE  TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK'
    AND PARAMETER_VALUE <> 'UNUSED'
  ORDER BY 1;

 

19.3	19.4
PARAMETER_NAME            PARAMETER_VALUE	PARAMETER_NAME            PARAMETER_VALUE
ACCEPT_PLANS              TRUE ALTERNATE_PLAN_BASELINE   AUTO ALTERNATE_PLAN_LIMIT      UNLIMITED ALTERNATE_PLAN_SOURCE     AUTO DAYS_TO_EXPIRE            UNLIMITED DEFAULT_EXECUTION_TYPE    SPM EVOLVE EXECUTION_DAYS_TO_EXPIRE  30 JOURNALING                INFORMATION MODE                      COMPREHENSIVE TARGET_OBJECTS            1 TIME_LIMIT                3600	ACCEPT_PLANS              TRUE ALTERNATE_PLAN_BASELINE   EXISTING ALTERNATE_PLAN_LIMIT      UNLIMITED ALTERNATE_PLAN_SOURCE     AUTO DAYS_TO_EXPIRE            UNLIMITED DEFAULT_EXECUTION_TYPE    SPM EVOLVE EXECUTION_DAYS_TO_EXPIRE  30 JOURNALING                INFORMATION MODE                      COMPREHENSIVE TARGET_OBJECTS            1 TIME_LIMIT                3600

You can try to switch it to “AUTO” by using:

BEGIN 
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ALTERNATE_PLAN_BASELINE', 
      value     => 'AUTO'); 
END; 
/

But you’ll receive an error if you are neither on an Exadata nor in ExaCS:

ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 6086
ORA-06512: at "SYS.DBMS_SPM", line 2749
ORA-06512: at line 2

Hence, you can’t enable it on a non-Exadata system.