Oracle 19C 配置捕获和使用SQL计划基准

原创 Asher.HU 2021-02-04

1327

您可以配置SQL计划基线的捕获和使用，以及SPM Evolve Advisor任务。

本节包含以下主题：

配置SQL计划基准的捕获和使用您可以使用初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES和来控制SQL计划管理OPTIMIZER_USE_SQL_PLAN_BASELINES。
管理SPM Evolve Advisor任务 SPM Evolve Advisor是一种SQL顾问程序，用于改进最近添加到SQL计划基准中的计划。顾问消除了手动进行计划的需要，从而简化了计划制定过程。

28.2.1配置捕获和使用SQL计划基准

您可以使用初始化参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES和来控制SQL计划管理OPTIMIZER_USE_SQL_PLAN_BASELINES。

默认值如下：

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false
对于不已经在计划历史记录存在任何重复的SQL语句，数据库并不会自动创建的声明初始SQL计划基准。
如果为OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true，则可以使用该DBMS_SPM.CONFIGURE过程配置筛选器，以确定哪些语句适合计划捕获。默认情况下，未配置任何过滤器，这意味着所有可重复的语句均适用于计划捕获。
OPTIMIZER_USE_SQL_PLAN_BASELINES=true
对于具有现有SQL计划基准的任何SQL语句，数据库都会自动将新计划作为不可接受的计划添加到SQL计划基准。

注意：

前述参数的设置彼此独立。例如，如果OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES是true，那么数据库中创建新的报表初步计划基线，即使OPTIMIZER_USE_SQL_PLAN_BASELINES是false。

如果默认行为是您想要的，请跳过本节。

以下各节说明如何从命令行更改默认参数设置。如果使用Cloud Control，请在“ SQL计划基准”子页面中设置这些参数。本节包含以下主题：

为SQL计划管理启用自动初始计划捕获将数据库自动为计划历史记录中尚未存在的任何合格SQL语句自动创建初始SQL计划基线所需的所有操作都需要
将OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数设置为true。
为自动计划捕获配置过滤器
如果为OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true，则可以使用该DBMS_SPM.CONFIGURE过程为可重复语句创建自动捕获过滤器。
禁用所有SQL计划基准将初始化参数
设置OPTIMIZER_USE_SQL_PLAN_BASELINES为时false，数据库将不使用数据库中的任何计划基准。

也可以看看：

28.2.1.1为SQL计划管理启用自动初始计划捕获

数据库自动为计划历史记录中未包含的任何合格SQL语句自动创建初始SQL计划基线，将OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数设置为即可true。

默认情况下，数据库认为所有可重复的SQL语句都符合捕获条件，但以下情况除外：

CREATE TABLE未指定 AS SELECT子句时
DROP TABLE
INSERT INTO ... VALUES

警告：

默认情况下，启用自动基线捕获后，数据库将为每个合格的可重复语句（包括所有递归SQL和监视SQL）创建SQL计划基线。因此，自动捕获可能导致创建大量计划基准。要限制符合计划基准的语句，请使用以下DBMS_SPM.CONFIGURE步骤配置过滤器。

该OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES参数不控制将新发现的计划自动添加到先前创建的SQL计划基准中。

要为SQL计划管理启用自动初始计划捕获：

在SQL * Plus中，以必需的特权登录到数据库。
显示SQL计划管理的当前设置。
例如，使用管理员权限将SQL * Plus连接到数据库，然后执行以下命令（包括示例输出）：
```
SHOW PARAMETER SQL_PLAN
```
以下示例输出显示自动初始计划捕获已禁用：
```
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
```
如果参数已按预期设置，则跳过其余步骤。
要自动识别可重复的SQL语句并为这些语句生成SQL计划基线，请输入以下语句：
```
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
```

也可以看看：

Oracle数据库参考以了解更多信息OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

28.2.1.2配置用于自动计划捕获的过滤器

如果为OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true，则可以使用该DBMS_SPM.CONFIGURE过程为可重复语句创建自动捕获过滤器。

自动过滤器使您可以仅捕获所需的语句，并排除非关键语句。此技术节省了SYSAUX表空间中的空间。

下表描述了该DBMS_SPM.CONFIGURE过程的相关参数。

表28-2 DBMS_SPM.CONFIGURE参数

参数描述

参数	描述
`parameter_name`	自动捕获的过滤器类型。可能的值是`AUTO_CAPTURE_SQL_TEXT`，`AUTO_CAPTURE_PARSING_SCHEMA_NAME`，`AUTO_CAPTURE_MODULE`，和`AUTO_CAPTURE_ACTION`。
`parameter_value`	自动捕获过滤器的搜索条件。当`parameter_name`设置为时`AUTO_CAPTURE_SQL_TEXT`，搜索模式取决于`allow`设置： `LIKE` 参数在时使用此模式`allow=>true`。 `NOT LIKE` 参数在时使用此模式`allow=>false`。对于所有其他非空`parameter_name`值，搜索模式取决于以下`allow`设置： `=` 参数在时使用此模式`allow=>true`。 `<>` 参数在时使用此模式`allow=>false`。空值将`parameter_name`完全删除过滤器。
`allow`	是否包含（`true`）或排除（`false`）匹配的SQL语句和计划。如果为null，则该过程将忽略指定的参数。

parameter_name

自动捕获的过滤器类型。

可能的值是AUTO_CAPTURE_SQL_TEXT，AUTO_CAPTURE_PARSING_SCHEMA_NAME，AUTO_CAPTURE_MODULE，和AUTO_CAPTURE_ACTION。

parameter_value

自动捕获过滤器的搜索条件。

当parameter_name设置为时AUTO_CAPTURE_SQL_TEXT，搜索模式取决于allow设置：

LIKE
参数在时使用此模式allow=>true。
NOT LIKE
参数在时使用此模式allow=>false。

对于所有其他非空parameter_name值，搜索模式取决于以下allow设置：

=
参数在时使用此模式allow=>true。
<>
参数在时使用此模式allow=>false。

空值将parameter_name完全删除过滤器。

allow 是否包含（true）或排除（false）匹配的SQL语句和计划。如果为null，则该过程将忽略指定的参数。

您可以配置不同类型的多个参数。另外，您可以在数据库合并的单独语句中为同一参数指定多个值。这些设置是可加的：一个参数设置不会覆盖上一个设置。例如，以下过滤器捕获解析架构SYS或中的SQL SYSTEM：

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYS',true);
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','SYSTEM',true);

但是，不能在同一过程中为同一参数配置多个值。例如，您不能为指定多个SQL文本字符串AUTO_CAPTURE_SQL_TEXT。

该DBA_SQL_MANAGEMENT_CONFIG视图显示当前参数值。

本教程假定以下内容：

该OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES初始化参数设置为true。
您只想包含在sh架构中解析的语句，以符合基线条件。
您要排除包含文本的语句TEST_ONLY。

要过滤掉除在sh模式中解析的语句以外的所有语句：

使用适当的权限将SQL * Plus连接到数据库。

要删除任何现有的用于解析模式和SQL文本的过滤器，请执行以下PL / SQL程序：

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME',null,true);
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT',null,true);

仅包括在sh架构中解析的语句，以考虑自动捕获：

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME','sh',true);

TEST_ONLY 不考虑任何包含文本的语句以进行自动捕获：

EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT','%TEST_ONLY%',false);

（可选）要确认过滤器，请查询DBA_SQL_MANAGEMENT_CONFIG。

例如，使用以下查询（包括示例输出）：

COL PARAMETER_NAME FORMAT a32
COL PARAMETER_VALUE FORMAT a32

SELECT PARAMETER_NAME, PARAMETER_VALUE 
FROM   DBA_SQL_MANAGEMENT_CONFIG 
WHERE  PARAMETER_NAME LIKE '%AUTO%';

PARAMETER_NAME                   PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (SH)
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT            (sql_text NOT LIKE %TEST_ONLY%)

也可以看看：

“ 自动初始计划捕获 ”
Oracle Database PL / SQL软件包和类型参考，以了解有关该DBMS_SPM.CONFIGURE过程的更多信息
Oracle数据库参考以了解有关DBA_SQL_MANAGEMENT_CONFIG视图的更多信息

28.2.1.3禁用所有SQL计划基准

当您将OPTIMIZER_USE_SQL_PLAN_BASELINES初始化参数设置为时false，数据库将不使用数据库中的任何计划基线。

通常，您可能要禁用一个或两个计划基准，但不是全部禁用。一个可能的用例可能是测试SQL计划管理的好处。

要禁用数据库中的所有SQL计划基准：

使用适当的特权将SQL * Plus连接到数据库，然后显示SQL计划管理的当前设置。
例如，使用管理员权限将SQL * Plus连接到数据库，然后执行以下命令（包括示例输出）：
```
SQL> SHOW PARAMETER SQL_PLAN
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
```
如果参数已按预期设置，则跳过其余步骤。

要忽略所有现有计划基准，请输入以下语句：

SQL> ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES=false

也可以看看：

Oracle数据库参考，以了解SQL计划基线初始化参数

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

关注作者

Oracle 19C 配置捕获和使用SQL计划基准

28.2.1配置捕获和使用SQL计划基准

28.2.1.1为SQL计划管理启用自动初始计划捕获

28.2.1.2配置用于自动计划捕获的过滤器

28.2.1.3禁用所有SQL计划基准

评论