X

Insights into Statistics, Query Optimization and the Oracle Optimizer

What is automatic SQL plan management and why should you care?

Nigel Bayliss
Product Manager

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.

Where is it available?

Recently we changed the availability of this feature so that the new auto mode is no longer available on all platforms in Oracle Database 19c. In addition, it is no longer the default setting.See Automatic SQL Plan Management in table 1-6 of the license guide.

Nevertheless, SPM still enables you to 'repair' SQL performance regressions by locating better SQL execution plans. Take a look at this post.

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 is it enabled?

The automatic mode is enabled by setting the DBMS_SPM parameter ALTERNATE_PLAN_BASELINE to AUTO. This setting not available on all platforms.

The ALTERNATE_PLAN_SOURCE parameter can be set to AUTO on all platforms and it is currently equivalent to "AUTOMATIC_WORKLOAD_REPOSITORY+CURSOR_CACHE+SQL_TUNING_SET".

How does it work?

Here is a summary of the flow:

Automatic SQL plan management

 

  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) if it is available (this is a tuning set maintained by the database primarily for automatic indexing). 
  2. 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.
  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 configured?

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

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; 
/

Option #2

Use AUTO and continue to capture and evolve as you see fit. The automatic approach can work along-side existing strategies.

Option #3

The Oracle Database 19c defaults are now the same as Oracle Database 12c Release 2 and Oracle Database 18c:

BEGIN 
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ALTERNATE_PLAN_BASELINE', 
      value     => 'EXISTING');   /* The Default */
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');  /* The Default */
END; 
/

If you want to use the same settings in Oracle Database 19c as the Oracle Database 12c Release 1 defaults:

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 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.

 

Join the discussion

Comments ( 4 )
  • Luis Santos Monday, June 3, 2019
    Is possible to configure/change a 12.2 or 18c rdbms to emulate 19c behaviour? In another words: set the parameters in a inverse sense as option #3?
  • Nigel Bayliss Monday, June 3, 2019
    Hi Luis, I'm afraid not. It's interesting to note that 12.2 and 18c have a DBMS_SPM Evolve "alternate_plan_source" setting that looks like Oracle Database 19c, but the internals are different. You can be forgiven for thinking they are the same!
    Regards,
    Nigel
  • Nik Eichler Monday, August 12, 2019
    Hi 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
  • Nigel Bayliss Wednesday, August 21, 2019
    Hi Nik - this parameter is still available and you can continue to capture SQL plan baselines like this if you want to. It is a perfectly valid way to capture plans still and we have no plans to phase this out.
    Regards,
    Nigel
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.