Oracle 19C 为Optimizer Advisor任务创建过滤

原创 Asher.HU 2021-02-04

1205

使用过滤器，您可以从Optimizer Statistics Advisor任务中包含或排除对象，规则和操作。

3.1关于Optimizer Statistics Advisor的过滤器

过滤器是用于DBMS_STATS将Optimizer Statistics Advisor任务限制为用户指定的一组规则，架构或操作。

过滤器对于包含或排除一组特定的结果很有用。例如，您可以将顾问程序任务配置为仅包含对sh架构的建议。此外，您可以排除所有违反规则的陈旧统计信息。筛选器的主要优点是可以忽略您不感兴趣的建议，并减少顾问程序任务的开销。

创建过滤器的最简单方法DBMS_STATS是单独或组合使用以下过程：

CONFIGURE_ADVISOR_OBJ_FILTER
使用此过程可以包括或排除指定的数据库模式或对象。对象过滤器接受所有者名称和对象名称，并%支持通配符（）。
CONFIGURE_ADVISOR_RULE_FILTER
使用此过程可以包括或排除指定的规则。通过查询获取规则名称V$STATS_ADVISOR_RULES。
CONFIGURE_ADVISOR_OPR_FILTER
使用此过程可以包括或排除指定的DBMS_STATS操作。通过查询获取ID和名称以进行操作DBA_OPTSTAT_OPERATIONS。

对于前面的功能，可以指定到该过滤器应用的操作的类型：EXECUTE，REPORT，SCRIPT，和IMPLEMENT。您还可以组合类型，如中所述EXECUTE + REPORT。Null表示该过滤器适用于所有类型的顾问程序操作。

18.2.3.2为Optimizer Advisor任务创建对象过滤器

该DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER函数为指定的Optimizer Statistics Advisor任务创建规则过滤器。该函数返回一个CLOB，其中包含过滤器的更新值。

您可以使用以下两种基本策略之一：

包括所有对象的发现（默认情况下，将考虑所有对象），然后排除指定对象的发现。
排除所有对象的发现，然后仅包括指定对象的发现。

先决条件

要使用该DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER功能，您必须满足以下先决条件：

要执行此子程序，您必须具有ADVISOR特权。
您必须是任务的所有者。

注意：

该子程序使用调用者的权限执行。

要创建对象过滤器：

在SQL * Plus或SQL Developer中，以具有必要特权的用户身份登录数据库。
使用该DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER函数排除或包括用于指定任务的对象。
以以下形式调用该函数，其中占位符的定义如下：
- report 是包含返回的XML的CLOB变量。
- tname 是任务的名称。
- opr_type 是要执行的操作类型。
- rule 是规则的名称。
- owner 是对象的架构。
- table 是表的名称。
- action是动作的名称：ENABLE，DISABLE，DELETE，或SHOW。
```
BEGIN
  report := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , ownname            => 'owner'
  , tabname            => 'table'
  , action             => 'action' );
END;
```

示例18-3在单个模式中仅包含对象

在此示例中，对于名为的任务opt_adv_task1，您的目标是禁用除sh架构中的对象之外的所有对象的建议。用户帐户sh已被授予ADVISOR和READ ANY TABLE权限。您执行以下步骤：

以身份登录数据库sh。

删除任何名为的现有任务opt_adv_task1。

DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

创建一个名为的过程sh_obj_filter，该过程将指定的任务限制为sh架构中的对象。

CREATE OR REPLACE PROCEDURE sh_obj_filter(p_tname IN VARCHAR2) IS
   v_retc CLOB;
BEGIN
   -- Filter out all objects that are not in the sh schema
   v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
               task_name          => p_tname
             , stats_adv_opr_type => 'EXECUTE'
             , rule_name          => NULL
             , ownname            => NULL
             , tabname            => NULL
             , action             => 'DISABLE' );

   v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(
               task_name          => p_tname
             , stats_adv_opr_type => 'EXECUTE'
             , rule_name          => NULL
             , ownname            => 'SH'
             , tabname            => NULL
             , action             => 'ENABLE' );
END;
/
SHOW ERRORS

创建一个名为的任务opt_adv_task1，然后执行sh_obj_filter该任务的过程。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  sh_obj_filter(v_tname);
END;
/

执行任务opt_adv_task1。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
begin
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/

18.2.3.3为Optimizer Advisor任务创建规则过滤器

该DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER函数为指定的Optimizer Statistics Advisor任务创建规则过滤器。该函数返回一个CLOB，其中包含过滤器的更新值。

您可以使用以下两种基本策略之一：

启用所有规则（默认情况下，启用所有规则），然后禁用指定的规则。
禁用所有规则，然后仅启用指定的规则。

先决条件

要使用该DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER功能，您必须满足以下先决条件：

要执行此子程序，您必须具有ADVISOR特权。
您必须是任务的所有者。

注意：

该子程序使用调用者的权限执行。

要创建规则过滤器：

在SQL * Plus或SQL Developer中，以具有必要特权的用户身份登录数据库。

通过查询获得顾问规则的名称V$STATS_ADVISOR_RULES。

例如，查询视图如下（包括部分示例输出）：

SET LINESIZE 200
SET PAGESIZE 100
COL ID FORMAT 99
COL NAME FORMAT a27
COL DESCRIPTION FORMAT a54

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
ORDER BY RULE_ID;

ID NAME                        RULE_TYPE DESCRIPTION
-- --------------------------- --------- -------------------------------------------------------
 1 UseAutoJob                  SYSTEM    Use Auto Job for Statistics Collection
 2 CompleteAutoJob             SYSTEM    Auto Statistics Gather Job should complete successfully
 3 MaintainStatsHistory        SYSTEM    Maintain Statistics History
 4 UseConcurrent               SYSTEM    Use Concurrent preference for Statistics Collection
...

使用该DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER函数排除或包括用于指定任务的规则。
以以下形式调用该函数，其中占位符的定义如下：
- tname 是任务的名称。
- report 是包含返回的XML的CLOB变量。
- opr_type 是要执行的操作类型。
- rule 是规则的名称。
- action是动作的名称：ENABLE，DISABLE，DELETE，或SHOW。
```
BEGIN
  report := DBMS_STATS.DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , action             => 'action' );
END;
```

示例18-4排除过时统计信息的规则

在此示例中，您知道统计信息是过时的，因为自动统计信息作业没有运行。您想要为名为的任务生成报告opt_adv_task1，但又不想因过时的统计信息而使报告混乱。

您查询V$STATS_ADVISOR_RULES处理过时统计信息的规则（包括示例输出）：

COL NAME FORMAT a15
SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
WHERE  DESCRIPTION LIKE '%tale%'
ORDER BY RULE_ID;

 ID NAME            RULE_TYPE DESCRIPTION
--- --------------- --------- -----------------------------------------
 12 AvoidStaleStats OBJECT    Avoid objects with stale or no statistics

您可以使用CONFIGURE_ADVISOR_RULE_FILTER来配置过滤器，以指定任务执行应排除该规则AvoidStaleStats，但应遵循所有其他规则：

VARIABLE b_ret CLOB
BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => 'AvoidStaleStats'
,    action             => 'DISABLE' );
END;
/

示例18-5仅包括避免陈旧统计信息的规则

此示例与前面的示例相反。要生成名为任务的报告opt_adv_task1，但希望看到只有约陈旧的统计数据的建议。

查询V$STATS_ADVISOR_RULES处理过时统计信息的规则（包括示例输出）：

COL NAME FORMAT a15

SELECT RULE_ID AS ID, NAME, RULE_TYPE, DESCRIPTION 
FROM   V$STATS_ADVISOR_RULES
WHERE  DESCRIPTION LIKE '%tale%'
ORDER BY RULE_ID;

 ID NAME            RULE_TYPE DESCRIPTION
--- --------------- --------- -----------------------------------------
 12 AvoidStaleStats OBJECT    Avoid objects with stale or no statistics

使用配置过滤器CONFIGURE_ADVISOR_RULE_FILTER，指定任务执行应排除所有规则：

VARIABLE b_ret CLOB
BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => null
,    action             => 'DISABLE' );
END;
/

配置仅启用AvoidStaleStats规则的过滤器：

BEGIN
   :b_ret := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER(
     task_name          => 'opt_adv_task1'
,    stats_adv_opr_type => 'EXECUTE'
,    rule_name          => 'AvoidStaleStats'
,    action             => 'ENABLE' );
END;
/

18.2.3.4为Optimizer Advisor任务创建操作过滤器

该DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER函数为指定的Optimizer Statistics Advisor任务创建一个操作过滤器。该函数返回一个CLOB，其中包含过滤器的更新值。

您可以使用以下两种基本策略之一：

禁用所有操作，然后仅启用指定的操作。
启用所有操作（默认情况下，启用所有操作），然后禁用指定的操作。

该DBA_OPTSTAT_OPERATIONS视图包含统计相关操作的ID。

先决条件

要使用DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER功能，必须满足以下先决条件：

要执行此子程序，您必须具有ADVISOR特权。
注意：
该子程序使用调用者的权限执行。
您必须是任务的所有者。
要查询DBA_OPTSTAT_OPERATIONS视图，您必须具有SELECT ANY TABLE特权。

创建操作过滤器：

在SQL * Plus或SQL Developer中，以具有必要特权的用户身份登录数据库。

查询操作类型。

例如，在中列出所有不同的操作DBA_OPTSTAT_OPERATIONS（包括示例输出）：

SQL> SELECT DISTINCT(OPERATION) FROM DBA_OPTSTAT_OPERATIONS ORDER BY OPERATION;

OPERATION
-----------------------
gather_dictionary_stats
gather_index_stats
gather_schema_stats
gather_table_stats
purge_stats
set_system_stats

通过查询获取要过滤的操作的ID DBA_OPTSTAT_OPERATIONS。

例如，要获取SYSand sh模式中表和索引的所有统计信息收集操作的ID ，请使用以下查询：

SELECT ID 
FROM   DBA_OPTSTAT_OPERATIONS
WHERE  (  OPERATION = 'gather_table_stats'
          OR OPERATION = 'gather_index_stats')
AND    (  TARGET LIKE 'SH.%'
          OR TARGET LIKE 'SYS.%');

使用DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER函数排除或包括用于指定任务的规则，并指定在上一步中获得的ID。
以以下形式调用该函数，其中占位符的定义如下：
- report 是包含返回的XML的CLOB变量。
- tname 是任务的名称。
- opr_type是要执行的操作类型。该值不能为空。
- rule 是规则的名称。
- opr_id是DBA_OPTSTAT_OPERATIONS.ID要执行的操作的ID（来自）。该值不能为空。
- action是动作的名称：ENABLE，DISABLE，DELETE，或SHOW。
```
BEGIN
  report := DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER(
    task_name          => 'tname'
  , stats_adv_opr_type => 'opr_type'
  , rule_name          => 'rule'
  , operation_id       => 'op_id'
  , action             => 'action' );
END;
```

示例18-6排除用于收集表统计信息的操作

在此示例中，您的目标是排除在hr模式中收集表统计信息的操作。用户帐户stats已被授予DBA角色，ADVISOR特权和SELECT ON DBA_OPTSTAT_OPERATIONS特权。您执行以下步骤：

以身份登录数据库stats。

删除任何名为的现有任务opt_adv_task1。

DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

创建一个名为的过程opr_filter，该过程将任务配置为建议所有操作，但那些收集hr模式中表统计信息的操作除外。

CREATE OR REPLACE PROCEDURE opr_filter(p_tname IN VARCHAR2) IS
   v_retc CLOB;
BEGIN
   -- For all rules, prevent the advisor from operating 
   -- on the operations selected in the following query
   FOR rec IN 
     (SELECT ID FROM DBA_OPTSTAT_OPERATIONS WHERE OPERATION = 'gather_table_stats' AND TARGET LIKE 'HR.%')
   LOOP
     v_retc := DBMS_STATS.CONFIGURE_ADVISOR_OPR_FILTER(
                 task_name          => p_tname
               , stats_adv_opr_type => NULL
               , rule_name          => NULL
               , operation_id       => rec.id
               , action             => 'DISABLE');
   END LOOP;
END;
/
SHOW ERRORS

创建一个名为的任务opt_adv_task1，然后执行opr_filter该任务的过程。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret VARCHAR2(32767);
BEGIN
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.CREATE_ADVISOR_TASK(v_tname);
  opr_filter(v_tname);
END;
/

执行任务opt_adv_task1。

DECLARE
  v_tname VARCHAR2(32767);
  v_ret   VARCHAR2(32767);
begin
  v_tname := 'opt_adv_task1';
  v_ret   := DBMS_STATS.EXECUTE_ADVISOR_TASK(v_tname);
END;
/

打印报告。

SPOOL /tmp/rep.txt
SET LONG 1000000
COLUMN report FORMAT A200
SET LINESIZE 250
SET PAGESIZE 1000

SELECT DBMS_STATS.REPORT_ADVISOR_TASK(
         task_name      => 'opt_adv_task1'
       , execution_name => NULL
       , type           => 'TEXT'
       , section        => 'ALL' 
       ) AS report
FROM   DUAL;
SPOOL OFF

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

关注作者

Oracle 19C 为Optimizer Advisor任务创建过滤

3.1关于Optimizer Statistics Advisor的过滤器

18.2.3.2为Optimizer Advisor任务创建对象过滤器

18.2.3.3为Optimizer Advisor任务创建规则过滤器

18.2.3.4为Optimizer Advisor任务创建操作过滤器

评论