AWR负载库的ASH信息记录在AWR的基础表wrh$_active_session_history中,wrh$_active_session_history记录的这些历史信息，可以通过dba_hist_active_sess_history视图进行聚合查询，wrh$_active_session_history是一个分区表，Oracle会自动进行数据清理。Oracle根据保留策略确定需要清除哪些,对于大型AWR表，数据是根据DBID,SNAPID存储在分区中。 如果分区中的所有记录都已过期，才会在自动清理数据时使用drop partition. 如果分区中有任何一条记录该分区都不会清除， 这是一个比较普遍的问题，当SYSAUX表空间一直增长，如超过20Gb 时需要检查一下是否有AWR 的表一直未清理，如wrh$_active_session_history， 最近遇到的较多简单记录一下处理方法.
SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
SYSAUX 33277 31558 1719 95% NO |################### | <<<<< too big
SYSTEM 17918 2242 15676 13% NO |### |
...
SQL> col topseg_segment_name head SEGMENT_NAME for a30
SQL> col topseg_seg_owner HEAD OWNER FOR A30
SQL> select tablespace_name,
2 topseg_seg_owner,
3 topseg_segment_name,
4 --partition_name,
5 segment_type,
6 MB,partitions, round((RATIO_TO_REPORT (MB) OVER ()) *100,2)||'%' pct from (
7 select
8 tablespace_name,
9 owner topseg_seg_owner,
10 segment_name topseg_segment_name,
11 --partition_name,
12 segment_type,
13 round(SUM(bytes/1048576)) MB,
14 case when count(*) >= 1 then count(*) else null end partitions
15 from dba_segments
16 where upper(tablespace_name) like upper('&TBSNA') -- tablespace name
17 group by
18 tablespace_name,
19 owner,
20 segment_name,
21 segment_type
22 order by MB desc
23 )
24 where rownum <= 50;
Enter value for tbsna: SYSAUX
TABLESPACE_NAME OWNER SEGMENT_NAME SEGMENT_TYPE MB PARTITIONS PCT
------------------ ---------- ------------------------------ ------------------ ---------- ---------- -----------------------------------------
SYSAUX SYS WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 8866 2 29.46%
SYSAUX SYS WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION 2756 1 9.16%
SYSAUX SYS WRH$_EVENT_HISTOGRAM TABLE PARTITION 1920 1 6.38%
SYSAUX SYS WRH$_FILESTATXS TABLE PARTITION 1606 2 5.34%
SYSAUX SYS WRH$_ACTIVE_SESSION_HISTORY_PK INDEX PARTITION 1347 2 4.48%
SYSAUX SYS WRH$_LATCH_MISSES_SUMMARY_PK INDEX PARTITION 1222 2 4.06%
SYSAUX SYS WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION 905 2 3.01%
SYSAUX SYS WRH$_LATCH TABLE PARTITION 892 2 2.96%
SYSAUX SYS WRH$_SYSSTAT_PK INDEX PARTITION 820 2 2.72%
SYSAUX SYS WRH$_LATCH_PK INDEX PARTITION 768 2 2.55%
SQL> @seg WRH$_ACTIVE_SESSION_HISTORY
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESP
---------- --------- ------------------------------ ------------------------------ -------------------- ------------
0 SYS WRH$_ACTIVE_SESSION_HISTORY_BL TABLE SYSAUX
0 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION SYSAUX
8866 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2197585535_57370 TABLE PARTITION SYSAUX <<<<< no purge
0 SYS WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN INDEX PARTITION SYSAUX
1347 SYS WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2197585535_57370 INDEX PARTITION SYSAUX
SQL> select min(SAMPLE_TIME) from WRH$_ACTIVE_SESSION_HISTORY partition(WRH$_ACTIVE_2197585535_57370);
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
17-DEC-18 01.00.47.297 AM <<<<<
SQL> select max(snap_id),to_char(sample_time,'yyyymm') from WRH$_ACTIVE_SESSION_HISTORY partition(WRH$_ACTIVE_2197585535_57370)
2 group by to_char(sample_time,'yyyymm') order by 2;
MAX(SNAP_ID) TO_CHA
------------ ------
58084 201812
59573 201901
60916 201902
62404 201903
63844 201904
65332 201905
66773 201906
68260 201907
69748 201908
71188 201909
71699 201910
11 rows selected.
SQL> @st dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------------ ------------- ----------
2197585535 +00000 00:30:00.0 <<<<< retention 30 days
# Try to force the execution of the purge operations
SQL> alter session set "_swrf_test_action"=72;
Session altered.
SQL> @seg WRH$_ACTIVE_SESSION_HISTORY
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE
---------- ------ ------------------------------ ------------------------------ -------------------- --------------
0 SYS WRH$_ACTIVE_SESSION_HISTORY_BL TABLE SYSAUX
0 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION SYSAUX
0 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2197585535_71702 TABLE PARTITION SYSAUX <<<<< new partition
8866 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2197585535_57370 TABLE PARTITION SYSAUX
0 SYS WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN INDEX PARTITION SYSAUX
1347 SYS WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2197585535_57370 INDEX PARTITION SYSAUX
0 SYS WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2197585535_71702 INDEX PARTITION SYSAUX
7 rows selected.
NOTE:
如果上面的命令失败，可以手动执行报错中的分区脚本，直到上面的event成功
# Perform a manual purge of the snapshots older then 30 day’s:
SQL> select max(snap_id) from WRH$_ACTIVE_SESSION_HISTORY partition(WRH$_ACTIVE_2197585535_57370) where sample_time<sysdate-31; MAX(SNAP_ID) ------------ 69732 SQL> begin 2 dbms_workload_repository.drop_snapshot_range(low_snap_id=>1,high_snap_id=>69732); 3 end; 4 / PL/SQL procedure successfully completed.
Note:
保留周期是30天， 但是目前分区里的数据已接近11个月， 手动清理过期的数据，这一过程会级连清除很多表的过期数据，所有时间可能较久。 如果表段的分区大小没有回收需要手动降一下高水位，如下:
SQL> alter table WRH$_ACTIVE_SESSION_HISTORY enable row movement; SQL> alter table WRH$_ACTIVE_SESSION_HISTORY modify partition WRH$_ACTIVE_2197585535_57370 shrink space compact;; SQL> alter table WRH$_ACTIVE_SESSION_HISTORY modify partition WRH$_ACTIVE_2197585535_57370 shrink space SQL> alter table WRH$_ACTIVE_SESSION_HISTORY disable row movement;
Note:
COMPACT选项允许将收缩操作分为两个阶段。 首先，使用COMPACT选项移动行，但不调整高水位线（HWM），因此不会使解析的SQL语句无效。 可以在以后通过重新发出不带COMPACT选项的语句来调整HWM。 此时，任何依赖的SQL语句都需要重新解析。