AWR Basic Info

By default the Automatic Workload Repository (AWR) is populated every hour.

Tablespace: SYSAUX (Data is stored in this tablespace)
Background Process : MMON (Flushes data from Memory to Disk)

To find the space used by AWR query the V$SYSAUX_OCCUPANTS view.

SQL> Select * From V$SYSAUX_OCCUPANTS where occupant_name like '%AWR%';

The space_usage_kbytes column gives the amount of space used by AWR in kilobytes.

To find the oldest AWR information

SQL> Select dbms_stats.get_stats_history_availability From dual;

To find the retention period for AWR. Default is 7 days.

SQL> Select dbms_stats.get_stats_history_retention From dual;

To change the retention period for AWR

SQL> Exec dbms_stats.alter_stats_history_retention(10);

Thanks