什么是自动SQL执行计划管理以及为何应该关注它

Oracle Database 19c增加了名为自动SQL执行计划管理（automatic SQL plan management）的功能。本文包含：

• 什么是自动SQL执行计划管理以及它是如何工作的
• 如何配置
• 总结

哪里可用？

这是一个自治功能，所以它只在部分平台上可用。查看table 1-6 of the license guide 获取详情。
如果你没有使用所支持的平台，在Oracle Database 19c中，仍然有一些很棒的，SPM的增强功能可以去使用，请点此链接了解。

它是什么？

自动SQL执行计划管理识别明显消耗了系统资源的SQL语句 （通过检查AWR和SQL调优集）。历史的性能信息用于确定是否存在可能的性能退化。应用查询Q1语句在过去的两年里可以在1分钟内完成，但今天花费了30分钟，候选的执行计划自动定位并测试执行（使用SPM演进），在不需要DBA干预的情况下，最优的执行计划被用于SQL执行计划基线（SQL plan baselines）。
简而言之：SQL语句性能的退化被自动修复。

如何启用？

自动模工的实现是基于高频的SPM顾问任务。启用后，其周期性的执行以下的工作流程：

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','ON');
END;
/
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_SOURCE',
      value     => 'AUTO');
END;
/


复制

如何工作的?

以下是工作过程摘要：

检查AWR中是否存在消耗大量系统资源的SQL。此外，还会检查自动SQL调优集（Automatic SQL Tuning Set 缩写ASTS)。
点此了解何为自动SQL调优集

数据库在ASTS中寻找候选的SQL执行计划，被找到的执行计划被添加到SPM的执行计划历史中。
SPM演进顾问测试执行候选的执行计划并比较其性能表现。
演进顾问确定哪个执行计划的性能最优，并把它们添加到SQL执行计划基线中。
SQL执行计划基线来防止“退化”的执行计划被使用。

如何关闭?

一些DBA做按照一定的策略使用SPM；在一个精心选择的SQL语句集中控制退化。还有一些会按照预先确定的计划捕获SQL语句，并在需要时手动执行SPM演进。再有一些完全关闭SPM演进，人工选择何时，以及如何演进特定的SQL语句。

Oracle Database 19c使用与Oracle Database 12c Release 2 和 Oracle Database 18c中同样的默认设置。如果你在这两个版本中使用的就是默认值，并希望升级后仍继续这样使用，那么什么都不需要改变。
非自动模式对于 on-premises数据库是默认设置，所以，如果你正在使用自动模式不可用的平台，那么不需要显式的禁用它。尽管如此，是可以按下面的方法关闭它的：

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
END;
/
-- To return parameters to defaults
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;
/


复制

注意事项

查看高频任务说详情：

select * 
from   dba_autotask_schedule_control 
where  dbid = sys_context('userenv','con_dbid') 
and    task_name = 'Auto SPM Task';

复制

查看当前的参数设置：

SELECT PARAMETER_NAME, PARAMETER_VALUE 
FROM   DBA_ADVISOR_PARAMETERS 
WHERE  TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';


复制

总结

自动SQL执行计划管理是一个非常好的新方法，它可以透明的捕获SQL执行计划基线，并且在无任何管理开销的情况下，修复SQL性能退化。

上面使用的参数在Oracle Database 12c Release 2时被首次引入。用于持续找到候选执行计划的内部实现，在 Oracle Database 18c 和 Oracle Database 19c中做了增强。
详情可参考SQL Tuning Guide

原文链接：https://blogs.oracle.com/optimizer/post/what-is-automatic-sql-plan-management-and-why-should-you-care

What is automatic SQL plan management and why should you care?
April 8, 2019 | 3 minute read
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.
• How to configure.
• Summary.

Where is it available?

This is an autonomous feature so it is only available on some platforms. See Automatic SQL Plan Management in table 1-6 of the license guide for full details.

If you are not using a supported platform, there are still some great SPM enhancements in Oracle Database 19c for you to make use of - see this post.

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 implemented by the high-frequency SPM advisor task. When enabled, it periodically executes the workflow described below:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','ON');
END;
/
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_SOURCE',
      value     => 'AUTO');
END;
/


复制

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](https://blogs.oracle.com/optimizer/post/what-is-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 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 as follows:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
END;
/
-- To return parameters to defaults
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 view details of the high-frequency task:

select * 
from   dba_autotask_schedule_control 
where  dbid = sys_context('userenv','con_dbid') 
and    task_name = 'Auto SPM Task';

复制

To view current parameter settings:

SELECT PARAMETER_NAME, PARAMETER_VALUE 
FROM   DBA_ADVISOR_PARAMETERS 
WHERE  TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';


复制

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.