AWR (Automatic Workload Repository) snapshots can be used to reduce the space usage of the SYSAUX tablespace by periodically purging older snapshots. The SYSAUX tablespace can grow rapidly due to the storage of historical AWR data, which can lead to performance issues and storage space constraints.
By regularly purging older snapshots, you can free up space and prevent the SYSAUX tablespace from growing unnecessarily. This can be achieved using the DBMS_WORKLOAD_REPOSITORY.PURGE_WORKLOAD_REPOSITORY procedure, which allows you to specify a retention period for the snapshots, after which they are automatically deleted.
The below method are used to purge the same.
1.) Run the below script to check the current space usage of Optimizer Statistics Histogram & AWR tables, its relevant
indexes in SYSAUX tablespace.
SQL> conn / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql
Output is written to awrinfo.lst in the current working directory
2.) Check the statistics availability from Optimizer Statistics Histogram table by number of days.
SQL> select systimestamp – min (savtime) from sys.wri$_optstat_histgrm_history;
The query will return a result similar to the followiing:
SYSTIMESTAMP-MIN(SAVTIME)
—————————-
+000000099 22:30:28.418491
3.) Purge the Statistics by altering the number of days. i.e for this case the number of days are 7, it will purge stats more than 7 days old
SQL> exec dbms_stats.purge_stats(sysdate – 7);
Executing DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL) is faster than dbms_stats.purge_stats(sysdate – x),
as some of the processes are performing truncate for tables rather than deleting, if you does not need all historical stats, you can use PURGE_ALL.
4.) Check the partition details for the table ‘WRH$_ACTIVE_SESSION_HISTORY’ before split.
SQL> set lines 150
SQL> col SEGMENT_NAME for a30
SQL> col PARTITION_NAME for a50
SQL> SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM
dba_segments WHERE segment_name=’WRH$_ACTIVE_SESSION_HISTORY’;
5.) Split the AWR partitions so that there is more chance of the smaller partition being purged:
SQL> alter session set “_swrf_test_action” = 72;
Note : The above 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.
6.) Check the partition details for the table ‘WRH$_ACTIVE_SESSION_HISTORY’ after split.
SQL> set lines 150
SQL> col SEGMENT_NAME for a30
SQL> col PARTITION_NAME for a50
SQL> SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM
dba_segments WHERE segment_name=’WRH$_ACTIVE_SESSION_HISTORY’;
Note : 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.
You can purge the statistics based on the snapshot range. Depending on the snapshots chosen, this may
remove data that has not yet reached retention limit
so this may not be suitable for all cases.
The following output shows the low and high 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;
/
7.) From the result of the above query purge the AWR snapshots based on the low & high snap_id’s.
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id IN NUMBER, high_snap_id IN NUMBER,
dbid IN NUMBER DEFAULT NULL);
8.) Run the below script to crosscheck whether the space usage of Optimizer Statistics Histogram & AWR tables, its relevant
indexes in SYSAUX tablespace has reduced or not.
SQL> conn sys as sysdba
SQL> @?/rdbms/admin/awrinfo.sql
Output is written to awrinfo.lst in the current working directory
Note: Compare the awrinfo.lst output of step 8 with step 1 and make sure the space usage of Optimizer Statistics Histogram
& AWR tables has reduced accordingly.
