Automatic SQL Plan Management – Slight Change with 19.4.0

I did blog in more detail about Automatic SQL Plan Management in Oracle 19c. And Roy and I had really a lot of discussion in every customer meeting at OOW19 about it. But there is a slight change to where the feature is available with Oracle 19.4.0.

Photo by Samuel Zeller on Unsplash

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;

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.

License Guide

First of all, when you look into the Oracle 19c License Guide (table 1-6), you’ll see that Automatic SPM is an Exadata feature, available for Exadata on premises and in ExaCS.

And just in case you are not aware, a good number of SQL Plan Management’s features are available in Oracle Standard Edition 2 – please check the same table in the License Guide for more details.

More Information

• Nigel Bayliss’ Optimizer Blog
• Automatic SQL Plan Management in Oracle 19c on this blog
• SQL Plan Management in Oracle 19c – White Paper

–Mike