WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy.pdf

让世界为你转身

101

3页

0次

2025-02-19

100墨值下载

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文

档 ID 387914.1)

In this Document

Symptoms

Changes

Cause

Solution

Community Discussions

References

APPLIES TO:

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Oracle Database Cloud Exadata Service - Version N/A and later

Oracle Database Cloud Service - Version N/A and later

Oracle Database - Enterprise Edition - Version 10.2.0.1 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.

CHANGES

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.

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

文档 387914.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_a...

第1页共3页 2025/2/19 10:18

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:

alter session set "_swrf_test_action" = 72;

To perform a single split of all the AWR partitions.

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.

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.

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

文档 387914.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_a...

第2页共3页 2025/2/19 10:18

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文

档 ID 387914.1)

In this Document

Symptoms

Changes

Cause

Solution

Community Discussions

References

APPLIES TO:

Oracle Cloud Infrastructure - Database Service - Version N/A and later

Oracle Database Backup Service - Version N/A and later

Oracle Database Cloud Exadata Service - Version N/A and later

Oracle Database Cloud Service - Version N/A and later

Oracle Database - Enterprise Edition - Version 10.2.0.1 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.

CHANGES

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.

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

文档 387914.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_a...

第1页共3页 2025/2/19 10:18

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:

alter session set "_swrf_test_action" = 72;

To perform a single split of all the AWR partitions.

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.

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.

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

文档 387914.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_a...

第2页共3页 2025/2/19 10:18

of 3

100墨值下载

oracle

关注

评论