using AWR

DBMS_WORKLOAD_REPOSITORY Procedures

• CREATE_SNAPSHOT Creates manual snapshots
• DROP_SNAPSHOT_RANGE Drops a range of snapshots at once
• CREATE_BASELINE Creates a single baseline
• DROP_BASELINE Drops a single baseline
• MODIFY_SNAPSHOT_SETTINGS Changes the RETENTION and INTERVAL settings

--Dropping Snapshots
LOW_SNAP_ID The lowest snapshot ID of range to drop
HIGH_SNAP_ID The highest snapshot ID of range to drop
DBID Optional database ID

SQL> exec dbms_workload_repository.drop_snapshot_range(6,14,3491152056);

PL/SQL procedure successfully completed.

--Creating Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
END;

--Modifying Snapshot Frequency
RETENTION Length of time to retain snapshots (in minutes). Must be between 1,440
(one day) and 52,596,000 (100 years).
INTERVAL Time interval between snapshots (in minutes). Must be between 10 and
525,600 (one year).
DBID The database ID (defaults to the local DBID).

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
RETENTION => 14400, INTERVAL => 45);
END;



--To To view the current AWR settings, you can use the DBA_HIST_WR_CONTROL view, as
shown here:
SQL> Select snap_interval, retention From dba_hist_wr_control;

SQL> select * from dba_hist_wr_control;

DBID
----------
SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
TOPNSQL
----------
3491152056
+00000 01:00:00.0
+00007 00:00:00.0
DEFAULT


SQL> desc dba_hist_wr_control;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NOT NULL NUMBER
SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
TOPNSQL VARCHAR2(10)