Insights into Statistics, Query Optimization and the Oracle Optimizer
What is automatic SQL plan management and why should you care?
Oracle Database 19c adds a new feature called automatic SQL plan management. This post covers:
- What is automatic SQL plan management and how it works.
- Why it is important for you to be aware of it.
- How to configure.
- Summary.
What is it?
Automatic SQL plan management identifies SQL statements that consume significant system resources and, in addition, they have been observed by the database to be using multiple SQL execution plans, some apparently more optimal than others. Auto SPM identifies the best plans from the alternatives it discovers and prevents the sub-optimal plans from being chosen (or at least until SPM evolution establishes that a sub-optimal plan has become optimal).
How does it work?
Here is a summary of the flow:
- The Automatic Workload Repository (AWR) is inspected for SQL execution plans that consume significant system resources. In addition, the database inspects the automatic SQL tuning set (ASTS) if it is available (this is a tuning set maintained by the database primarily for automatic indexing).
- The database looks for alternative SQL execution plans in various sources such as AWR, SQL tuning sets and the cursor cache. The plans identified are added to the SQL plan history.
- The SPM evolve advisor test executes the alternative plans and compares their performance.
- The evolve advisor decides which plans perform best and adds them to the SQL plan baseline.
- SQL plan baselines prevent 'regressed' execution plans from being used.
Why do you need to be aware of it?
Automatic SQL plan management is enabled by default in Oracle Database 19c. A message I have received loud-and-clear is that you want us to tell you if we have changed default behavior, and you want to know clearly how to enable and disable the new behavior. I will cover this below.
If you upgrade to Oracle Database 19c and use the default SPM settings, then you will notice that new SQL plan baselines will be created automatically (and they will be visible in the dictionary view, DBA_SQL_PLAN_BASELINES).
How is it configured?
If you are deploying a new system on Oracle Database 19c, then Oracle recommends that you use the new automatic SPM defaults. However, I am aware that some DBAs use SQL plan management in a tactical fashion; controlling regression in a carefully chosen set of SQL statements. Some DBAs will capture SQL statements according to a predetermined schedule and perhaps run SPM evolution manually as-and-when required. Some DBAs disable SPM evolution altogether, choosing exactly when and how to evolve a particular SQL statement.
If you currently have a particular way of using SPM, then when you upgrade to Oracle Database 19c you will need to decide whether to choose:
- Option#1: Use the new 'auto regime' in Oracle Database 19c alone.
- Option#2: Continue to use SPM in the way you have in the past, but in conjunction with automatic SPM.
- Option#3: Disable automatic SPM and continue to use SPM in the way you have used it in the past.
Option #1
Use default settings.
Option #2
Use default settings and continue to capture and evolve as you see fit. The automatic approach can work along-side existing strategies.
Option #3
If you don't want to use automatic SPM and use the same defaults as Oracle Database 12c Release 2 or Oracle Database 18c, then you can change the DBMS_SPM settings as follows:
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
parameter => 'ALTERNATE_PLAN_BASELINE',
value => 'EXISTING'); /* 19c Default is AUTO */
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'); /* 19c default is AUTO */
END;
/
If you don't want to use automatic SPM and use the same defaults as Oracle Database 12c Release 1:
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;
/
Notes
To view current parameter settings:
SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_ADVISOR_PARAMETERS WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';If you want to accept evolved execution plans manually, I recommend that you continue to allow the automatic SPM evolve advisor task to execute (so that you can view SPM reports and findings). Then, to prevent alternative plans from being accepted automatically, use the following setting:
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
parameter => 'ACCEPT_PLANS',
value => FALSE);
END;
/
Note that the evolve advisor task, SYS_AUTO_SPM_EVOLVE_TASK, was introduced in Oracle Database 12c.
Summary
Automatic SQL plan management is a great new way to prevent query performance regressions and capture SQL plan baselines transparently, without any management overhead.
The parameters listed above were introduced in Oracle Database 12c Release 2. However, the internal implementation for identifying alternative plans has been enhanced in Oracle Database 19c. The creation of new SQL plan baselines for queries that previously had no plan baselines has become the new default setting.
Regards,
Nigel
although automatic SQL Plan Baselines are the default in 19c, the parameter 'optimizer_capture_sql_plan_baselines' in 19c is set to false. Is this parameter obsolete now? I ask, because it is not listed in v$obsolete_parameter
thanks,
Nik
Regards,
Nigel