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

 

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.

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.

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.

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

The database looks for alternative SQL execution plans in ASTS. The plans identified are added to the SPM 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.

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

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.

For reference, this is the Oracle Database 19c default setting:

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