Shulou(Shulou.com)06/01 Report--

In theory, AWR data should be cleaned automatically based on retention time, but in practice, you often encounter something like this:

Col segment_name for A32 domestic set linesize 500 desc set pagesize 500 select with T1 as (select round (sum (bytes) / 1024 bytes) MB,segment_name from dba_segments where owner='SYS' group by segment_nameorder by 1 desc) select * from T1 where rownum 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 Retention from dba_hist_wr_control SNAP_INTERVAL RETENTION -- + 00000 01 00.0 + 00008 00:00:00.0select min (snap_id) Max (snap_id), dbid from sys.WRH$_EVENT_HISTOGRAM group by dbid

MOS check BUG number 14084247, almost all have this problem since 11.2.0.3, and after calling 14084247, it doesn't seem to be able to solve it. MOS sent another article, manual cleaning, DOCID387914.1, the steps are as follows:

1 check the zoning

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

2 modify implied parameters:

Alter session set "_ swrf_test_action" = 72

3 check the partition again

4 count the maximum and minimum snap_id of each WRH table

Set serveroutput on declare CURSOR cur_part IS SELECT partition_name from dba_tab_partitions WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; query1 varchar2; query2 varchar2; 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; /

5 Delete 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 to check again

7 finally, it is recommended to restart MMON refresh:

Alter system set "_ swrf_mmon_flush" = false; alter system set "_ swrf_mmon_flush" = true

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.