It appears there is an issue with the purging mechanism of AWR (Automatic Workload Repository) tables in Oracle databases. The main problem is that the tables are not being purged according to the settings defined in sys.wrm$_wr_control. This results in the accumulation of rows, causing the associated segments to become excessively large. Here’s a breakdown of the issue and some potential solutions:
Understanding the Issue
Retention Policy: Oracle uses a retention policy to decide which rows need to be purged from the AWR tables. This policy is crucial for maintaining optimal database performance and storage management.
Partitioning Mechanism: The AWR tables store snapshot data in partitions. During the nightly purge tasks, partitions that only contain expired data (as per the retention policy) are dropped to free up space.
Partition Dropping Criteria: A partition is only dropped if all the data within it has expired. If even one row within a partition is still valid, the entire partition is retained. This can lead to the retention of old data beyond its expiry date.
Partition Splits: If partitions are not split appropriately, newer data can end up in the same partition as older data. The result is that the entire partition cannot be dropped until all the data within it, including the newer entries, has expired. This delays the purging of older data.
Potential Solutions
Review and Adjust Retention Settings: Ensure that the retention settings in sys.wrm$_wr_control are correctly configured. Adjust the retention period if necessary to ensure it aligns with the data purging requirements.
Manual Partition Management: Consider manually managing the partitions. This might involve splitting partitions to ensure that data is more evenly distributed, allowing for more efficient purging.
Regular Monitoring and Maintenance: Implement regular monitoring of partition sizes and row counts. This can help identify partitions that are not being purged as expected and take corrective actions.
Database Maintenance Scripts: Develop and use maintenance scripts that can help in identifying and managing partitions that should be purged. These scripts can automate the process of checking partition data against the retention policy.
Consult Oracle Support: If the issue persists, it might be beneficial to consult Oracle Support. There could be underlying issues or bugs that require patches or workarounds provided by Oracle.
Solution to Managing Partitioned AWR Objects
Managing the Automatic Workload Repository (AWR) objects in Oracle databases can be challenging, especially when dealing with large amounts of data that need to be purged efficiently. One potential solution is to manually split the partitions of the partitioned AWR objects, which increases the likelihood of smaller partitions being purged once their retention time is reached.
Steps to Manually Split AWR Partitions
Set the Session for Splitting Partitions:
First, you need to set a session parameter to enable partition splitting. This can be done using an undocumented command:
ALTER SESSION SET “_swrf_test_action” = 72
This command performs a single split of all the AWR partitions.
Check Partition Details Before the Split:
It’s important to review the partition details before performing the split to understand the current state of the table. You can use the following SQL query:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 AS Size_GB
FROM dba_segments
WHERE segment_name = ‘WRH$_ACTIVE_SESSION_HISTORY’;
Split the Partitions:
Execute the partition split using the previously set session parameter:
ALTER SESSION SET “_swrf_test_action” = 72;
Note: This command will split partitions for all partitioned AWR objects. It initiates a single split and does not need to be disabled. It will need to be repeated if multiple splits are required.
Check Partition Details After the Split:
After splitting the partitions, check the partition details again to verify the changes:
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 AS 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.
Alternative: Purging Data Based on Snapshot Range
An alternative approach is to purge data based on a snapshot range. This method might not be suitable for all cases, as it may remove data that has not yet reached the retention limit.
Identify Snapshot ID Range in Each Partition:
Use the following PL/SQL block to identify the minimum and maximum sapshot IDs 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;
/
Drop Snapshot Range:
Once you have identified a partition with a range of snapshot IDs that can be deleted, you can free up memory by dropping the snapshot range that matches the high and low snapshot 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
);
By following these steps, you can manage and optimize the partitions of your AWR objects effectively, ensuring that old and unnecessary data is purged in a timely manner.
Conclusion
Managing AWR tables and ensuring that they are purged according to retention policies is essential for maintaining database performance and storage efficiency. By understanding the partitioning mechanism and the criteria for dropping partitions, database administrators can take proactive steps to ensure old data is purged as expected. Regular monitoring and maintenance, along with proper configuration of retention settings, can mitigate the issues associated with large AWR tables.
