无法根据保留策略清除WRH$_ACTIVE_SESSION_HISTORY (Doc ID 387914.1)

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

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

AWR tables are not being purged according to settings in sys.wrm$_wr_control. Because of this the tables are accumulating more and more rows and the segments associated with these tables become very large.  不会根据sys.wrm$_wr_control中的设置清除AWR表。因此，表累积了越来越多的行，与这些表关联的段变得非常大。

CAUSE

Oracle decides what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables where we store the snapshot data in partitions. One method of purging data from these tables is by removing partitions that only contain rows that have exceeded the retention criteria. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn't be removed,  then  the partition won't be dropped and as such the table will contain old data.

Oracle根据保留策略决定需要清除哪些行。对于大型AWR表，在将快照数据存储在分区中的情况下，有一种特殊的机制。从这些表中清除数据的一种方法是通过删除仅包含已超过保留条件的行的分区。在夜间清除任务期间，仅当分区中的所有数据均已到期时才删除分区。如果分区包含至少一行，根据保留策略，不应删除该行，则不会删除该分区，因此该表将包含旧数据。

If partition splits do not occur (for whatever reason), then we can end up with a situation where we have to wait for the latest entries to expire before the partition that they sit in can be removed. This can mean that some of the older entries can be retained significantly past their expiry date. The result of this is that the data is not purged as expected.

如果没有发生分区拆分（无论出于何种原因），那么我们最终将不得不等待最新的条目到期，然后才能删除它们所在的分区。这可能意味着一些较旧的条目可以在其到期日期之后保留很长时间。结果是数据没有按预期清除。

SOLUTION

A potential solution to this issue is to manually split the partitions of the partitioned AWR objects such that there is more chance of the split partition being purged.You will still have to wait for all the rows in the new partitions to reach their retention time but with split partitions there is more chance of this happening. you can manually split the partitions using the following undocumented command:

解决此问题的一种可能的方法是手动拆分已分区的AWR对象的分区，以便有更多机会清除拆分的分区。您仍然必须等待新分区中的所有行达到保留时间，但是对于分割分区，发生这种情况的可能性更大。您可以使用以下未记录的命令手动分割分区：

alter session set "_swrf_test_action" = 72;

To perform a single split of all the AWR partitions.  对所有AWR分区执行一次拆分。

1.  Check the partition details for the offending table before the split:   在拆分之前，请检查有问题的表的分区详细信息：

    SELECT owner,
     segment_name,
     partition_name,
     segment_type,
     bytes/1024/1024/1024 Size_GB
   FROM dba_segments
   WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

    

2. Split the partitions so that there is more chance of the smaller partition being purged:  分割分区，以便有更大的机会清除较小的分区：

   alter session set "_swrf_test_action" = 72;

   NOTE: This command will split partitions for ALL partitioned AWR objects. It also initiates a single split; it does not need to be disabled and will need to be repeated if multiple splits are required.  注意：此命令将为所有分区的AWR对象分割分区。它还会发起一次拆分；它不需要禁用，如果需要多次拆分，则需要重复执行。

     
3.  Check the partition details for the offending table after the split:   分割后检查有问题的表的分区详细信息：     

   SELECT owner,
     segment_name,
     partition_name,
     segment_type,
     bytes/1024/1024/1024 Size_GB
   FROM dba_segments
   WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

   With smaller partitions it is expected that some will be automatically removed when the retention period of all the rows within each partition is reached.  对于较小的分区，当达到每个分区内所有行的保留期时，有望自动删除某些分区。

As an alternative, you could purge data based upon a snapshot range. Depending on the snapshots chosen, this may remove data that has not yet reached the retention limit so this may not be suitable for all cases.  或者，您可以根据快照范围清除数据。根据选择的快照，这可能会删除尚未达到保留限制的数据，因此可能不适用于所有情况。

The following output shows the min and max snapshot_id in each partition.  以下输出显示每个分区中的最小和最大snapshot_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;
/

Once you have split the partitions and identified a partition with a range of snap ids that can be deleted, you can free up the memory by dropping a snapshot range than matches the high and low snap_ids for the partition:  分割分区并确定具有可删除的快照ID范围的分区后，可以通过删除快照范围（与该分区的高和低snap_id相匹配）的快照来释放空间：

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN "1",
high_snap_id IN "36255" 
dbid IN NUMBER DEFAULT NULL);