AWR and your Database Instance

Licensing

Make sure you are licensed! Usage of AWR Reports and AWR Related data are governed by the Diagnostics Pack license. It does not come with your Enterprise Edition license unless you paid for it or negotiated it into your agreement with Oracle. That being said

Settings

View Settings

SQL> select * From dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
5551212555 +00000 01:00:00.0    +00100 00:00:00.0    DEFAULT

Configure Settings

Note all intervals are specified in Minutes!
  • 7 days = 10080
  • 31 days = 44640
  • 90 days = 129600
execute dbms_workload_repository.modify_snapshot_settings(
    interval => 60,
    retention => 129600);

How much disk space is the AWR data taking in my database?

There is a Oracle supplied script called utlsyxsz.sql that shows SYSAUX related sizing including AWR. I have used it and I am not sure where they are getting their numbers from but will review the script in the future. In the meantime this will give you info about the current AWR storage:
WITH AWR AS (select sum(bytes)/1024/1024 as AWR_mb 
               From dba_segments 
              where segment_name like 'WRM%' or segment_name like 'WRH%'),
     TOT AS (SELECT SUM(BYTES)/1024/1024 as TOT_mb
               FROM DBA_SEGMENTS)
SELECT AWR.AWR_mb, TOT.TOT_mb, ROUND(AWR_MB / TOT_MB * 100,2) as Ratio FROM DUAL, AWR, TOT;
And estimate storage required per day:
SET SERVEROUTPUT ON
DECLARE
  AWR_STORAGE_IN_MB NUMBER;
  TOT_DB_SIZE_IN_MB NUMBER;
  RATIO_AWR_TO_DB   NUMBER;
  RETENTION_DAYS    NUMBER;
  STORED_DAYS       NUMBER;
BEGIN
  SELECT SUM(bytes)/1024/1024 as AWR_SIZE_mb 
    into AWR_STORAGE_IN_MB
    From dba_segments 
   where segment_name like 'WRM%' or segment_name like 'WRH%';

  SELECT SUM(BYTES)/1024/1024
    INTO TOT_DB_SIZE_IN_MB
    FROM DBA_SEGMENTS;
    
  SELECT extract( day from ( RETENTION ) )
    INTO RETENTION_DAYS
    FROM dba_hist_wr_control;
    
  select count(distinct trunc(end_interval_time)) 
    INTO STORED_DAYS
    FROM dba_hist_snapshot;
  
  RATIO_AWR_TO_DB := ROUND(AWR_STORAGE_IN_MB / TOT_DB_SIZE_IN_MB * 100,2);
   
   DBMS_OUTPUT.PUT_LINE('*** Current AWR Storage Info ***');
   DBMS_OUTPUT.PUT_LINE('AWR Space Used:'||AWR_STORAGE_IN_MB||'mb for '||STORED_DAYS||' days');
   DBMS_OUTPUT.PUT_LINE('Storage per day: '||ROUND(AWR_STORAGE_IN_MB/STORED_DAYS)||'mb');

   IF (STORED_DAYS < RETENTION_DAYS) THEN
      DBMS_OUTPUT.PUT_LINE('*** Projected Storage Info ***');
      DBMS_OUTPUT.PUT_LINE('Retention Days '||RETENTION_DAYS);
      DBMS_OUTPUT.PUT_LINE('Projected Total Storage Required for AWR Data:'||
          ROUND((AWR_STORAGE_IN_MB/STORED_DAYS)*RETENTION_DAYS,2)||'mb');
   END IF;
   
   DBMS_OUTPUT.PUT_LINE('*** Database Storage Info ***');
   DBMS_OUTPUT.PUT_LINE('TOT_STORAGE_IN_MB '||TOT_DB_SIZE_IN_MB||'mb');
   DBMS_OUTPUT.PUT_LINE('Percent of data in DB used by AWR data: '||RATIO_AWR_TO_DB||'%');
END;
/