SYSAUX表空间中WRI$_OPTSTAT_HISTGRM_HISTORY等历史统计信息表较大问题

关注

原创最新推荐文章于 2026-08-03 09:55:38 发布 6.7k 阅读 AI 写同款· GEO 优化 ›

SYSAUX表空间清理之SM/OPTSTAT sysaux是system的辅助表空间，主要存放AWR基表和审计信息，一般情况下sysaux的使用率都是正常的，看到sysaux表空间使用过高就有点怀疑了，首先查看下表空间。 SQL> select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024/1024 GB from dba_segments where tablespace_name='SYSAUX' group by owner,segment_n 阅读详情

之前有排查过一次SYSAUX表空间增长过快方面的问题，今天客户反应另外一个库的SYSAUX表空间也是增长过快，于是直接用起之前排查的语句，下意识的以为也是awr数据不能自动删除的问题。

结果查看SYSAUX表空间中对象所占空间大小情况时发现，WRI$_OPTSTAT_HISTGRM_HISTORY和WRI$_OPTSTAT_HISTHEAD_HISTORY两张表以及相关索引占据了SYSAUX表空间大量空间。

在metalink上搜索了下WRI$_OPTSTAT_HISTGRM_HISTORY，有个相关文档Doc ID 1055547.1

大致内容如下：

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

SYMPTOMS

Space consumption continuously increases in the following tables:

wri$_optstat_tab_history
wri$_optstat_ind_history
wri$_optstat_histhead_history
wri$_optstat_histgrm_history

CAUSE

By default the MMON performs the automatic purge that removes all history older than the older of:

  current time - statistics history retention

and

  time of recent analyze in the system - 1

Default for Statistics History Retention is 31 days

MMON performs the purge of the optimizer stats history automatically. However it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged.
No trace or alert message is reported.

意思是oracle默认保留31天的历史统计信息数据，但会出现一种情况，到oracle自动删除超过保留期限的数据时，如果删除操作超过5分钟，那么该删除操作将会自动停止，继而历史数据没有删除成功，便一直积累，导致历史统计信息相关表一直增长

解决方法：

1. Apply patch for unpublished Bug 14373728.
This patch supersedes fix for Bug 11869207.

This fix makes deep changes to purge historic (archived) schema statistics. As it makes changes to the data dictionary, Support needs to be involved in a situation where a patch is requested.
The fix is included in 11.2.0.4 and 12.1

When compatibility in 11.2 is above 11 , the patch will be enabled when the patchset is installed. However it is not enabled when compatibility is less than 11.
For further information see:

How to Manually Enable the Patch for Bug 14373728 on Oracle 11g Release 11.2.0.4 [ID 1537496.1]

2. If the previous patch cannot be applied, it is recommended that the following patches are applied:

Bug 8553944 SYSAUX tablespace grows
Included in 12.1 and 11.2.0.2
This fix alters the purging to run in chunks rather than trying to do all in one go.
and
Bug 10279045 Slow Statistics purging (SYSAUX grows)
Fixed in 12.1 and 11.2.0.3

Check for existing interim patches on top of older patchsets: Patch 10279045

也就是告诉我们打补丁了。。。。

本以为事情到此为止，结果发现WRI$_OPTSTAT_HISTGRM_HISTORY等表里面保留的数据都是31天内的，并没有踩中该bug。

只好继续探究，发现oracle中对于那些自动删除保留期限外的数据，都是采用delete的方式，而delete方式很容易导致空间碎片的情况，所以怀疑WRI$_OPTSTAT_HISTGRM_HISTORY等表的空间碎片可能较为严重了，建议进行shrink一下。

范例：

alter table WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;

alter table WRI$_OPTSTAT_HISTGRM_HISTORY shrink space cascade ;

结果

报：ORA-10631: SHRINK clause should not be specified for this object

原来WRI$_OPTSTAT_HISTGRM_HISTORY上有函数索引，导致shrink不可用。只好采用move的方式。

alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;

然后将失效索引rebuild。