AWR - Automatic Workload Repository

Modify AWR snapshot settings

SQL> set linesize 110

SQL> col snap_interval format a22

SQL> col retention format a21

SQL> col topnsql format a24

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL          RETENTION             TOPNSQL

---------- ---------------------- --------------------- ------------------------

3141168584 +00000 01:00:00.0      +00008 00:00:00.0     DEFAULT

Change the interval for 30 minutes

execute dbms_workload_repository.modify_snapshot_settings (interval => 30, retention => 14400);

Drop one or more snapshots

exec dbms_workload_repository.drop_snapshot_range(low_snap_id =>5110, high_snap_id=>5115);

Compare two periods:  You can compare the workloads between two periods by generate the report in $ORACLE_HOME/rdbms/admin/awrddrpt