正德厚生,臻于至善

sysaux表空间异常增长之统计信息数据未自动清理

首先还是去查sysaux表空间中占用空间最多的组件和对象
select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB
from V$SYSAUX_OCCUPANTS order by SPACE_USAGE_KBYTES desc;

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
 ORDER BY SIZE_MB DESC;
如果发现是统计信息的问题,可以按照如下方法清理:

法一:purge(底层用delete删除)
Show the current history level:

select dbms_stats.get_stats_history_availability from dual;
Assuming  history is 100 days old and you want to purge it until 10 days old:

begin
for i in reverse 61..100
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/
Show the new history level

select dbms_stats.get_stats_history_availability from dual;

法二:truncate
只有当purge的情况下,还是没有释放空间,或者需删除量过大时,才建议用truncate方式进行清理。

备份待删除基表中最新数据
create table SYS.WRI$_OPTSTATHISTHEADHISTORYBAK as (select * from sys.wri$_optstat_histhead_history where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTATHISTGRMHISTORYBAK as (select * from sys.WRI$_OPTSTAT_HISTGRM_HISTORY where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTAT_TAB_BAK as (select * from sys.wri$_optstat_tab_history where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTAT_IND_BAK as (select * from sys.wri$_optstat_ind_history where savtime > SYSDATE - 14);

查看SM/OPTSTAT(用于存储老的统计信息)保留天数
select dbms_stats.get_stats_history_retention from dual; 

设置SM/OPTSTAT保留的时间(-1表示无限)
exec dbms_stats.alter_stats_history_retention(-1);

truncate较大的TABLE
truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;
truncate table  sys.wri$_optstat_ind_history;
truncate table  sys.wri$_optstat_tab_history;

清理历史统计信息
exec dbms_stats.purge_stats(sysdate-30);

将历史统计信息保留时间设为30天
exec dbms_stats.alter_stats_history_retention(30);
select dbms_stats.get_stats_history_retention from dual;

将历史统计信息相关的表进行MOVE
alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_HH_ST rebuild online;
...
alter table sys.WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_IND_ST rebuild online;

对MOVE表的统计信息进行收集
 EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);
 ...
 EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);

参考
http://zxf261.blog.51cto.com/701797/776496
http://otn-world.blogspot.com/2014/06/purging-optimizer-stats.html
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68644
赞(0) 打赏
未经允许不得转载:徐万新之路 » sysaux表空间异常增长之统计信息数据未自动清理

评论 抢沙发

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫

微信扫一扫

登录

找回密码

注册