Oracle 12C WRI$_ADV_OBJECTS表清理

原创已于 2024-11-17 12:24:18 修改 · 564 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2024-11-17 12:12:28 首次发布

环境说明:

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<=10;

OCCUPANT_NAME GB
------------------------------ ----------
SM/ADVISOR 19.5944824
SM/OPTSTAT 1.44366455
SM/AWR .874633789
......

检查段大小

SET PAGESIZE 100
SET LINE 300
COL segment_name FOR A35
COL segment_type FOR A25
COL OWNER FOR A15
select * from (
select owner,
segment_name,
segment_type,
sum(bytes)  1024  1024  1024 GB
from dba_segments
where TABLESPACE_NAME in ('SYSAUX') 
group by owner, segment_name, segment_type
order by 4 desc ) where rownum <20;

OWNER SEGMENT_NAME SEGMENT_TYPE GB
--------------- ----------------------------------- ------------------------- ----------
SYS WRI$_ADV_OBJECTS TABLE 17.3955078
SYS WRI$_ADV_OBJECTS_IDX_01 INDEX 1.31933594
SYS WRI$_ADV_OBJECTS_PK INDEX .826171875
......

检查表大小

SELECT count(*) FROM WRI$_ADV_OBJECTS; ---23184654
SELECT count(*) FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK'); ---1412

检查AUTO_STATS_ADVISOR_TASK
默认是unlimited，之前已经改成5天了，但实际上并没有自动清理。

col parameter_name for a30
col parameter_value for a20
select parameter_name, parameter_value from dba_advisor_parameters where task_name='AUTO_STATS_ADVISOR_TASK' and parameter_name='EXECUTION_DAYS_TO_EXPIRE';
PARAMETER_NAME PARAMETER_VALUE
------------------------------ --------------------
EXECUTION_DAYS_TO_EXPIRE 5

没有自动清理

select max(execution_start),min(execution_start) from dba_advisor_executions where task_name='AUTO_STATS_ADVISOR_TASK';
MAX(EXECUTION_S MIN(EXECUTION_S
--------------- ---------------
26-MAY-24 27-MAR-19

清理
注意：如果担心有问题，可以先做一个全表备份。

CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');

SELECT COUNT(*) FROM WRI$_ADV_OBJECTS_NEW; ---1412

TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT /*+ APPEND */ INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;

---1412 rows created.
COMMIT;

ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD online;

ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD online;

drop table WRI$_ADV_OBJECTS_NEW;

参考：

How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)

SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)

Bug 26749785 - Enhancement to have more controls on auto Statistics Advisor (Doc ID 26749785.8)

###chenjuchao 20240602###
欢迎关注我的公众号《IT小Chen》