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 NUMBER
with 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
Hi Mike,
I didn’t get it. What is enabled by default? Capturing or Evolvement?
“optimizer_capture_sql_plan_baselines = true” ==>> Capturing
“alternate_plan_baselines = auto” ==>> Evolvement
Correct?
Thanks,
Markus
Markus,
the automatic evolvement happened since 12.2.0.1 already as a subtask of the Automatic SQL Tuning task. But nothing was enabled by default.
Now, in 19c, the database inspects the automatic SQL tuning set (ASTS) if it is available (tuning set maintained by the database primarily for automatic indexing).
When you check Nigel’s blog post, the difference is here under 4.:
“The evolve advisor decides which plans perform best and adds them to the SQL plan baseline.”
This is new. Sorry for making this not clear enough.
Cheers,
Mike
Hi Mike.
I found in 11.2 SPM to be very hit or miss. Meaning anyything but the most basic of plans would often not adhere to a baseline – sometimes a bug sometimes a “limitation” per SR. We have gotten in the habit of just using profiles to try and pin the execution plan.
We are upgrading to 19c and I want to revisit this. Are things better with SPM and baselines – we want to create a STS on 11.2 and then baseline the entire the code before 19c upgrade. I have raised a SR but havent got much assistance.
thanks
Hi John,
if I remember correctly, in 11.2 you needed a decent number of fixes for SPM. And I’m pretty certain that there was a MOS note about it with a list of essential SPM fixes.
Cheers
Mike
Would you exclude SYS & SYSTEM from Auto?
EXEC DBMS_SPM.CONFIGURE(‘AUTO_CAPTURE_PARSING_SCHEMA_NAME’,’SYS’,FALSE);
EXEC DBMS_SPM.CONFIGURE(‘AUTO_CAPTURE_PARSING_SCHEMA_NAME’,’SYSTEM’,FALSE);
It depends what you are trying to achieve.
There is no general recommendation to exclude it – but in some cases it may make sense.
Cheers
Mike