环境说明:
DB:Oracle 12.2.0.1.0
问题现象:
1.WRI$_ADV_OBJECTS表数据量较大,需要清理。
2.参考2686022.1文档,尝试永久关闭AUTO_STATS_ADVISOR_TASK ,执行:
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
报错:
ORA-20001:Invalid input values for pname
这是一个BUG,Bug 26749785,具体BUG描述参考:Doc ID 26749785.8
问题原因:
从12.2版本开始,Oracle推出了优化器统计顾问(Optimizer Statistics Advisor),对应TASK是AUTO_STATS_ADVISOR_TASK,他会根据事先定义的规则,定期执行,提供给用户参考的统计信息收集相关的建议。
但此功能,未见其利,先见其害,需要人为评估并干预WRI$_ADV_OBJECTS表数据增长情况。
解决方案:
临时解决方案,参考 2305512.1 文档,定期清理WRI$_ADV_OBJECTS表数据。
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
也可以打下面的补丁,然后永久关闭AUTO_STATS_ADVISOR_TASK,此方法我没测试过,谨慎使用,仅供参考。
https://updates.oracle.com/download/26749785.html
详细过程如下:
检查表空间使用率
###check tbs used%###
TABLESPACE_NAME USED_PRC FREE_MB
----------------------------------- ---------- ----------
SYSAUX 75.21 7615.8125
......
TABLESPACE_NAME FILE_MB AUT MAX_MB
-------------------- ---------- --- ----------
SYSAUX 24240 YES 30720
......
检查SYSAUX表空间使用情况
COL OCCUPANT_NAME FORMAT A30;
SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM