DBMS WORKLOAD REPOSITORY

View and change current AWR Settings

check the oldest available AWR snapshot

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

SQL> select min(begin_interval_time) from dba_hist_snapshot;   

MIN(BEGIN_INTERVAL_TIME)
---------------------------------------------------------------------------
26-JUN-19 10.58.48.701 PM

check current AWR retention and interval settings

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",
topnsql
from dba_hist_wr_control;

Snapshot Interval Retention Interval TOPNSQL
----------------- ------------------ ----------

               14             172800 DEFAULT

check the current size of the AWR repository

SQL> col occupant_name format a30
col occupant_desc format a60
select occupant_name, occupant_desc, round(space_usage_kbytes/(1024)) size_MB from V$SYSAUX_OCCUPANTS order by 3 desc;

OCCUPANT_NAME                  OCCUPANT_DESC                                                   SIZE_MB
------------------------------ ------------------------------------------------------------ ----------
SM/AWR                         Server Manageability - Automatic Workload Repository              14192
SM/ADVISOR                     Server Manageability - Advisor Framework                            407
SM/OTHER                       Server Manageability - Other Components                             390
SM/OPTSTAT                     Server Manageability - Optimizer Statistics History                 204
XDB                            XDB                                                                  48
SDO                            Oracle Spatial                                                       31
JOB_SCHEDULER                  Unified Job Scheduler                                                18
LOGMNR                         LogMiner                                                             14
SMON_SCN_TIME                  Transaction Layer - SCN to TIME mapping                               7
SQL_MANAGEMENT_BASE            SQL Management Base Schema                                            2
PL/SCOPE                       PL/SQL Identifier Collection                                          2
STREAMS                        Oracle Streams                                                        1
. . .
. . .

Change current AWR settings

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Create a snapshot manually

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Run AWR Reports

Run a Compare Period AWR Report

SQL> @?/rdbms/admin/awrddrpt.sql
Enter value for number of days: 10
Enter value for begin_snap1: 1
Enter value for end_snap2: 2

Enter value for number of days: 2
Enter value for begin_snap2: 20
Enter value for begin_snap2: 21