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; /