12.2 开始如何清除 Optimizer Statistics Advisor 旧的记录

刘泽宇

目标
本文档的目标是提供一个方法来删除优化器统计信息顾问任务的（Optimizer Statistics Advisor Task）历史数据，以避免它过多消耗过多SYSAUX磁盘空间。

AUTO_STATS_ADVISOR_TASK 和 INDIVIDUAL_STATS_ADVISOR_TASK 两个task导致了表WRI$_ADV_OBJECTS记录大量数据，进而引发SYSAUX 表空间占用大量空间。AUTO_STATS_ADVISOR_TASK 是自动统计信息顾问任务（Automatic Statistics Advisor task），INDIVIDUAL_STATS_ADVISOR_TASK 是手工统计信息顾问任务（Manual Statistics Advisor task）。

DBA_ADVISOR_PARAMETERS 显示所有顾问任务的参数和当前值。其中有一个参数EXECUTION_DAYS_TO_EXPIRE，这个参数指定单位是天。执行信息早于这个天数的记录将会在自动清理窗口期间被自动清理掉。

在12.2.0.1版本上，参数EXECUTION_DAYS_TO_EXPIRE被设定为UNLIMITED，意味着历史数据永远不会被清理。

SQL> col TASK_NAME format a25
SQL> col parameter_name format a35
SQL> col parameter_value format a20
SQL> set lines 120
SQL> select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME                      PARAMETER_VALUE
------------------------- ----------------------------------- --------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE            UNLIMITED
由于AUTO_STATS_ADVISOR_TASK产生的记录永远不会从WRI$_ADV_OBJECTS中清理掉，故而导致SYSAUX空间持续增长。

一个未公开的Bug Bug 27983174 是导致参数EXECUTION_DAYS_TO_EXPIRE被设定成 UNLIMITED的原因，导致AUTO_STATS_ADVISOR_TASK的历史记录没有清理。 Document 27983174.8 lnx-183-mdb:got ORA-12012, ORA-20001 and ORA-6512 err from ora$at_os_opt_sy_xxx

另外一个未公开Bug Bug 26764561 导致了 delete_expired_tasks 没有正常工作。
Document 26764561.8 SQL TUNING Tasks may Fail With: ORA-00933: SQL command not properly ended (SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION)

解决方案
对于12.2.0.1，下载 Patch 30138470 12.2.0.1.191015 (Oct 2019) Database Release Update (DB RU) 或者更高版本来修复上述两个未公开bug。 对于18c，下载 Patch 28822489 18.5.0.0.190115 (Jan 2019) Database Release Update (DB RU) 或更高版本来修复。对于19C，这俩bug已经修复了，无需任何补丁。

重要：oracle 总是推荐您使用最新的DBRU来修复更多已知bug。

更多关于Release Updates信息，请参考 Document 2285040.1

应用了补丁修复了上述的未公开bug后，可以考虑使用下面的任何一个选项来修复历史数据清理问题：

Option 1 (自动清理):

设定参数EXECUTION_DAYS_TO_EXPIRE为30（默认）天，所有历史数据超过30天的都会被标记成过期，自动清理job将会清理这些过期数据。如果数据过多，可能一个清理窗口内无法完成，但是经过一些天之后，这些过期数据就会逐渐的被清理完毕。

Option 2 (手工):

这些过期数据可以通过下面手工的方式进行清理（下面命令会清理超过30天的数据）。
SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;


在多租户（CDB/PDB）环境 下，即使打了上述的DBRU修复了bug，但是会出现PDB里面的过期数据仍然没有被清理情况，您可以通过手工的方法来清理PDB里的数据，CDB的数据会自动清理。如下未公开的增强补丁来修复PDB 里面EXECUTION_DAYS_TO_EXPIRE不生效的问题。

<Unpublished> ENH 31028071 - PURGE EXPIRED AUTO_STATS_ADVISOR_TASK DATA IN PDB

Option 3 (定制):

如果默认的30天保留时间不能满足SYSAUX的剩余空间要求，您可以调整参数EXECUTION_DAYS_TO_EXPIRE更小，例如10（天），这样历史数据将会进一步清理。
SQL> EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=> 'AUTO_STATS_ADVISOR_TASK', parameter=> 'EXECUTION_DAYS_TO_EXPIRE', value => 10);
或者

SQL> EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => 10);
如果配置了 INDIVIDUAL_STATS_ADVISOR_TASK 并且存在太多记录，则还可以调整 EXECUTION_DAYS_TO_EXPIRE 参数值。

SQL> select TASK_NAME,parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME='EXECUTION_DAYS_TO_EXPIRE';
TASK_NAME                 PARAMETER_NAME                      PARAMETER_VALUE
------------------------- ----------------------------------- --------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE            10

现在，所有的统计信息顾问咨询记录若是早于10天的，都会被标记成过旧，而且会再自动清理窗口期间被自动清理掉。

如下是手工清理办法：

SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;
如果执行上述命令时，遇到了ORA-01555错误，请使用如下循环办法来每次清理50天数据：

    set serveroutput on
    DECLARE
        v_oldest INTEGER := 730; -- Two years
        v_increment INTEGER := 50;
        v_cur_age INTEGER;
        v_min_age INTEGER := 30; -- Retain one month.
    BEGIN
        v_cur_age := v_oldest;
        WHILE v_cur_age >= v_min_age LOOP
            dbms_sqltune.set_tuning_task_parameter(task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => v_cur_age);
            prvt_advisor.delete_expired_tasks;
            v_cur_age := v_cur_age - v_increment;
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Execution halted with error number ' || sqlerrm);
    END;
    /
完成数据清理后，请使用如下命令来释放空间并重建index。

SQL> alter table WRI$_ADV_OBJECTS move;
SQL> alter index WRI$_ADV_OBJECTS_PK rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
SQL> alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;
样例:

再执行清理前：（查询历史记录情况）

SQL> col task_name format a25
SQL> col EXECUTION_NAME format a15
SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';

   TASK_ID TASK_NAME                 EXECUTION_NAME  EXECUTION
---------- ------------------------- --------------- ---------
        11 AUTO_STATS_ADVISOR_TASK   EXEC_1          26-JAN-17
        11 AUTO_STATS_ADVISOR_TASK   EXEC_11         18-NOV-19
        11 AUTO_STATS_ADVISOR_TASK   EXEC_25         19-NOV-19
...
        11 AUTO_STATS_ADVISOR_TASK   EXEC_5590       04-APR-20
...
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6505       14-APR-20
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6535       15-APR-20
手工清理历史数据：

SQL> conn / as sysdba
SQL> exec prvt_advisor.delete_expired_tasks;

PL/SQL procedure successfully completed.
执行手工清理后，可以看到早于10天（EXECUTION_DAYS_TO_EXPIRE）前的记录都被清理掉了：

SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK';

        11 AUTO_STATS_ADVISOR_TASK   EXEC_5796       06-APR-20
        11 AUTO_STATS_ADVISOR_TASK   EXEC_5867       07-APR-20
...
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6505       14-APR-20
        11 AUTO_STATS_ADVISOR_TASK   EXEC_6535       15-APR-20
注意，在 PDB 中 Move WRI$_ADV_OBJECTS 时，可能会遇到以下错误：

SQL> alter table WRI$_ADV_OBJECTS move;
alter table WRI$_ADV_OBJECTS move
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

所以，需要使用 DBMS_PDB.EXEC_AS_ORACLE_SCRIPT 包来执行此操作：

SQL> exec dbms_pdb.exec_as_oracle_script('alter table WRI$_ADV_OBJECTS move parallel 2');
PL/SQL procedure successfully completed.