Oracle 11G AWR does not automatically clean up

In theory, AWR data should be cleaned automatically based on retention time, but in fact, it often comes across:

col segment_name for a32;set linesize 500;set pagesize 500;with t1 as (select round(sum(bytes)/1024/1024) MB,segment_name from dba_segments where owner=‘SYS‘ group by segment_nameorder by 1 desc )select * from t1 where rownum<16;

Then wrh$ the first few tables accounted for more than 10 g of the case, that is, the AWR data is not completely cleaned out, check the policy is all right:

  sql> Select Instance_number, Min (sample_time), Max (Sample_time) from Wrh$_active_session_history Group  by Instance_number; 2 Instance_number MIN (sample_time) MAX (sample_time)----------- -------------------------------------------------------------------------------1 25-may-16 08. 14.48.613 PM 28-mar-18 08.56.02.944 amsql> Select Snap_interval,rete Ntion from Dba_hist_wr_control; Snap_interval RETENTION------------------------------------                                                           ---------------------------------------------------------------------------+00000 01:00:00.0 +00008 00:00:00.0sql> Select min (snap_id), Max (snap_id) from Wrh$_event_histogram; MIN (snap_id) MAX (snap_id)------------------------1 16015  

MOS check bug number 14084247, from 11.2.0.3 after almost all have this problem, and hit 14084247, seemingly can not solve, MOS and send an article, manual cleanup, DOCID387914.1, the steps are as follows:
1 Checking the partitioning situation

SELECT owner,  segment_name,  partition_name,  segment_type,  bytes/1024/1024/1024 Size_GBFROM dba_segmentsWHERE segment_name=‘WRH$_ACTIVE_SESSION_HISTORY‘;

2 Modifying the implied parameters:
alter session set "_swrf_test_action" = 72;
3 re-check the partitioning situation
4 statistics of the maximum, minimum snap_id of each WRH table

Set serveroutput on declare CURSOR cur_part are SELECT partition_name from dba_tab_partitions WHERE table_name = ' Wrh$_acti Ve_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 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 /

5 Deleting unwanted data

Dbms_workload_repository. Drop_snapshot_range (
low_snap_id in number,
high_snap_id in number
dbid in number DEFAULT NULL);

6 Run @?/rdbms/admin/awrinfo.sql again check under
7 Finally, we recommend restarting the Mmon refresh:

Oracle 11G AWR does not automatically clean up