WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)
来源:互联网 发布:公司名义开淘宝店 编辑:程序博客网 时间:2024/05/24 07:01
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)
In this Document
Symptoms
Cause
Solution
Community Discussions
References
APPLIES TO:
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.
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 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.
- 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';
- 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.
- 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
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:
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
- WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)
- WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)
- sys.wrh$_active_session_history v$active_session_history
- How Does The Invoice Date Get Derived From The Sales Order Line? (文档 ID 300311.1)
- RETENTION POLICY
- Your password does not satisfy the current policy requirements
- Your password does not satisfy the current policy requirements解决办法
- Srvctl Does Not Work After the Grid infrastructure upgrade from 11gr2 to 12c (文档 ID 2132856.1)
- CONFIGURE RETENTION POLICY
- [loadFromRemoteSources]This release of the .NET Framework does not enable CAS policy by default
- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- 解决 MySQL 5.7 中 Your password does not satisfy the current policy requirements. 问题
- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- 解决 MySQL 5.7 中 Your password does not satisfy the current policy requirements. 问题
- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- Mysql 异常ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
- Java获取json数组对象的方法
- 适用于Web应用的五套通用型服务器设置方案
- sqlserver2008之用户名和密码迁移
- mybatis与spring整合(扫描)
- 阿里云 OCR身份证识别Api 使用(一)
- WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)
- BeautifulSoup解析网页
- hibernate中@Transient
- Mac 下的android studio获取sha1和md5
- HDU-1004 Let the Balloon Rise
- OpenCV —— Calling Cameras
- tensorflow之如何打印tensor张量的值
- http请求远程接口获得Json数据(2) --- 利用HttpClient发送post/get请求
- 软件测试江湖之公会武器之争