1 、故障描述

某用户某次SYSAUX表空间占用过大，为避免空间被打爆影响业务运行，需要定位具体哪些对象占用，以尽快使得SYSAUX表空间使用率处于合理区间。

查看v$sysaux_occupants确认占用空间对象

select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;

查询结果：

SM/ADVISOR   32741952

通过dba_segments进一步确认

select segment_name,owner,tablespace_name,bytes/1024/1024"SIZE(MB)",segment_type from dba_segments where tablespace_name='SYSAUX' order by bytes desc;

查看结果：

WRI$_ADV_OBJECTS          SYS   SYSAUX   14497   TABLE

WRI$_ADV_OBJECTS_IDX_01   SYS   SYSAUX    6907     INDEX

WRI$_ADV_OBJECTS_IDX_02   SYS   SYSAUX    5761     INDEX

WRI$_ADV_OBJECTS_PK        SYS   SYSAUX    4804     INDEX

2 、排查处理

因为在12.2中，引入了新的特性：optimizer statistics advisor。优化器统计信息顾问每天都会在维护窗口运行，auto_stats_advisor_task多次运行，因而会消耗大量sysaux表空间。

解决方案：

1 、删除statistics advisor task(auto_stats_advisor_task)，删除该任务后就可以释放统计信息产生的数据

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 TaskName For thecurrent user

如果遇到上面的错误，可以先重建AUTO_STATS_ADVISOR_TASK来解决问题：

SQL>connect as sysdba

SQL>EXEC DBMS_STATS.INIT_PACKAGE();

删除任务后，重新组织表和索引

alter table wri$_adv_objects move;

alter index wri$_adv_objects_idx_01 rebuild;

alter index wri$_adv_objects_idx_02 rebuild;

alter index wri$_adv_objects_pk rebuild;

如果 WRI$_ADV_OBJECTS 记录过多，delete以上会占用较大undo,可以把想要的数据存储在临时表，truncate table WRI$_ADV_OBJECTS，再insert回来。

create table wri$_adv_objects_new tablespace ticket_data

as select * from wri$_adv_objects

where task_id !=(select distinct id 

from wri$_adv_tasks

where name='AUTO_STATS_ADVISOR_TASK');

SQL> select count(*)from wri$_adv_objects_new;

   COUNT(*)

----------

      2968

insert /*+ APPEND */ into wri$_adv_objects

select * from wri$_adv_objects_new;

truncate 后查看空间占用：

select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB

from V$SYSAUX_OCCUPANTS

order by SPACE_USAGE_KBYTES DESC;

SM/ADVISOR   Server Manageability - Advisor Framework   6.1875

2 、缩短任务执行历史的保存时间

确认当前设定的保持期间（30天或UNLIMITED）

修改设定的保持期间

可以通过下面的命令，将EXECUTION_DAYS_TO_EXPIRE修改为15天，即执行历史数据的保存时间为7天：

connect as sysdba

BEGIN

  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (

   task_name => 'AUTO_STATS_ADVISOR_TASK'

  , parameter => 'EXECUTION_DAYS_TO_EXPIRE'

  , value => 15

);

END;

/

3 、也可以禁用该任务，而不是删除。

declare

  filter1 clob;

begin

  filter1 := dbms_stats.configure_advisor_rule_filter('AUTO_STATS_ADVISOR_TASK',

                                                      'EXECUTE',

                                                      NULL,

                                                      'DISABLE');

END;

/