Oracle 19c Automatic SQL Plan Management Behaviors Change

 

What's New : 

Automatic SQL Plan Management is enabled by default in Oracle 19c

New SQL plan baselines will be created automatically

By default, the SPM Evolve Advisor runs daily in the maintenance window

• In 19c it can be configured to also run outside of the maintenance window

• To control it, the DBMS_SPM.CONFIGURE procedure supports a new parameter

• AUTO_SPM_EVOLVE_TASK (OFF, ON, AUTO)

• In 19c AUTO is equivalent to ON

• When enabled, it runs every hour for no longer than 30 minutes

How to check :

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;

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines;

The new default AUTO for ALTERNATE_PLAN_BASELINE and ALTERNATE_PLAN_SOURCE enables the Automatic SQL Plan Management in Oracle Database 19c. The attribute ALTERNATE_PLAN_SOURCE lets the advisor identify the sources for plans by itself. The ALTERNATE_PLAN_BASELINE parameter determines which SQL statements are eligible for plan capture. AUTO in this case means that any SQL statement in AWR will be a potential candidate.

How to  revert to the Oracle 12.2.0.1   Behavior : 

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,

      parameter => 'ALTERNATE_PLAN_BASELINE', 

      value     => 'EXISTING'); 

END; 

/

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_SOURCE', 

      value     => 'CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY'); 

END; 

/

BEGIN

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_LIMIT',

      value => 10);

END;

/

Switching to the Oracle 19c  behaviour 

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,

      parameter => 'ALTERNATE_PLAN_BASELINE', 

      value     => 'AUTO');  

END; 

/

BEGIN 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_SOURCE', 

      value     => 'AUTO');  

END; 

/

BEGIN

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(

      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',

      parameter => 'ALTERNATE_PLAN_LIMIT',

      value => 'UNLIMITED');

END;

/

Reference : 

https://blogs.oracle.com/optimizer/post/what-is-automatic-sql-plan-management-and-why-should-you-care