Increasing AWR Retention

Referencia
1326067 - Configure retention period for Automatic Workload Repository
http://oraclespin.wordpress.com/2009/09/27/how-to-change-awr-retention-intervaltopnsql/


Solução

– retention=>Valor em minutos (42 dias * 24 (horas por dia) * 60 minutos por hora = 60480), caso o valor da retenção seja 0, isto significa que as estatísticas nunca serão apagadas.
– internal=>30 minutos (snap neste intervalo), o valor 0 desativa o AWR  

- Retention
SYS@maq>select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00007 00:00:00.0

SYS@maq>exec dbms_workload_repository.modify_snapshot_settings(retention=>60480);

PL/SQL procedure successfully completed.

SYS@maq>select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00042 00:00:00.0


- Interval

SYS@maq>exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (INTERVAL => 30);
PL/SQL procedure successfully completed.

SYS@maq>SYS@maq>select snap_interval from dba_hist_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
+00000 00:30:00.0


Abraços