AUTO_STATS_ADVISOR_TASK任务导致sysaux表空间异常增长

每周巡检数据库，发现sysaux表空间使用量增长迅速，数据文件都resize扩了好几次了，不得不查下原因。

查看sysaux表空间分布：

select occupant_name "Item",space_usage_kbytes /1024/1024 "Space Used(GB)",schema_name "Schema",move_procedure "Move Procedure" from v$sysaux_occupants order by 2 desc;

查看前10位大表：

SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10;

查看是什么任务：

select task_name,count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;

查看过期数据删除时间：

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';

可以用下面语句验证一下：

select task_id,task_name,execution_name,execution_start from dba_advisor_executions where TASK_NAME='AUTO_STATS_ADVISOR_TASK' order by execution_start;

考虑重建该任务，或者减少保留时间。

最先选择的是减少保留时间，比如20天。

EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(task_name=>'AUTO_STATS_ADVISOR_TASK',parameter=>'EXECUTION_DAYS_TO_EXPIRE',value=>20);

结果第二天发现不但没生效，30天前的也不删了。奇怪！

于是查到这么一篇文章：

This is because of bug:Bug 26764561(Doc ID 26764561.8).After setting EXECUTION_DAYS_TO_EXPIRE to non-default value,MMON worker tries to delete the old data, but because of the bug recursive delete statements get ORA-933 or ORA-1722 and the delete transaction is rolled back.（AUTO_STATS_ADVISOR_TASK Not Purging Even Though Setting EXECUTION_DAYS_TO_EXPIRE (Doc ID 2615851.1)）

原来是碰上bug了。

那没办法，只能重建任务了。

1.删除

DECLARE

v_tname VARCHAR2(32767);

BEGIN

v_tname := 'AUTO_STATS_ADVISOR_TASK';

DBMS_STATS.DROP_ADVISOR_TASK(v_tname);

END;

/

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;

SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD; /* this index is included from 19c and above */

3.重建

EXEC DBMS_STATS.INIT_PACKAGE();

后来发现还是增长迅速，做了下过滤大字段操作：

也是在观察中。

对了，数据库版本是Oracle 19.13。