Oracle 19C 管理SPM Evolve Advisor任务

原创 Asher.HU 2021-02-04

1862

SPM Evolve Advisor是一个SQL顾问，用于发展最近已添加到SQL计划基准中的计划。顾问消除了手动进行计划的需要，从而简化了计划制定过程。

本节包含以下主题：

关于SPM Evolve Advisor任务默认情况下，SYS_AUTO_SPM_EVOLVE_TASK每天在计划的维护窗口中运行。
启用和禁用SPM Evolve Advisor任务对于自动SPM Evolve Advisor任务，没有单独的调度程序客户端。
配置SPM Evolve Advisor 自动任务通过使用DBMS_SPM.SET_EVOLVE_TASK_PARAMETER过程指定任务参数来配置自动计划演化。

28.2.2.1关于SPM Evolve Advisor任务

默认情况下，SYS_AUTO_SPM_EVOLVE_TASK每天在计划的维护窗口中运行。

SQL计划基准可防止由于次优计划而导致的性能下降。如果SQL语句没有SQL计划基准，并且alternate_plan_baseline参数设置为AUTO，则SQM Evolve Advisor有时可以自动解决这种性能下降问题。顾问将比较所有可用计划，然后选择性能最佳的计划作为基准。

下图显示了自动SPM Evolve Advisor的工作流程：

图28-2自动SPM Evolve Advisor

每当它在维护窗口中运行时，SPM Evolve Advisor都会执行以下任务：

检查AWR以获得最佳SQL
AWR存储最消耗资源的SQL语句。SPM Evolve Advisor在AWR中搜索最有可能从SQL计划基准中受益的语句，然后将这些语句的计划添加到基准中。
在所有可用资源中寻找替代计划
默认情况下（alternate_plan_source=AUTO），自动任务会在所有可用存储库中搜索SMB计划历史记录中尚未存在的计划。的设置alternate_plan_source显示在DBA_ADVISORS_PARAMETERS视图中。
将不接受的计划添加到计划历史记录
这些计划尚未在任何SQL语句的SQL计划基线中。
在维护时段内测试尽可能多的计划的执行
对于每个替代计划，数据库测试都会执行该语句并记录性能统计信息。目标是使用基于成本的算法将每个替代计划的性能与优化程序原本会选择的计划进行比较。
根据替代计划是否比当前计划执行得更好，执行以下任一操作：
- 如果性能更好，则SPM Evolve Advisor接受该计划。替代计划现在处于基准中。
- 如果性能较差，则该计划将保留在语句历史记录中，而不是基线中。

示例28-1超出范围的查询

假设应用程序发出了一个新的，长期运行的查询sh.products。该查询prod_list_price使用绑定变量引用该列。数据库执行以下步骤：

优化器将根据最新统计信息为该查询选择一个最佳计划，该统计信息prod_list_price的最高标价为1299.99美元。最佳计划位于AWR。
应用程序将更多产品添加到sh.products表中，并更改许多产品的标价。
应用程序发出原始查询，优化程序将重新解析该原始查询。
在执行查询的过程中，绑定变量将标价设置为$ 1500，高于表统计信息中记录的最大值$ 1299.99。这被称为超范围查询。
优化器为超出范围的查询选择了次优计划，从而导致性能下降。
该优化器尝试通过允许超出范围的条件来避免性能下降，但有时无法成功，如本例所示。结果是次优计划。
该数据库按如下方式解决性能下降问题：
- SPM Evolve Advisor将长时间运行的查询标识为SQL计划基准的候选对象。
- SPM Evolve Advisor会找到两个计划，即AWR中的原始计划和超范围查询的次优计划，并确定原始计划的性能更好。
- SQL计划管理将查询的原始计划添加到SQL计划基线。因此，优化器将不会使用回归计划。

也可以看看：

Oracle数据库许可信息用户手册，详细介绍了不同版本和服务支持哪些功能
Oracle Database PL / SQL软件包和类型参考，以了解有关该DBMS_SPM.SET_EVOLVE_TASK_PARAMETER过程的更多信息

28.2.2.2启用和禁用SPM Evolve Advisor任务

自动SPM Evolve Advisor任务没有单独的调度程序客户端。

一个客户端同时控制自动SQL Tuning Advisor和自动SPM Evolve Advisor。因此，同一任务启用或禁用这两者。

也可以看看：

“ 启用和禁用自动SQL调整任务 ”以了解如何启用和禁用自动SPM Evolve Advisor

28.2.2.3配置自动SPM Evolve Advisor任务

通过使用DBMS_SPM.SET_EVOLVE_TASK_PARAMETER过程指定任务参数来配置自动计划演变。

由于SYS_AUTO_SPM_EVOLVE_TASK任务由拥有SYS，因此只能SYS设置任务参数。

下表描述了一些过程参数。

表28-3 DBMS_SPM.SET_EVOLVE_TASK_PARAMETER参数

参数	描述	默认
`alternate_plan_source`	确定要搜索的来源增发预案：`CURSOR_CACHE`，`AUTOMATIC_WORKLOAD_REPOSITORY`或`SQL_TUNING_SET`。将多个值与加号（`+`）组合。	默认值取决于SPM Evolve Advisor任务是自动还是手动：如果是自动的，则默认值为`AUTO`。如果为手动，则默认值为`CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY`。
`alternate_plan_baseline`	确定应加载哪些替代计划： `AUTO` 赋予数据库自主权以选择是否为带有或不带有基线的语句加载计划。 `EXISTING`使用具有现有基准的for语句加载备用计划。 `NEW` 加载没有基线的语句的替代计划，在这种情况下，将创建新的基线。您可以将多个值与加号（`+`）组合在一起，如中所示`EXISTING+NEW`。	默认值取决于SPM Evolve Advisor任务是自动还是手动：如果是自动的，则默认值为`AUTO`。如果为手动，则默认值为`EXISTING`。
`alternate_plan_limit`	指定总共可以加载的最大计划数（即，不是每个SQL语句的限制）。	默认值取决于SPM Evolve Advisor任务是自动还是手动：如果是自动的，则默认值为`UNLIMITED`。如果为手动，则默认值为`10`。
`accept_plans`	指定是否自动接受推荐的计划。如果`ACCEPT_PLANS`为`true`，则SQL计划管理将自动接受任务建议的所有计划。设置`false`为时，任务会验证计划，然后生成其发现的报告，但不会演变计划。	默认值为`true`顾问程序是自动运行还是手动运行。

本节中的教程假定以下内容：

您希望数据库自动接受计划。
您希望任务在每次执行1200秒后超时。
您希望Evolution任务在共享的SQL区域和AWR存储库中最多查找500个计划。

设置自动演化任务参数：

启动SQL * Plus，然后以身份登录到数据库SYS。

查询的当前参数设置SYS_AUTO_SPM_EVOLVE_TASK。

例如，使用管理员权限将SQL * Plus连接到数据库，然后执行以下查询：

COL PARAMETER_NAME FORMAT a25
COL VALUE FORMAT a42
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   DBA_ADVISOR_PARAMETERS
WHERE  ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
         ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
           (PARAMETER_NAME LIKE '%ALT%') OR
           (PARAMETER_NAME = 'TIME_LIMIT') ) );

示例输出如下所示：

PARAMETER_NAME            VALUE
------------------------- ------------------------------------------
ALTERNATE_PLAN_LIMIT      0
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              true
TIME_LIMIT                3600

使用以下形式的PL / SQL代码设置参数：

BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => parameter_name
,   value     => value
);
END;
/

例如，以下PL / SQL块将SYS_AUTO_SPM_EVOLVE_TASK任务配置为自动接受计划，在共享的SQL区域和AWR存储库中最多查找500个计划，并在20分钟后超时：

BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => 'TIME_LIMIT'
,   value     => '1200'
);
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => 'ACCEPT_PLANS'
,   value     => 'true'
);
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SPM_EVOLVE_TASK'
,   parameter => 'ALTERNATE_PLAN_LIMIT'
,   value     => '500'
);
END;
/

（可选）通过查询的当前参数设置来确认更改SYS_AUTO_SPM_EVOLVE_TASK。

例如，执行以下查询：

SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM   DBA_ADVISOR_PARAMETERS
WHERE  ( (TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK') AND
         ( (PARAMETER_NAME = 'ACCEPT_PLANS') OR
           (PARAMETER_NAME LIKE '%ALT%') OR
           (PARAMETER_NAME = 'TIME_LIMIT') ) );

示例输出如下所示：

PARAMETER_NAME            VALUE
------------------------- ------------------------------------------
ALTERNATE_PLAN_LIMIT      500
ALTERNATE_PLAN_SOURCE     CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY
ALTERNATE_PLAN_BASELINE   EXISTING
ACCEPT_PLANS              true
TIME_LIMIT                1200

也可以看看：

《 Oracle数据库PL / SQL软件包和类型参考》，以获得有关DBMS_SPM.SET_EVOLVE_TASK_PARAMETER
Oracle数据库参考以了解有关DBA_ADVISOR_PARAMETERS视图的更多信息

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 19C 管理SPM Evolve Advisor任务

28.2.2.1关于SPM Evolve Advisor任务

28.2.2.3配置自动SPM Evolve Advisor任务

评论