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.
  • How to configure.
  • Summary.

Oracle Database 23ai and Oracle Autonomous Database 19c includes additional automatic SPM capabilities (called real-time SPM). This is covered in another post.

How is it licensed?

Historically, this feature had some licensing restrictions limiting it to Autonomous Database and Exadata. However, its availability has just been increased, so it’s now available in Oracle Database 19c Release Update 19.22 Enterprise Edition and Oracle BaseDB 23c BaseDB Enterprise Edition.

See this blog post or check the Automatic SQL Plan Management enty in table 1-6 of the license guide for details.

What is it?

Automatic SQL plan management identifies SQL statements that are consuming significant system resources (by inspecting the AWR and SQL tuning sets). Historic performance information is used to establish whether there has been a likely performance regression. Perhaps application query Q1 has been completing in 1 minute for the past two years but today it takes 30 minutes. Alternate SQL execution plans are located automatically and test executed (using SPM evolution). The best plans are then enforced using SQL plan baselines without DBA intervention.

In short: SQL statement performance regressions are repaired automatically.

What, another AUTO?

In the context of SPM, the word ‘auto’ is used rather a lot. Here are the things that are not directly related to autmatic SPM:

  • Auto capture. This is related to the parameter optimizer_capture_sql_plan_baselines and came in with Oracle Database 11g. It enables SPM to capture all repeatable SQL statements. This should be set to FALSE if you’re going to use automatic SPM.
  • The SPM automatic evolution task. In Oracle Database 12c, we enabled a background task that runs in the maintenance window to evolve SQL plan baselines automatically. This is controlled using DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(task_name=>’SYS_AUTO_SPM_EVOLVE_TASK’…). You can disable this task if you want to use automatic SPM, but it doesn’t matter if you don’t.

Automatic SPM is disitinct from these ‘autos.’ It is enabled and disabled using DBMS_SPM.CONFIGURE (see below). When you set AUTO_SPM_EVOLVE_TASK to ON, you start the high-frequency SPM evolve task, which runs periodically inside and outside the maintenance window. You can see this task by querying dba_autotask_schedule_control (there’s an example below).

How is auto SPM enabled?

The automatic mode is implemented by the high-frequency SPM advisor task.

In Oracle Autonomous Database it is already ON by default.

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','ON');
END;
/

-- For non-autonomous systems only, in the relevant PDB,
-- execute the following as SYS to ensure that the correct
-- parameter values for AUTO SPM are set
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_SOURCE',
      value     => 'SQL_TUNING_SET');
END;
/
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ACCEPT_PLANS',
      value     => 'TRUE');
END;
/

How does it work?

Here is a summary of the flow:

  1. 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).
  2. The database looks for alternative SQL execution plans in ASTS. The plans identified are added to the SPM SQL plan history.
  3. The SPM evolve advisor test executes the alternative plans and compares their performance.
  4. The evolve advisor decides which plans perform best and adds them to the SQL plan baseline.
  5. SQL plan baselines prevent ‘regressed’ execution plans from being used.

How is it disabled?

Some DBAs use SQL plan management in a tactical fashion; controlling regression in a carefully chosen set of SQL statements. Some will capture SQL statements according to a predetermined schedule and perhaps run SPM evolution manually as-and-when required. Some disable SPM evolution altogether, choosing exactly when and how to evolve a particular SQL statement.

Oracle Database 19c uses the same defaults as Oracle Database 12c Release 2 and Oracle Database 18c. If you are using the defaults in either of these releases and want to continue to do this post-upgrade, then there is no need to change anything.  Non-auto mode is the default for on-premises databases, so If you are using a platform where AUTO mode is not available, then there is no need to explicitly disable it. Nevertheless, it can be disabled (in Autonomous Database and on-premises) as follows:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
END;
/

-- For non-autonomous systems only, in the relevant PDB, 
-- you will probably want to return to default 'manual' SPM values.
-- To do that, execute the following as SYS
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     => 'AUTO');
END;
/

Notes

To see the status of automatic SPM directly (e.g. ON or OFF), you can do this:

SELECT parameter_value spm_status
FROM   dba_sql_management_config
WHERE  parameter_name = 'AUTO_SPM_EVOLVE_TASK';

You can check the high-frequency task to see if it is enabled:

SELECT enabled 
FROM   dba_autotask_schedule_control 
WHERE  dbid = sys_context('userenv','con_dbid') 
AND    task_name = 'Auto SPM Task';

To view the task parameter settings:

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
AND    parameter_value != 'UNUSED';

Can auto SPM be limited to a single schema?

No. You may be aware of auto capture filters – auto_capture_action, auto_capture_module, auto_capture_parsing_schema_name and auto_capture_sql_text. These are associated with SPM auto capture (where optimizer_capture_sql_plan_baselines=TRUE) and are not directly related to automatic SPM.

Summary

Automatic SQL plan management is a great new way to capture SQL plan baselines transparently and repair SQL performance regressions without any management overhead.
The parameters used above were first introduced in Oracle Database 12c Release 2. The internal implementation for identifying alternative plans continued to be enhanced in Oracle Database 18c and Oracle Database 19c.
See the SQL Tuning Guide for more details.