oracle rdbms占内存,SYSAUX表空间占用过大如何处理？

SYSAUX表空间在默认条件下如果不做任何配置，那么随着时间的推移，会越来越大。所以，如果SYSAUX表空间过大，那么应该及时诊断清理该表空间。

对于SYSAUX表空间而言，如果占用过大，那么一般情况下是由于AWR信息或对象统计信息没有及时清理引起的，具体原因可以通过如下的SQL语句查询：

SELECT OCCUPANT_NAME "Item",

SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",

SCHEMA_NAME "Schema",

MOVE_PROCEDURE "Move Procedure"

FROM V$SYSAUX_OCCUPANTS

WHERE SPACE_USAGE_KBYTES > 1048576

ORDER BY "Space Used (GB)" DESC;

如果OCCUPANT_NAME列为SM/AWR(Server Manageability - Automatic Workload Repository)，那么表示AWR信息占用过大；如果该列为SM/OPTSTAT(ServerManageability - Optimizer Statistics History)，那么表示优化器统计信息占用过大。

也可以直接查询DBA_SEGMENTS视图获取信息：

SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024  SIZE_M

FROM DBA_SEGMENTS D

WHERE D.TABLESPACE_NAME = 'SYSAUX'

GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE

ORDER BY SIZE_M DESC;

然后查询占用空间较大的表，即可得到占用空间较大的原因，下面分别讨论。

(一)AWR信息占用过大

如果确认是AWR信息占用空间过大，那么还可以使用如下的SQL脚本获取AWR占用信息的详细信息：

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrinfo.sql

如果AWR信息占用过大，那么可以通过设置AWR的保留时间来减小AWR信息的存储空间。通过如下的SQL语句可以获取AWR的保留时间：

SELECT * FROM DBA_HIST_WR_CONTROL;

通过如下的SQL语句可以设置AWR信息的保留时间为7天(7*24*60)，每隔1小时收集一次AWR信息：

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60,RETENTION=>7*24*60);

需要注意的是，在Oracle 10g中，AWR默认保留7天，在Oracle 11g中，AWR默认保留8天。

在以上设置完成后，可以删除不需要的AWR快照信息，从而释放SYSAUX表空间，相关SQL语句如下所示：

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_ACTIVE_SESS_HISTORY;

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

LOW_SNAP_ID => 1,

HIGH_SNAP_ID => 36768,

DBID=> 1148453265);

END;

如果DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE执行太慢，那么可以先执行TRUNCATE操作：

select distinct 'truncate  table '||segment_name||';',s.bytes/1024/1024

from dba_segments s

where s.segment_name like 'WRH$%'

and segment_type in ('TABLE PARTITION', 'TABLE')

and s.bytes/1024/1024>100

order by s.bytes/1024/1024/1024 desc;

执行完TRUNCATE操作后，再执行DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE。需要注意的是，以上TRUNCATE操作会将AWR中的所有信息全部清除。所以，需要先确认释放需要这些AWR信息，当然也可以先把需要的AWR信息做导出操作，然后再清空以上AWR信息。

需要注意的是，DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE是通过DELETE操作来完全清理工作的。所以，执行完成后，并不会真正的释放SYSAUX表空间。此时，应该对相关的表执行MOVE或TRUNCATE操作。在执行MOVE操作时，由于AWR信息的表都是分区表，不能对分区表全表执行MOVE操作，所以需要单独对分区执行MOVE操作，例如：

ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY MOVE PARTITION 分区名称;

执行完MOVE操作后，需要对索引进行重建。同理，对于分区索引，只能对分区的单个索引进行重建，而不能总体重建：

ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION 分区名称;

需要注意的是，可以在以上SQL后加上“UPDATE GLOBAL INDEXES”子句让全局索引不失效。

(二)统计信息占用过大

如果统计信息占用空间过大，那么可以修改统计信息的保留时间。统计信息默认保留31天，过期的统计信息会自动被删除。

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;  --查询统计信息的保留时间

EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);  --设置统计信息的保留时间

若发现统计信息占用了SYSAUX上的大量空间，则可以考虑使用DBMS_STATS.PURGE_STATS过程实施清理。

以下的SQL语句对于诊断SYSAUX表空间的占用情况非常有用：

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;

以下SQL可以查询到无效的ASH信息：

SELECT COUNT(*)

FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A

WHERE NOT EXISTS (SELECT 1

FROM SYS.WRM$_SNAPSHOT B

WHERE A.SNAP_ID = B.SNAP_ID

AND A.DBID = B.DBID

AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);

最后需要说明的一点是，负责收集和清理AWR信息的后台进程为MMON，而隐含参数“_swrf_test_action”可以调试MMON的行为，可以和10046事件结合使用。MMON进程每分钟都会自动刷新一定的AWR数据到磁盘上，默认情况下，MMON每30分钟做一次AWR信息的清理工作。在trace文件中可以看到“MMON Auto-Purge cycle”字样。