ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE 10g SYSAUX表空间快速增长之WRH$_ACTIVE_SESSION_HISTORY篇

ORACLE 10g SYSAUX表空间快速增长之WRH$_ACTIVE_SESSION_HISTORY篇

原创 Linux操作系统 作者:dcswinner 时间:2011-12-30 16:50:19 0 删除 编辑

近一段时间监控生产数据库的表空间使用情况,发现SYSAUX表空间非常大(17g之多),而且每天都有几十M的增长,看到此现象后,认为这很不正常,分析SYSAUX表空间那些segments占用空间比较大:

SELECT SUM(T.BYTES)/1024/1024 segments_SIZE,T.segment_name
  FROM Dba_Segments t
 WHERE t.tablespace_name='SYSAUX'  
   AND ROWNUM<21
 GROUP BY T.segment_name ORDER BY 1 DESC;

segments_size segment_name
10631.0625 WRH$_ACTIVE_SESSION_HISTORY
2683.0625 WRH$_ACTIVE_SESSION_HISTORY_PK
1026.0625 WRH$_LATCH_MISSES_SUMMARY_PK
730.0625 WRH$_LATCH_MISSES_SUMMARY
618.0625 WRH$_SQLSTAT
564.0625 WRH$_LATCH
496.9375 WRH$_LATCH_PK
490 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
471.8125 WRH$_SYSSTAT_PK
395 SYS_LOB0000009001C00038$$
378 WRI$_OPTSTAT_HISTGRM_HISTORY
371.5 WRH$_SEG_STAT
370.25 WRH$_PARAMETER_PK
305.125 WRH$_SYSSTAT
292.125 WRH$_PARAMETER
280 WRH$_SQL_PLAN
274 WRH$_SYSMETRIC_SUMMARY
268 SYS_LOB0000008995C00004$$
257.75 WRH$_SYSTEM_EVENT_PK
247 I_WRI$_OPTSTAT_H_ST
233.75 WRH$_SYSTEM_EVENT
205.4375 WRH$_SERVICE_STAT_PK

发现WRH$_ACTIVE_SESSION_HISTORY表以及其索引占据了绝大部分的空间,这个表是oracle 10g 用来存储AWR的相关信息,对应的视图是:dba_hist_active_sess_history                                                                                        注:oracle提供的dba_hist开头的视图中,都是通过wrh$ 开头的表,wrm$开头的表以及部分dba_hist开头的表关联出来的。

查看快照信息:

SELECT COUNT(*) FROM WRM$_SNAPSHOT;或者SELECT COUNT(*) FROM dba_hist_snapshot;

发现保存有25043个快照!

看看最旧的快照是什么时候的:

SELECT * FROM WRM$_SNAPSHOT t ORDER BY t.snap_id ASC;

发现相关的快照竟是两年前的!

查看此库关于awr的相关参数设置:

SELECT * FROM wrm$_wr_control;或者select a.snap_interval,a.retention,a.topnsql from dba_hist_wr_control a;

SNAP_INTERVAL RETENTION TOPNSQL
+00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

保留历史快照的时间是7天,和oracle默认的是一致的,为什么数据库没有删除过期的快照信息?而且还发现所有历史的快照信息并不是连续的,难道这是oracle的bug,还是有人为的删除过历史的快照信息导致了oracle的bug?这个数据库原来是另外一个团队维护的,中间做过一次物理的迁移和升级,在他们手上还做了什么操作呢?一大堆的疑问。

看着sysaux表空间快要满了,只能先想办法先将此表的数据清理写,以降低sysaux表空间的大小。

通过参考metalink文章:WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged [ID 387914.1]

select table_name,partition_name 
from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
alter session set "_swrf_test_action" = 72;      
 
set serveroutput on 
declare
CURSOR cur_part IS
SELECT partition_name from dba_tab_partitions
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

query1 varchar2(200);
query2 varchar2(200);

TYPE partrec IS RECORD (snapid number, dbid number);
TYPE partlist IS TABLE OF partrec;

Outlist partlist;
begin
dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
dbms_output.put_line('--------------------------- ------- ----------');

for part in cur_part loop
query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query1 bulk collect into OutList;

if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
end loop;
end if;

query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
execute immediate query2 bulk collect into OutList;

if OutList.count > 0 then
for i in OutList.first..OutList.last loop
dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
dbms_output.put_line('---');
end loop;
end if;

end loop;
end;
/
 
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
 
将WRH$_ACTIVE_SESSION_HISTORY的segment占用的大小缩小。
 
参考资料:http://space.itpub.net/25380220/viewspace-713184

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12129601/viewspace-714267/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-03

  • 博文量
    61
  • 访问量
    486789