oracle sysaux表空间异常爆满—ora-m6米乐安卓版下载

布衣 2022-12-02

653

背景

晚上突然收到数据库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