AWR Retention and Snapshot Interval change and configuring AWR Baselines

The dba_hist_wr_control table shows the AWR snapshot settings, namely the snapshot interval, the retention period

view: dba_hist_wr_control

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)
 CON_ID                                             NUMBER


### The below script gives present snapshot interval and rentional interval of AWR#####

SQL> @awr_settings.sql

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;


SQL> @awr_settings.sql

Snapshot Interval Retention Interval
----------------- ------------------
               60              11520


#################################################################################


AWR retention and Snapshot interval modification:

Snapshot Interval: The time between the two snapshots of AWR
Retention Interval: The retention interval determines the length of time that data will be preserved in the AWR

SQL>execute dbms_workload_repository.modify_snapshot_settings (interval => 15, retention => 23040);

PL/SQL procedure successfully completed.

Interval: snapshot interval in minutes
Rentention: retentin interval in minutes



SQL> @awr_settings.sql;

Snapshot Interval Retention Interval
----------------- ------------------
               15              23040

#################################################################################

AWR Baselines:
AWR baselines are named series of AWR snapshots that are immune from being purged by the AWR retention mechanism.

awr baselines creation:(baselines are immune to awr retention period)

start_snapshot:98
end_snapshot:103
Name: PEAK_TIME_LOAD

SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.create_baseline(98, 103, 'PEAK_TIME_LOAD');



#################################################################################



Enabling and disabling of automatic AWR snapshots generation:


Oracle uses the MMON background process to collect AWR statistics. You can disable this job by changing the parameter STATISTICS_LEVEL to BASIC then snapshots will not be taken automatically.

SQL> show parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
statistics_level                     string      BASIC

You can re-enable this job by changing the parameter STATISTICS_LEVEL to TYPICAL then snapshots will be taken automatically.


#################################################################################

Sysaux tablespace size estimation script:(this script also estimate AWR space)

SQL> @$ORACLE_HOME/rdbms/admin/utlsyxsz.sql