Oralce sysaux WRH$_ACTIVE_SESSION_HISTORY清理

In this Document

Symptoms

Cause

Solution

References

Symptoms

sysaux表空間的WRH$_ACTIVE_SESSION_HISTORY表變得很大，把SYSAUX表空間撐得報警。

Cause

AWR 表沒有根據sys.wrm$_wr_control的設定清除數據，行數不斷累積，表段變得非常的大。

Solution

從dba_hist_wr_control視圖查看當前的快照保留時間

SQL> 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                         100

從dba_hist_snapshot視圖查看當前快照id

SELECT * FROM dba_hist_snapshot t ORDER BY t.snap_id ASC;

如下程式會從WRH$_ACTIVE_SESSION_HISTORY 表中得到一個最小和最大的snap id

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;

>EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(102, 105); --選擇清除一個快照範圍

實際去測試這句的時候，不知道是卡很久還是太慢，沒有成功。使用之前需再次測試。

>alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;  --把表縮小

另一種方式

alter session set "_swrf_test_action" = 72;

會將WRH$_ACTIVE_SESSION_HISTORY分區表劃分成更細的一個分區表（SYSAUX表空間下的其他分區表其實也會被分多一個分區）

然後我直接將那個小分區給幹掉了，這樣做會刪掉最小那個分區全部的snapshot

SQL> alter table WRH$_ACTIVE_SESSION_HISTORY drop partition WRH$_ACTIVE_1015375936_0  ;

Table altered.

重複運行上面的alter session set "_swrf_test_action" = 72;會重新建立一個分區。

我麼刪掉了快照歷史，去看ASH報告，就會得到沒有數據的提示。

No data exists for this section of the report.

Back to Load Profile
Back to Top

Top SQL Command Types

No data exists for this section of the report.

Back to Load Profile
Back to Top

Reference

   【1】WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)

   【2】 http://blog.itpub.net/26764973/viewspace-1853612/