retençao no AWR

Para reduzir a retenção do AWR


EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>1107, high_snap_id=>1108);
-- Reduzir a retencao para dois dias.


exec dbms_workload_repository.modify_snapshot_settings(retention => 2880, interval => 60, topnsql => 'DEFAULT');

-- Verificar o status da retencao atual do AWR

select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

retention = snapshot interval (1 hr ) X 24 X X 7 days
For each week it's 10080

- Retain data for 21 days ( 3 weeks )
exec dbms_workload_repository.modify_snapshot_settings(retention => 30240, interval => 60, topnsql => 'DEFAULT');

- Retain data for 28 days (4 weeks

SQL> desc dba_hist_snapshot;
Nome Nulo? Tipo
----------------------------------------- -------- ----------------------------

SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)

SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER

SQL>

Drop AWR snapshots in range

declare
v_max number;
v_min number;
begin

select max(snaP_id) ,min(snap_id)
into v_max, v_min
from dba_hist_snapshot;

dbms_workload_repository.drop_snapshot_range (low_snap_id=>v_min, high_snap_id=> v_max);
end;


MAX(SNAP_ID) MIN(SNAP_ID)
------------ ------------
14360 14176

Drop AWR snapshots in range

EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id=>14176, high_snap_id=>14360);