Purging AWR statistics from Oracle

It is easy to purge un-used data from AWR.  from scheduler$_event_log and  scheduler$_job_run_details.
In order to collect long-term statistics in AWR, it is necessary to periodically purge details that you not longer need, and SQL statement details do indeed comprise the majority of the data space used within AWR.  You can also adjust the number of SQL statements collected in an AWR snapshot by adjusting dbms_workload_repository topnsql.
These tables contain SQL that is rarely useful after a month.  Note:  You must be connected as SYSDBA to truncate these tables.

• wrh$_sqlstat contains a history for SQL execution statistics and stores snapshots of v$sql view.
  
• wrh$_sqltext stores actual text for SQL statements captured from v$sql.
  
• wrh$_sql_plan stores execution plans for SQL statements available in dba_hist_sqlstat.

This script below will show you the total amount of space in the SYSAUX tablespace used by AWR.  To purge ALL of the statistics for a specific date backwards, you can invoke the following:

-- Set retention period to 90 days

exec dbms_stats.alter_stats_history_retention(90);

-- Display retention period:

select dbms_stats.get_stats_history_retention from dual;

-- Manually purge after x days and before

exec dbms_stats_purge_stats(SYSDATE-30);

However, the data portion of the AWR tables is heavily skewed and more than 80% of the historical data is kept within the AWR tables.  By periodically removing the SQL table rows, we keep the information that is important to forecasting and capacity planning while removing old SQL statements that will never be needed.
Let's take a closer look at how to purge the AWR SQL table data rows.

Partial purge of AWR data

For long term capacity planning, it is always a good idea to run an AWR report that shows the relative amount of data consumed by each AWR table.  If you purge data that is no longer required, then you are going to have a compact set of historical data.  Let's start by getting the Oracle table size information:

col c1 heading 'table|name' format a30
col c2 heading 'table size|meg'format 999,999,999


select 
   segment_name c1, 
   sum(bytes)/(1024*1024) c2 
from 
   dba_extents 
where 
   segment_type='TABLE' 
and 
   segment_name like 'WR%'
group by 
   segment_name
order by 
   c2 desc;
table                          table size 
name                           meg 
------------------------------ ----------
wrh$_sql_plan                     3 
wrh$_sqltext                      1

Now we can simply issue a truncate command to purge all SQL data:

truncate table wrh$_sql_plan;

truncate table wrh$_sqltext;

If you want to get fancy, you would arrange to delete the old SQL tables before a particular data.

delete 
from 
   dba_hist_sql_plan
where 
   sql_id = 
      (select
           sql_id
       from
           dba_hist_snapshot
       where
    begin_interval_time < sysdate - 90);

Here is another example of purging highly populated SQL tables in order to free-up enough space for longer AWR retention periods.

delete 
from 
   dba_hist_sqltext
where 
   sql_id = 
    (select
         sql_id
      from
         dba_hist_snapshot
      where
          begin_interval_time < sysdate - 90);