【12.2 新功能】优化器统计顾问（Optimizer Statistics Advisor）及常见问题

SQLplusDB

发布于 2020-03-26 10:44:32

1.5K0

发布于 2020-03-26 10:44:32

文章被收录于专栏：Oracle数据库技术Oracle数据库技术

Keyword:

Optimizer Statistics Advisor，统计信息，ORA-20001 ， ORA-00932 ，12.2.0.1

概述：

对于Oracle的CBO优化器而言，统计信息是其最重要的组成部分，因此，Oracle在统计信息的准确性方面不断地进行优化和加强。

从12.2版本开始，Oracle推出了优化器统计顾问（Optimizer Statistics Advisor），根据事先定义的规则，定期执行，提供给用户参考的统计信息收集相关的建议。

▲优化器统计顾问原理

参考：

Home/Database/Oracle/Oracle Database/Release 12.2
SQL Tuning Guide
>18 Analyzing Statistics Using Optimizer Statistics Advisor

   Optimizer Statistics Advisor In 12.2 (Quick Overview) (Doc ID 2259398.1)
    12c Release 2 New Feature : Statistics Advisor (Walk-Through) (Doc ID 2360311.1)
    AUTO_STATS_ADVISOR_TASK Running Outside of Maintenance Window (Doc ID 2387110.1)

统计信息收集相关内容：

【常用命令】自动统计收集的停止（无效）和启动（有效）

自动统计信息收集（Automatic Optimizer Statistics Collection）

在12.2.0.1以后的版本上运行该功能时，遇到问题的报告很多,但是Oracle也在不断地修正着相关地问题，所以保持数据库为最新版本通常是一个最佳的选择。

常见问题和解决方法：

问题1：

由于12.2.0.1等版本上的设计不足(Bug) 等，可能会在告警日志（alert log）或者JOB跟踪日志中，发现ORA-12012, ORA-20001等错误。

例：

    >ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_nn"
    >ORA-20001: Statistics Advisor: Invalid Task Name For the current user
    >ORA-06512: at "SYS.DBMS_STATS", line 46861
    >ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 734
    >ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 19930
    >ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21934
    >ORA-06512: at "SYS.DBMS_STATS", line 46851

可以参考如下方法尝试解决。

1.通过dbms_stats.init_package()程序包，重新创建优化器统计顾问任务。

例：
 EXEC dbms_stats.init_package();

2. 首先看看这个bug 27983174和27774706是否被修复，如果存在则建议打上补丁或打上最新的RU.

如果没有被修复，可向官方技术支持申请Patch 27983174 和Patch 27774706 补丁并应用。

3. 应用Patch 27774706 补丁后，执行如下操作：

$ sqlplus / as sysdba
drop table WRI$_ADV_DEFINITIONS;
@?/rdbms/admin/catadvtb.sql
@?/rdbms/admin/utlrp.sql
execute dbms_advisor.setup_repository;
EXEC dbms_stats.init_package();

4.如果应用Patch 27774706 补丁过程中出错的话，试着执行如下命令手动更新状态。

UPDATE dba_registry_sqlpatch
SET status = 'SUCCESS', action_time = SYSTIMESTAMP
WHERE patch_id = 27774706 and status='WITH ERRORS' and rownum=1;
commit;

很多问题会在19c以后的版本进行修复，所以有可能的话注意保持版本的更新。

参考：

Recurring ORA-12012, ORA-20001, ORA-06512 In Container Database (Doc ID 2420581.1)  12.2.0.0.2
Automatic Statistics Advisor Job Errors with Statistics Fatal Error (Doc ID 2448436.1)  Version 12.2.0.1 to 18.3.0.0.0
ORA-12012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2.0 Database version or higher release (like 18c) (Doc ID 2127675.1)  Version 12.2.0.1 and later

问题2：

由于优化器统计顾问的执行，SYSAUX表空间增长过快，导致可用表空间不足。

特征：

  V$SYSAUX_OCCUPANTS中，SM/ADVISOR占据大量空间
  DBA_SEGMENTS中，WRI$_ADV_OBJECTS占据大量空间

解决方法：

1. 尝试删除Statistics Advisor 任务(AUTO_STATS_ADVISOR_TASK)，然后重建。

具体删除方法例:

 connect / as sysdba
 --1.1 删除Statistics Advisor 任务
 DECLARE
 v_tname VARCHAR2(32767);
 BEGIN
 v_tname := 'AUTO_STATS_ADVISOR_TASK';
 DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
 END;
 /

 --1.2 删除任务之后，重组表和所有索引
 SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
 SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
 SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

 注：如果在删除过程中，发生ORA-20001等错误的话，可以执行下面的方法重建AUTO_STATS_ADVISOR_TASK来解决，
     然后再执行上面的删除方法，最终达到删除Statistics Advisor 任务(AUTO_STATS_ADVISOR_TASK)的目的。
SQL> connect / as sysdba
SQL> EXEC DBMS_STATS.INIT_PACKAGE();

2. 缩短任务执行历史的保存时间

-- 确认当前设定的保持期间
 select task_name, parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
 where task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME like '%EXPIRE%';
 
-- 修改设定的保持期间
 可以通过下面的命令，将EXECUTION_DAYS_TO_EXPIRE修改为10天，即执行历史数据的保存时间为10天：


 connect /as sysdba
 BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
   task_name => 'AUTO_STATS_ADVISOR_TASK'
  , parameter => 'EXECUTION_DAYS_TO_EXPIRE'
  , value => 10
 );
 END;
 /

注：未来版本中，可以通过如下方法禁用Statistics Advisor 任务。

现阶段（2020/1）无法禁用这个任务。


DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK',NULL,NULL,'DISABLE');
END;
/

参考：

How To Set DAYS_TO_EXPIRE and EXECUTION_DAYS_TO_EXPIRE of Automatic Statistics Advisor Task (Doc ID 2544788.1)
升级DB到12.2.0.1版本之后，由于统计信息顾问导致SYSAUX 过快增长 (Doc ID 2440139.1)
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)

3. Bug的影响

12.2.0.1版本由于Bug26764561的影响，即使EXECTION_DAYS_TO_EXPIRE设成10天后，优化器统计顾问相关段依然不断增加。

所以首先看看这个bug是否被修复，如果存在则建议打上补丁或打上最新的RU。

参考：

AUTO_STATS_ADVISOR_TASK Not Purging Even Though Setting EXECUTION_DAYS_TO_EXPIRE (Doc ID 2615851.1)
>Bug 26764561(Doc ID 26764561.8)

※注意打补丁后的Postinstallation内容

■ 关于EXECUTION_DAYS_TO_EXPIRE的默认值：

ver<=18.3
        SQL> EXEC DBMS_STATS.INIT_PACKAGE();
        PL/SQL procedure successfully completed.

        SQL> SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
          WHERE task_name='AUTO_STATS_ADVISOR_TASK'
          and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';
          2    3
        PARAMETER_NAME
        --------------------------------------------------------------------------------
        PARAMETER_VALUE
        --------------------------------------------------------------------------------
        EXECUTION_DAYS_TO_EXPIRE
        UNLIMITED

 Version 19.3.0.0.0 &18.5 (Bug 27983174修复后)

        SQL> SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
                  WHERE task_name='AUTO_STATS_ADVISOR_TASK'
                  and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';  2    3
        PARAMETER_NAME
        --------------------------------------------------------------------------------
        PARAMETER_VALUE
        --------------------------------------------------------------------------------
        EXECUTION_DAYS_TO_EXPIRE
        30

问题3：关于在多租户环境中 CDB/PDB的设置问题

对于多租户环境中，CDB/PDB的设置相对独立，互相不影响。

■EXECUTION_DAYS_TO_EXPIRE的CDB/PDB设置测试：

---CDB側
    SQL> conn / as sysdba
    Connected.
    SQL> SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
                      WHERE task_name='AUTO_STATS_ADVISOR_TASK'
                      and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';  2    3

    PARAMETER_NAME
    --------------------------------------------------------------------------------
    PARAMETER_VALUE
    --------------------------------------------------------------------------------
    EXECUTION_DAYS_TO_EXPIRE
    UNLIMITED

    SQL> show pdbs
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             2 PDB$SEED                       READ ONLY  NO
             3 PDB1                           READ WRITE NO
    SQL>
    BEGIN
     DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
      task_name => 'AUTO_STATS_ADVISOR_TASK'
    , parameter => 'EXECUTION_DAYS_TO_EXPIRE'
    , value => 30
    );
    END;
    /
    SQL>   2    3    4    5    6    7    8
    PL/SQL procedure successfully completed.

    SQL> SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
                      WHERE task_name='AUTO_STATS_ADVISOR_TASK'
                      and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';   2    3
    PARAMETER_NAME
    --------------------------------------------------------------------------------
    PARAMETER_VALUE
    --------------------------------------------------------------------------------
    EXECUTION_DAYS_TO_EXPIRE
    30


---PDB側
    SQL> alter session set container=pdb1;
    Session altered.
   SQL> SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
                      WHERE task_name='AUTO_STATS_ADVISOR_TASK'
                      and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';  
    PARAMETER_NAME
    --------------------------------------------------------------------------------
    PARAMETER_VALUE
    --------------------------------------------------------------------------------
    EXECUTION_DAYS_TO_EXPIRE
    UNLIMITED

    SQL> show pdbs
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             3 PDB1                           READ WRITE NO
    SQL>

■PDB/CDB的JOB关联性的测试：

    SQL> conn / as sysdba
    Connected.
    SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
    where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  2
    AUTO_STATS_ADVISOR_TASK              25-SEP-19 CMD                             SYS

    SQL> DECLARE
                    v_tname VARCHAR2(32767);
                    BEGIN
                    v_tname := 'AUTO_STATS_ADVISOR_TASK';
                    DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
                    END;
                    /  2    3    4    5    6    7
    PL/SQL procedure successfully completed.

    SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
                    where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
      2
    no rows selected
    SQL> show pdbs
             2 PDB$SEED                       READ ONLY  NO
             3 PDB1                           READ WRITE NO

---PDB中JOB的状态
    SQL> alter session set container=pdb1;
    Session altered.

    SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
                    where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
      2  AUTO_STATS_ADVISOR_TASK     26-JAN-17 CMD                             SYS
    INDIVIDUAL_STATS_ADVISOR_TASK    26-JAN-17 CMD                             SYS

    SQL> DECLARE
                    v_tname VARCHAR2(32767);
                    BEGIN
                    v_tname := 'AUTO_STATS_ADVISOR_TASK';
                    DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
                    END;
                    /  2    3    4    5    6    7
    PL/SQL procedure successfully completed.

    SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
                    where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
      2  INDIVIDUAL_STATS_ADVISOR_TASK               26-JAN-17 CMD                 SYS

---CDB 不会影响PDB
    SQL> conn / as sysdba
    Connected.
    SQL>  EXEC DBMS_STATS.INIT_PACKAGE();
    PL/SQL procedure successfully completed.
    
    SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
                    where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
      2  AUTO_STATS_ADVISOR_TASK         15-NOV-19 CMD                             SYS
    INDIVIDUAL_STATS_ADVISOR_TASK        15-NOV-19 CMD                             SYS

    SQL> alter session set container=pdb1;
    Session altered.

    SQL> select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
                    where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
      2  INDIVIDUAL_STATS_ADVISOR_TASK    26-JAN-17 CMD                             SYS
    SQL>

问题4：PGA 限制ORA-4036

在执行统计信息收集JOB等的过程中会调用优化器统计顾问任务。

如果Statistics Advisor 任务的数据过多，有可能导致在执行统计信息收集JOB过程中引发ORA-4036.

例如输出的跟踪日志中的HEAP DUMP中qosadvCreateSu Chunk占用了较大空间，qosadvCreateSu是Statistics Advisor运行时所需的heap。

例：
        PRIVATE HEAP SUMMARY DUMP
        2582 MB total:
          1969 MB commented, 975 KB permanent
           612 MB free (0 KB in empty extents),
            2557 MB,   2 heaps:   "callheap       "            607 MB free held
        ------------------------------------------------------
        Summary of subheaps at depth 1
        1962 MB total:
          1957 MB commented, 4805 KB permanent
          1136 KB free (0 KB in empty extents),
            1940 MB,   1 heap:    "qosadvCreateSu "           ★

可以通过定期删除Statistics Advisor 任务的数据或者增加PGA_AGGREGATE_LIMIT的大小来解决。

用碎片化的时间，一点一滴地学习一套系统化的知识。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-02-08，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle

编程算法

本文分享自 Oracle数据库技术微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

oracle

编程算法

登录后参与评论

0 条评论

热度

【12.2 新功能】优化器统计顾问（Optimizer Statistics Advisor）及常见问题

【12.2 新功能】优化器统计顾问（Optimizer Statistics Advisor）及常见问题

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐