Oracle 19.7关于SYSAUX表空间迅速增长问题处理

---

前言:一定要都看完，尤其关于CDB部分

在日常运维中，遇到了应用报错无法正常使用，经过alert日志发现是由于sysaux表空间空间不够导致，且增长速度大过自动扩容速度，导致此问题，立即进行扩容，然后排查问题，主要原因如下2个：

1. 统计信息保留信息太多
2. 优化器统计任务（12.2版本新功能）

详细如下：
Oracle 19C 本身的SYSAUX表空间就很大，如果启用更多的特性功能并伴随着繁忙的业务，SYSAUX表空间就会出乎我们的意料。

---

提示：以下是本篇文章正文内容，下面文章可供参考

一、设置统计信息保留时长

1.统计信息大表查询

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

2.备份/删除统计表信息

truncate table WRI$_SQLSET_PLAN_LINES;

3.修改统计信息保留时长

查询：

select dbms_stats.get_stats_history_retention from dual;

修改

exec dbms_stats.alter_stats_history_retention(8);

二、禁用优化器统计任务

此任务是12.2的新功能
cdb和pdb都需处理：使用sysdba权限连接。

1 统计信息大表查询

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

2 清掉1中查到的大表数据：

TRUNCATE TABLE WRI$_ADV_OBJECTS;

3.1 禁用Optimizer Statistics Advisor Task

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

3.2 删除Statistics Advisor 任务

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

如果删除任务执行成功，结束。
如果删除任务失败，遇到错误：Ora-20001:statistics advisor:invalid Task Name for the current user
执行 EXEC DBMS_STATS.INIT_PACKAGE();后再执行删除任务即可。

3.3自动统计任务到期天数修改

BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 8
);
END;
/

SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='AUTO_STATS_ADVISOR_TASK'
and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';

4 检查表空间占用

三、CDB下的SYSAUX持续增长

经过以上的调整后，又经过几周发现在CDB下并未达到预期效果，每周以20G速度增长，19.7默认启用了一个Auto STS Captrue Task 任务，但19.8版本又被默认为Disable。

查询Auto STS Captrue Task状态

禁用/启用Auto STS Captrue Task