Optimizer Statistics Advisor是Oracle12.2中新增的功能, 而AUTO_STATS_ADVISOR_TASK任务的自动
运行会占用大量sysaux表空间,从而导致sysaux表空间不足,严重情况下导致数据库无法正常使用。
这个问题比较常见,在此提醒大家注意。
◼ 问题描述
这种情况存在于Oracle数据库版本12.2.0.1到18.3.0.0.0,由于Optimizer Statistics Advisor新特性的引
入,AUTO_STATS_ADVISOR_TASK自动任务的执行,导致SYSAUX表空间使用逐渐增长,主要表象为:
1)查看v$sysaux_occupants,SM/ADVISOR的空间使用排在第一位。
2)查看dba_segments,发现SYSAUX表空间中WRI$_ADV_OBJECTS占用最大。
SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS WHERE task_name='AUTO_STATS_ADVISOR_TASK'and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';
◼ 问题解决
针对这种情况,建议首先通过以下查询,获得AUTO_STATS_ADVISOR_TASK任务数据的保留时间,
缺省的保留时间是没有限制的:
SELECT parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='AUTO_STATS_ADVISOR_TASK'
and PARAMETER_NAME IN 'EXECUTION_DAYS_TO_EXPIRE';
可以通过修改保留时间,限制保留为 31 天:
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 31
);
END;
/