近期一客户19c RAC CDB数据库的SYSAUX表空间增长超大，分析原因为Optimizer statistics advisor特性导致的WRI$_ADV_OBJECTS对象记录数变多， 以下为清理方法。
1, 找出最大对象
SQL> set lines 120
SQL> col occupant_name format a30
SQL> select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;
或
prompt
prompt List of TOP 10 largest objects in SYSTEM AND SYSAUX TABLESPACE:
prompt
select * from (
select tablespace_name,topseg_seg_owner,topseg_segment_name,segment_type,mb,partitions, row_number() over(partition by tablespace_name order by mb desc) rn from (
select
tablespace_name,
owner topseg_seg_owner,
segment_name topseg_segment_name,
--partition_name,
segment_type,
round(SUM(bytes/1048576)) MB,
case when count(*) >= 1 then count(*) else null end partitions
from dba_segments
where upper(tablespace_name) in ('SYSTEM','SYSAUX') -- tablespace name
group by
tablespace_name,
owner,
segment_name,
segment_type ))
where rn<=10;
这个客户发现是Sm/advisor和最大对象为Wri$_adv_objects ，因为在12.2中引入了一个新功能：优化器统计顾问。 优化器统计信息顾问每天在“维护”窗口中运行，多次auto_stats_advisor_task，并占用大量sysaux表空间。
2，统计记录数
SQL> col task_name format a35
SQL> select task_name, count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;
3, 手动清理 如WRI$_ADV_OBJECTS
-- 删除Statistics Advisor 任务
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
Note:
1, 如果遇到错误：Ora-20001:statistics advisor:invalid Task Name for the current user
执行
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
2, 如果 WRI$_ADV_OBJECTS 记录过多，delete以上会占用较大undo,可以把想要的数据存储在临时表，truncate table WRI$_ADV_OBJECTS,再insert回来。
–删除任务之后，重组表和所有索引
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
SQL> ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
3， 如果在CDB中，以上操作没有任何问题，但是在PDB中MOVE表可能会有如下ORA-65040错误
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER TABLE WRI$_ADV_OBJECTS MOVE
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> ho oerr ora 65040
65040, 00000, "operation not allowed from within a pluggable database"
// *Cause: An operation was attempted that can only be performed in the root
// or application root container.
// *Action: Switch to the root or application root container to perform the
// operation.
//
解决方法有2种：
1，_oracle_scripts参数
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> ALTER TABLE WRI$_ADV_OBJECTS MOVE;
Table altered.
2，dbms_pdb.exec_as_oracle_script
SQL> exec dbms_pdb.exec_as_oracle_script('alter table sys.WRI$_ADV_OBJECTS move');
PL/SQL procedure successfully completed.
4. 为了减少advisor存储，可以减少保留期限
-确认当前设定的保持期间
select task_name, parameter_name, parameter_value FROM DBA_ADVISOR_PARAMETERS
where task_name='AUTO_STATS_ADVISOR_TASK' and PARAMETER_NAME like '%EXPIRE%';
-- 修改设定的保持期间,如历史数据的保存时间为15天：
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 15
);
END;
/
Note:
但是需要注意12.2中的Bug 26764561 AUTO_STATS_ADVISOR_TASK Not Purging Even Though Setting EXECUTION_DAYS_TO_EXPIRE (Doc ID 2615851.1)，
该配置在CDB和不同PDB中相互独立。
5，禁用AUTO_STATS_ADVISOR_TASK
如果觉的这ADVISOR实在没用，可以考虑禁用，但是12c-20c默认需要先安装一bug 26749785 patch增加AUTO_STATS_ADVISOR_TASK控制,注意这不是bug，只是增强功能。在安装该patch前（或21.1版本前），无法使用以下功能。
SQL> exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
SQL> select dbms_stats.get_prefs('AUTO_STATS_ADVISOR_TASK') from dual;
DBMS_STATS.GET_PREFS('AUTO_STATS_ADVISOR_TASK')
--------------------------------------------------------------------------------
FALSE
或者使用
declare
filter1 clob;
begin
filter1 := dbms_stats.configure_advisor_rule_filter('AUTO_STATS_ADVISOR_TASK',
'EXECUTE',
NULL,
'DISABLE');
END;
/
References SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
