ORACLE 10g SYSAUX表空間快速增長之WRH$_ACTIVE_SESSION_HISTORY篇

近一段時間監控生產資料庫的表空間使用情況，發現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/，如需轉載，請註明出處，否則將追究法律責任。