WRI$_ADV_OBJECTS表过大,导致PDB的SYSAUX表空间不足

DBA小记 2021-08-06

2060

监控发现sysaux表空间使用不断增加，导致表空间不足。

数据库版本：

select * from v$version;
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

查看v$sysaux_occupants，发现SM/ADVISOR排在第一

select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;
SM/ADVISOR   32741952

看dba_segments，发现WRI$_ADV_OBJECTS占用最大

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

原因：

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

select task_name,count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;
 
TASK_NAME                                  CNT
----------------------------------- ----------
AUTO_STATS_ADVISOR_TASK   127918465
SYS_AUTO_SPCADV205002227072021   50

解决方案：

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）

 select task_name, parameter_name, parameter_value 
 FROM DBA_ADVISOR_PARAMETERS
 where task_name='AUTO_STATS_ADVISOR_TASK' 
 and PARAMETER_NAME like '%EXPIRE%';

修改设定的保持期间

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

 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;
/

数据库

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

WRI$_ADV_OBJECTS表过大,导致PDB的SYSAUX表空间不足

评论