Automatic SQL Plan Management in Oracle Database 19c

When you upgrade to a new database release, there will be changes. Some are obvious, others are not. This one is somewhere in between because it is documented quite well but not very well known yet as far as I see. I’m talking about the Automatic SQL Plan Management in Oracle Database 19c.

What is changing in Oracle 19c?

PLEASE READ THIS UPDATED BLOG POST:
https://mikedietrichde.com/2019/10/02/automatic-sql-plan-management-slight-change-with-19-4-0/
AS THERE IS A CHANGE WITH ORACLE 19.4.0.

First of all, I don’t want to rewrite everything Nigel Bayliss wrote in his detailed blog post about Automatic SQL Plan Management in Oracle 19c in April 2019. But the essence is:

• Automatic SQL Plan Management is enabled by default in Oracle 19c
• New SQL plan baselines will be created automatically

Don’t get me wrong. I fully agree with Nigel’s conclusion that this is a good feature. And we are in agreement too, that you need to be aware of such things happening in the background. I’m a very big fan of SQL Plan Baselines as it is a very useful tool to prevent unwanted plan changes as part of an upgrade or a migration. Actually I use it quite often with customers. Personally, I’m still scared how little people use it despite the fact that it s a free Enterprise Edition feature since Oracle 11.1, and became partially even an SE2 feature later.

A closer look at different settings

I compare the different settings between Oracle 12.1.0.2, Oracle 18c (settings are identical with 12.2.0.1) and Oracle 19c. The key view you need to access is DBA_SQL_PLAN_BASELINES.

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;

Hereafter I compare only default values which are not UNUSED.

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 switch between different behavior?

Nigel documented this already in his blog post.

If you’d like to restore the Oracle 12.1 behavior:

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

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

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

If you’d like to revert to the Oracle 12.2.0.1 and Oracle 18c 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;
/

Of course, switching to the Oracle 19c defaults works this way:

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;
/

Please note, that ALTERNATE_PLAN_LIMIT is defined in the documentation as NUMBERwith an extra explanation:
“You must include ALTERNATE_PLAN_LIMIT for VARCHAR2 to accept the UNLIMITED parameter value.”

And finally, let me say this again: I don’t recommend to revert to the old behavior. This is a recommendation to check and keep and eye on it. My intention is to explain a behavior change in Oracle 19c.

Links and more information

• Optimizer Blog – Nigel Bayliss – Apr 8, 2019:
  What is automatic SQL plan management and why should you care?
• SQL Plan Management in Oracle 19c – White Paper
• Oracle 19c – SET_EVOLVE_TASK_PARAMETER
• Upgrade Blog: New SPM Evolve Advisor Task in Oracle 12.1.0.2
• Automatic SPM – Slight Change with Oracle 19.4.0

–Mike