Oracle SYSAUX表空间异常爆满—ORA-1653

背景

  晚上突然收到数据库ORA日志报警：ORA-1653: unable to extend table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY by 128 in tablespace SYSAUX 这个报错需要关注一下。SYSAUX 数据库的系统表空间，平时都是数据库自己维护的一般不需要单独注意，怎么就空间空间爆满了呢？

处理过程：

1、首先还是去查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
SM/AWR   -->AWR信息            Server Manageability - Automatic Workload Repository          8165.0625
XDB                            XDB                                                            126.9375
SDO                            Oracle Spatial                                                    74.25
SM/ADVISOR                     Server Manageability - Advisor Framework                             66
JOB_SCHEDULER                  Unified Job Scheduler                                           50.1875
EM                             Enterprise Manager Repository                                   46.0625
AO                             Analytical Workspace Object Table                               38.1875
-- 与其它库对比：
OCCUPANT_NAME                  OCCUPANT_DESC                                                  USAGE_MB
------------------------------ ------------------------------------------------------------ ----------
SM/AWR                         Server Manageability - Automatic Workload Repository          3042.8125
SM/ADVISOR                     Server Manageability - Advisor Framework                       528.9375
SM/OPTSTAT                     Server Manageability - Optimizer Statistics History              452.75
XDB                            XDB                                                            157.5625
JOB_SCHEDULER                  Unified Job Scheduler                                          102.3125

发现：SM/OPTSTAT组件（优化器统计信息）使用了：24G，其它库才：452.75MB,差距很大

2、查看下表空间：SYSAUX

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

SYSAUX 使用了32GB,由此可以确认SM/OPTSTAT组件（优化器统计信息）空间使用异常导致的。

3、查看占用SYSAUX 表空间前10的对象

SQL>  select * from 
  2  (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB  from dba_segments where tablespace_name='SYSAUX'
  3  group by owner,segment_name,segment_type
  4  order by 4 desc )
  5  where rownum <10; 
OWNER                          SEGMENT_NAME                             SEGMENT_TYPE               GB
------------------------------ ---------------------------------------- ------------------ ----------
SYS                            I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX              8.70898438
SYS                            WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE              8.41699219
SYS                            I_WRI$_OPTSTAT_HH_ST                     INDEX              4.81835938
SYS                            WRH$_LATCH                               TABLE PARTITION    .812561035
SYS                            WRH$_EVENT_HISTOGRAM_PK                  INDEX PARTITION    .664123535
SYS                            WRH$_EVENT_HISTOGRAM                     TABLE PARTITION    .640686035
SYS                            WRH$_SYSSTAT_PK                          INDEX PARTITION    .562561035
SYS                            WRH$_SQLSTAT                             TABLE PARTITION    .546936035
SYS                            WRH$_SYSSTAT                             TABLE PARTITION    .531311035

4、查看WRI$_OPTSTAT_HISTHEAD_HISTORY表的关联索引

SQL> select owner,index_name from dba_indexes where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';

OWNER                          INDEX_NAME
------------------------------ ------------------------------
SYS                            I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
SYS                            SYS_IL0000000494C00016$$
SYS                            I_WRI$_OPTSTAT_HH_ST

5、查看相关联对像大小

SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_MB
 FROM DBA_SEGMENTS D
WHERE D.TABLESPACE_NAME = 'SYSAUX'
GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE 
having D.SEGMENT_NAME in ('WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','SYS_IL0000000494C00016$$','I_WRI$_OPTSTAT_HH_ST');

SEGMENT_NAME                             SEGMENT_TYPE          SIZE_MB
---------------------------------------- ------------------ ----------
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST            INDEX                    8918
SYS_IL0000000494C00016$$                 LOBINDEX                .0625
I_WRI$_OPTSTAT_HH_ST                     INDEX                    4934
WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE                    8619

6、与（23922.25）基本吻合

SQL> select (8918+.0625+4934+8619)/1024 from dual;

(8918+.0625+4934+8619)/1024
---------------------------
                  21.944397

由此确认了占比空间最大的对象：WRI$_OPTSTAT_HISTHEAD_HISTORY

7、分析原因

百度查“SYSAUX表空间清理 WRI$_OPTSTAT_HISTHEAD_HISTORY”，结果都说是:
Bug 12540172 : SYSAUX CONTINUES TO GROW MMON NOT CLEANING UP
SYSAUX Grows Because Optimizer Stats History is Not Purged(文档 ID 1055547.1)
不能什么都扔给BUG，自己往深的挖挖，看看能不能挖出点东西来。

• 1）与另一个生产数据库的数据量做下对比：

SQL> select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
  COUNT(*)
----------
 141033942
-- 另一个库：
SQL>  select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
  COUNT(*)
----------
    151012

正常库记录数才10万+，这个库达到了1.4亿，属实不正常，比对一下每天的量，是不是某一天记录突增：

-- 本库
SQL> select to_char(savtime,'yyyy-mm-dd') as savtime,count(*) from  WRI$_OPTSTAT_HISTHEAD_HISTORY group by to_char(savtime,'yyyy-mm-dd') order by 1;
SAVTIME      COUNT(*)
---------- ----------
2022-11-04	1768420
2022-11-05	21849053
2022-11-06	21311262
2022-11-07	20613680
2022-11-08	20666343
2022-11-09	20078776
2022-11-10	19649162
2022-11-11	15097246
-- 另一个库
SQL> select to_char(savtime,'yyyy-mm-dd') as savtime,count(*) from  WRI$_OPTSTAT_HISTHEAD_HISTORY group by to_char(savtime,'yyyy-mm-dd') order by 1;
SAVTIME      COUNT(*)
---------- ----------
......省略
2022-11-04       4456
2022-11-05      10657
2022-11-06       8967
2022-11-07       2029
2022-11-08       4508
2022-11-09       4200
2022-11-10       4777
2022-11-11       4315
31 rows selected

每天平均：1千万以上的记录。正常情况下也就4千左右。
那么查一下都是哪个对象在频繁刷统计信息：

SQL> select obj#,  count(*)
  from WRI$_OPTSTAT_HISTHEAD_HISTORY
 where to_char(savtime, 'yyyy-mm-dd') = '2022-11-09' -
 group by obj#--, to_char(savtime, 'yyyy-mm-dd hh24:mi') order by 1;
 OBJ#       COUNT(*)
---------- ----------
147988     8000 -- 8000条属实异常
147989	   8000
141836	   8000
484	   7
5304	   5
5325	   12
6243	   7
6661	   3
468	   10
6731	   13

在这里就发现问题了，147988、147989、141836 这几个对象一天刷新了8000次，其它也就10个左右。
查下 OBJ#对应的对象：

select owner,object_name,subobject_name from dba_objects where object_id in ('147988','147989','141836 ');

因为此库为综合库上面放着好多应用，一个应用对应一个用户。于是登到对应用户，查看里面的存储过程，发现有一个存储过程代码中有对其表进行统计：

后与开发人员沟通确认，此存储过程为每天一次执行频率，但在2022-11-04程序执行报错，于是程序又重新调用，调用又报错，于是又调用（死循环了，又是一个程序死循环的坑）。开发表示后期进行优化调整。至此问题分析完成。

8、解决

因此库为生产数据库，但不是核心数据库，所以邮件申请了2个小时的维护窗口。
网上查到有好多的解决方法，后续再进行总结，这里先写我的处理过程。

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

exec dbms_stats.alter_stats_history_retention(-1);

• 2、先对表move:仅释放出：6GB空间

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;

并没有解决多少空间，因为此库但不是核心数据库，直接truncate 得了，此次不建议，一定要根据自己库的实际情况进行风险评估。

• 3、truncate TABLE:

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

• 4、清理历史统计信息

exec dbms_stats.purge_stats(sysdate-3);    --保留3天

• 5、将历史统计信息保留时间设为31天

exec dbms_stats.alter_stats_history_retention(31);

• 6、重新将表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;

• 7、收集’WRI_OPTSTAT_HISTHEAD_HISTORY、'WRI_OPTSTAT_HISTGRM_HISTORY统计信息

SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);
SQL> EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE); 

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

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

• 9、验证空间：释放空间：22GB

表空间名                       表空间大小(M) 已使用空间(M) 使用比   空闲空间(M)  最大块(M)
------------------------------ ------------- ------------- -------- ----------- ----------
SYSAUX                                 32720       9506.69   29.05%    23213.31       1096

自我总结

1. 一定要深挖一下问题，BUG不是所有问题的答案。这次如果不挖一下，程序的一个BUG(死循环的大坑)就发现不了，过一段时间就又会出现SYSAUX爆满的报警。
2. 表空间监控还需要再优化，系统自增表空间监控还有遗漏，有时间改进一下：Oracle 表空间监控脚本优化
3. 做一次SYSAUX 爆满的方案总结，以应对不同的数据库环境。SYSAUX 表空间基于SM/OPTSTAT组件爆满—解决方案汇总