sysaux表空间增大的几种情况及解决办法

sysaux表空间会因为多种情况而增大，以下介绍几种情况及解决办法

1、由于设置了awr快照基线导致awr无法purge

--查看sysaux表空间内容占用情况
SELECT occupant_name "Item", 
       space_usage_kbytes / 1048576 "Space Used (GB)", 
       schema_name "Schema", 
       move_procedure "Move Procedure" 
  FROM v$sysaux_occupants 
ORDER BY 1 ;

 

--查询sysaux表空间排名前20的大段对象
select owner, segment_name, segment_type, bytes / 1024 / 1024
  from (select *
          from dba_segments
         where tablespace_name = 'SYSAUX'
         order by bytes desc)
 where rownum < 20;

 

--查看awr的保留属性
select * from dba_hist_wr_control;
 
--查看awr的最大、最小快照号 
select max(snap_id), min(snap_id) from sys.WRM$_SNAPSHOT;

--查看数据库dbid
select dbid from v$database;

--查看awr相关的基线情况
select * from dba_hist_baseline_details;

--删除指定的基线
EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'test_baseline');

--清理指定范围的awr快照
begin
     dbms_workload_repository.drop_snapshot_range(
       low_snap_id => 4217,
      high_snap_id => 4218,
      dbid => 2513064869);
end;
/

 

 

 

2、WRH$_ACTIVE_SESSION_HISTORY表未自动purge
参考MOS：WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)

--检查WRH$_ACTIVE_SESSION_HISTORY表的分区情况
SELECT owner,
  segment_name,
  partition_name,
  segment_type,
  bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';


--手工为WRH$_ACTIVE_SESSION_HISTORY表进行分区，可重复执行
alter session set "_swrf_test_action" = 72;


--检查WRH$_ACTIVE_SESSION_HISTORY表的分区情况
SELECT owner,
  segment_name,
  partition_name,
  segment_type,
  bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

之后oracle内部调用自动清理作业会purge该表的相关分区

 

3、WRH$_SQL_PLAN表未自动purge
参考MOS：How to Purge WRH$_SQL_PLAN Table in AWR Repository, Occupying Large Space in SYSAUX Tablespace. (文档 ID 1478615.1)

方法1：可能无效

--查看WRH$_SQL_PLAN表总行数
select count(*) from sys.wrh$_sql_plan;

--查看数据库dbid
SELECT dbid FROM v$database;

--清理WRH$_SQL_PLAN表(官方文档未记录该函数)
exec dbms_workload_repository.purge_sql_details(1000, &dbid);

--查看WRH$_SQL_PLAN表总行数
select count(*) from sys.wrh$_sql_plan;

方法2：

--查看WRH$_SQL_PLAN表记录的最小时间戳
select min(TIMESTAMP) from wrh$_sql_plan;

--手工清理WRH$_SQL_PLAN表，删除dba_hist_snapshot记录时间戳范围外的数据(耗时随数据量增加而延长，注意undo空间使用情况)
delete from wrh$_sql_plan where trunc(TIMESTAMP) < (select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot);

--查看WRH$_SQL_PLAN表总行数
select count(*) from sys.wrh$_sql_plan;

 

4、WRI$_OPTSTAT_TAB_HISTORY表未自动purge
参考MOS：SYSAUX Grows Because Optimizer Stats History is Not Purged (文档 ID 1055547.1)

原因：由于oracle内部自动purge WRI$_OPTSTAT_TAB_HISTORY表的JOB存在5分钟的窗口限制，因此5分钟内未清理完成则JOB失败，该MOS提供的
清理方式未必生效，建议定位到purge WRI$_OPTSTAT_TAB_HISTORY表的sql，手工进行清理

--查看sysaux表空间内容占用情况
SELECT occupant_name "Item", 
       space_usage_kbytes / 1048576 "Space Used (GB)", 
       schema_name "Schema", 
       move_procedure "Move Procedure" 
  FROM v$sysaux_occupants 
ORDER BY 1 ;


--查询sysaux表空间排名前20的大段对象
select owner, segment_name, segment_type, bytes / 1024 / 1024
  from (select *
          from dba_segments
         where tablespace_name = 'SYSAUX'
         order by bytes desc)
 where rownum < 20;

--通过awr、ash排查问题时间段自动purge的sql

--追溯sql的执行计划，建立合适的索引后，手工执行purge sql

--降低删除历史信息数据表的高水位线，并重建索引

注：move表前，导出表的所有对象定义(索引、约束、触发器等)，move前、后检查所有对象的可用状态是否一致

--检查I_WRI$_OPTSTAT_IND_OBJ#_ST索引状态
select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';

--导出索引定义，最好get_ddl和PLSQL的查看sql定义都导出
select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_TAB_ST','SYS') from dual;

--move WRI$_OPTSTAT_TAB_HISTORY表，降低高水位
alter table WRI$_OPTSTAT_TAB_HISTORY move;

--重建索引
alter index sys.I_WRI$_OPTSTAT_TAB_ST rebuild;

--检查I_WRI$_OPTSTAT_IND_OBJ#_ST索引状态
select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';