Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Tuesday, 01 October 2019 07:00

Automatic SQL Plan Management – Slight Change with 19.4.0

Written by  https://mikedietrichde.com/2019/10/02/automatic-sql-plan-management-slight-change-with-19-4-0/
Rate this item
(0 votes)

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.
Automatic SQL Plan Management - Slight Change with 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;
19.319.4
PARAMETER_NAME            PARAMETER_VALUEPARAMETER_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.

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

–Mike

Share this:

Related

Read 18 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.