SYSAUX 表空间基于SM/OPTSTAT组件爆满—解决方案汇总

背景

  经历了一次“Oracle SYSAUX表空间异常爆满”，当时查资料几种解决思路，在此做个汇总方便后续查看。不严谨的地方欢迎大家指正

问题介绍

• sysaux表空间中占用空间的组件

set line 800
col OCCUPANT_NAME for a30
col OCCUPANT_DESC for a60
select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB
from V$SYSAUX_OCCUPANTS order by SPACE_USAGE_KBYTES desc;
OCCUPANT_NAME                  OCCUPANT_DESC                                                  USAGE_MB
------------------------------ ------------------------------------------------------------ ----------
SM/OPTSTAT -->优化器统计信息   Server Manageability - Optimizer Statistics History            23922.25

• SYSAUX 表空间

表空间名                       表空间大小(M) 已使用空间(M) 使用比   空闲空间(M)  最大块(M)
------------------------------ ------------- ------------- -------- ----------- ----------
SYSAUX                                 32720      32673.31   99.86%       46.69    

由此可以看到SM/OPTSTAT组件占比 SYSAUX 空间：73%

SM/OPTSTAT简介

SM/OPTSTAT是用于存储老的统计信息。
（1）10G之前，当对表/字段/索引做了相应的统计信息之后，新的统计信息就会覆盖老的统计信息，也就是说的无法直接找回统计信息，要找回，只能事先通过dbms_stats导出来。
（2）10G之后，就不必了，它会自动的存到相应的表里，而这些表是存在sysaux的，但这也引出了一个问题：如果这些表的数据不断的增长，而不把老的数据删除的话，sysaux迟早会被撑爆。
默认的情况下,系统会为SM/OPTSTAT保留31天的记录，可以通过dbms_stats.get_stats_history_retention 来确定。

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SM/OPTSTAT保留的时间可以通过dbms_stats.alter_stats_history_retention来控制。

-- 将历史统计信息保留时间设为无限:
SQL> exec dbms_stats.alter_stats_history_retention(-1);
-- 将历史统计信息保留时间设为7天
SQL> exec dbms_stats.alter_stats_history_retention(7);
-- 查看历史统计信息保留时间
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
04-NOV-22 10.00.07.354209000 PM +08:00

如果SM/OPTSTAT确实占用了比较多的空间，要删除某个时间前的记录可以用：dbms_stats.purge_stats

-- 清理3天前的历史统计信息
SQL>  exec dbms_stats.purge_stats(sysdate-3);
PL/SQL procedure successfully completed.

这个SP其实只从存储历史统计信息的表里删除记录，这样的话，就会出现一种情况，删除了大量的数据，但这些表占用的空间并没有释放,也就是HWM不会降下来的。这时要手工处理

处理方案

方案一：来自(文档 ID 1055547.1)的方案：

• 涉及的表
  
• 原因
  
• 解决
  
  

方案二：直接truncate 表

• 1、将历史统计信息保留时间设为无限:

exec dbms_stats.alter_stats_history_retention(-1);

• 2、truncate TABLE:

truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY; 
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;

• 3、清理历史统计信息(清理7天前的信息)

exec dbms_stats.purge_stats(sysdate-7); 

• 4、将历史统计信息保留时间设为7天（默认31天）

exec dbms_stats.alter_stats_history_retention(7);

• 5、避免有其它问题，执行一次数据库的收集任务：

SQL> exec dbms_stats.gather_database_stats_job_proc();
PL/SQL procedure successfully completed.

方案三：move table 达到清理碎片的目地

• 将表move 到users表空间

SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace users;
-- LOB 字段字段需要单独move 
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace users LOB (expression) STORE AS lobsegment (TABLESPACE users);
SQL> alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  rebuild online;
SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online;

• 重新将表move 回原空间：SYSAUX

SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX;
SQL> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace SYSAUX LOB (expression) STORE AS lobsegment (TABLESPACE SYSAUX);
SQL> alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  rebuild online;
SQL> alter index I_WRI$_OPTSTAT_HH_ST rebuild online;

方案四：重创建表

• 根据create table as 创建一张指定范围内的表备做备份，truncate 表后再重新导入

SQL> CREATE TABLE WRI$_OPTSTAT_HISTGRM_HISTORY_B AS SELECT * FROM WRI$_OPTSTAT_HISTGRM_HISTORY where to_char(savtime,'yyyy-mm-dd')>'2022-11-09';
SQL> TRUNCATE TABLE  WRI$_OPTSTAT_HISTGRM_HISTORY ;
SQL> INSERT INTO WRI$_OPTSTAT_HISTGRM_HISTORY  SELECT * FROM  WRI$_OPTSTAT_HISTGRM_HISTORY_B;
SQL> COMMIT;
SQL> drop table WRI$_OPTSTAT_HISTGRM_HISTORY_B purge;

总结：

• 不一定是全的，但应该有一款适合你！