SYSAUX表空间占用过大情况下的处理(AWR信息过多)

发布时间:2020-07-22 21:07:05 作者:小麦苗best
来源:网络 阅读:51099

真题1、  SYSTEMSYSAUX表空间存储的内容有哪些区别?若SYSAUX表空间占用过大则应该如何处理?

答案:在一般情况下,企业产生的业务数据应该存放在单独的数据表空间,而不应该使用系统已存在的表空间,尤其不能将业务数据保存到SYSTEMSYSAUX表空间中,所以,DBA需要着重关注SYSTEMSYSAUX表空间的占用情况。

Oracle服务器使用SYSTEM表空间管理整个数据库。这个表空间包含系统的数据字典和关于数据库的管理信息,这些信息均包含在SYS方案中,只有SYS用户或者拥有所需权限的其它管理用户才可访问这些信息。SYSTEM表空间用于核心功能(例如数据字典表)。

SYSAUXSYSTEM表空间的辅助表空间。Oracle DB早期版本中某些使用SYSTEM表空间或其本身表空间的组件和产品现在改为使用SYSAUX表空间。每个Oracle Database 10g(或更高版本)数据库都必须拥有SYSAUX表空间。辅助表空间SYSAUX用于附加的数据库组件,例如,OEM库(Oracle Enterprise Manager Repository)、AWR快照信息库、统计信息、审计信息等。

SYSTEMSYSAUX表空间是在创建数据库时创建的必需存在的表空间。这些表空间必须联机。在OPEN状态下,SYSAUX表空间可以脱机以执行表空间恢复,而SYSTEM表空间则不能,这两种表空间都不能设置为只读状态。在MOUNT状态下,任何表空间都可以脱机。

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

对于SYSTEM表空间而言,如果占用过大,那么一般情况下是由于审计表(SYS.AUD$)过大引起的。需要将审计表移动到其它表空间中,然后再清理审计表(TRUNCATE TABLE SYS.AUD$)即可。需要注意的是,如果审计表过大,那么应该分部去清理审计表,详细步骤可以参考审计部分。

对于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/AWRServer Manageability - Automatic Workload Repository),那么表示AWR信息占用过大;如果该列为SM/OPTSTATServer Manageability - 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表空间。此时,应该对相关的表执行MOVETRUNCATE操作。在执行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数据到磁盘上,默认情况下,MMON30分钟做一次AWR信息的清理工作。在trace文件中可以看到“MMON Auto-Purge cycle”字样。

& 说明:

有关SYSTEMSYSAUX的更多内容可以参考我的BLOGhttp://blog.itpub.net/26736162/viewspace-2152868/

有关审计的更多内容可以参考我的BLOGhttp://blog.itpub.net/26736162/viewspace-2140644/

有关数据库操作导致索引失效的更多内容可以参考我的BLOGhttp://blog.itpub.net/26736162/viewspace-2152871/

 

 

 



推荐阅读:
  1. 怎么解决redis数据过多内存过大问题
  2. Oracle SYSAUX表空间维护

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

awr sysaux 处理

上一篇:入门python之前,应该先了解这些

下一篇:django-关于JSON编码格式的异常处理

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》