SYSAUX表空间WRI$\_ADV\_OBJECTS表过大清理V2

# SYSAUX表空间WRI$\_ADV\_OBJECTS表过大清理V2

1. #问题分析 
2. 数据库版本SQL*Plus: Release 12.2.0.1.0 Production，SYSAUX表空间使用率达到100%。
	1. 查询V$SYSAUX\_OCCUPANTS,显示SM/ADVISOR的SPACE\_USAGE\_KBYTES大小17G左右；
		1. SELECT OCCUPANT\_NAME,SPACE\_USAGE\_KBYTES FROM V$SYSAUX\_OCCUPANTS ORDER BY SPACE\_USAGE\_KBYTES DESC;

![](/media/202403//1711442165.396581.png)

* 
	1. 进一步查询DBA\_SEGMENTS视图展示WRI$\_ADV\_OBJECTS消费的SYSAUX表空间
		1. select segment\_name,owner,tablespace\_name,bytes/1024/1024 "SIZE(MB)",segment\_type from dba\_segments where tablespace\_name ='SYSAUX' order by bytes desc;

![](/media/202403//1711442165.4158459.png)

* 
	1. Optimizer Statistics Advisor,12.2之后新增功能，每天的运维窗口期都会运行。AUTO\_STATS\_ADVISOR\_TASK长时间多次运行SYSAUX空间大量使用至耗尽（共计52365834次）
		1. select task\_name,count(*) CNT from dba\_advisor\_objects group by task\_name order by cnt desc;

![](/media/202403//1711442165.4482324.png)

1. 综上，ORACLE 12.2版本的数据库有大量Statistics Advisor Task作业执行耗费大量SYSAUX表空间。
2. #解决方案 
3. 1. 清理历史Statistics Advisor 记录
	1. EXECUTION\_DAYS\_TO\_EXPIRE参数值设置为31天（default unlimited），可以适当调整
		1. select TASK\_NAME,PARAMETER\_NAME,PARAMETER\_VALUE from dba\_advisor\_parameters where task\_name ='AUTO\_STATS\_ADVISOR\_TASK' AND PARAMETER\_NAME ='EXECUTION\_DAYS\_TO\_EXPIRE';

![](/media/202403//1711442165.462166.png)

* 
	1. 三个选项可以实现历史数据清理
		1. 选项-自动清理：将任务AUTO\_STATS\_ADVISOR\_TASK参数EXECUTION\_DAYS\_TO\_EXPIRE设置为10.超过10天的记录将标记为过期，在自动清理窗口自动清理作业将删除过期记录（如果记录较多，可能会分为多次进行清理）。
		2. 选项-手动清理：SQL>exec prvt\_advisor.delete\_expired\_tasks;-----手动清理过期记录
		3. 选项-自定义：
			1. SQL> exec dbms\_advisor.set\_task\_parameter(task\_name => 'AUTO\_STATS\_ADVISOR\_TASK',parameter => 'EXECUTION\_DAYS\_TO\_EXPIRE',value => 10);
			2. SQL> exec dbms\_sqltune.set\_tuning\_task\_parameter(task\_name => 'AUTO\_STATS\_ADVISOR\_TASK',parameter => 'EXECUTION\_DAYS\_TO\_EXPIRE',value => 10);
		4. 本实例：自定义->手动清理
	2. 清理WRI$\_ADV\_OBJECTS完成后，重建三个索引
		1. alter table WRI$\_ADV\_OBJECTS move;
		2. alter index WRI$\_ADV\_OBJECTS\_PK rebuild;
		3. alter index WRI$\_ADV\_OBJECTS\_IDX\_01 rebuild;
		4. alter index WRI$\_ADV\_OBJECTS\_IDX\_02 rebuild;#19c
	3. 查询 Statistics Advisor Records
		1. select task\_id,task\_name,execution\_name,execution\_start from dba\_advisor\_executions where task\_name ='AUTO\_STATS\_ADVISOR\_TASK';

![](/media/202403//1711442165.5038276.png)

1. 2. Statistics Advisor Task(AUTO\_STATS\_OBJECTS)能被删除
	1. 执行以下命令drop掉AUTO\_STATS\_OBJECTS任务，WRI$\_ADV\_OBJECTS表中该任务相关的数据将被删除
		1. DECLARE v\_tname varchar2(32767);
		2. BEGIN
		3.  v\_tname :='AUTO\_STATS\_ADVISOR\_TASK';
		4.  DBMS\_STATS.DROP\_ADVISOR\_TASK(v\_tname);
		5.  END;
		6.  /
	2. 重新组织表WRI$\_ADV\_OBJECTS及其索引
		1. alter table WRI$\_ADV\_OBJECTS move;
		2. alter index WRI$\_ADV\_OBJECTS\_PK rebuild;
		3. alter index WRI$\_ADV\_OBJECTS\_IDX\_01 rebuild;
		4. alter index WRI$\_ADV\_OBJECTS\_IDX\_02 rebuild; #19c
	3. 确保所有索引是有效的
		1. select index\_name,status from dba\_indexes where table\_name ='WRI$\_ADV\_OBJECTS' and index\_type like '%NORMAL';

![](/media/202403//1711442165.5202293.png)

* 
	1. 如果报错ORA-20001,重建AUTO\_STATS\_ADVISOR\_TASK
		1. EXEC DBMS\_STATS.INIT\_PACKAGE();