Configure retention period for Automatic Workload Repository

              1326067 - Configure retention period for Automatic Workload Repository            

Symptom

Automatic Workload Repository (AWR):
This note describes how you configure the retention period and the snapshot interval for performance data in the Automatic Workload Repository (AWR) for an Oracle database.

By default, the system retains AWR performance data in the AWR repository for a period of 7 days. For important SAP systems, we recommend that you set an AWR retention period of at least 42 days (6 weeks). This kind of retention period enables you to analyze performance problems by comparing the performance between monthly closings based on the AWR performance data.

For more information about AWR, see SAP Notes 853576, 974781, 1028068, 828268, and 1149771.

Other Terms

AWR, Automatic Workload Repository
DBA_HIST_WR_CONTROL

Reason and Prerequisites

Prerequisites for using the AWR

• Oracle Database Release 10.2 or higher
• Sufficient free space for the AWR repository in the SYSAUX tablespace

Solution

Configuration of the AWR retention period

1. Determine the AWR retention period that is currently set.
   
   To do this, use the following SQL command:
   SQL> select retention from dba_hist_wr_control;
2. Define the new AWR retention period.
   Oracle default setting: 7 days
   Recommendation for SAP: At least 42 days
3. Check whether you have to extend the SYSAUX tablespace
   Determine how much freespace is available in the SYSAUX tablespace, how much space is required due to the longer retention period for the AWR, and whether the tablespace must therefore be extended. SAP Note 872851 describes how much space is occupied by which component in the SYSAUX tablespace.
   
   If you increase the AWR retention period from 7 to 42 days, you should calculate a sixfold requirement for the AWR repository. To calculate whether the free space is sufficient for the higher retention period, you can use the following SQL queries:
   
   The space currently filled by the AWR in the SYSAUX tablespace:
   SQL> select SPACE_USAGE_KBYTES/1024 "AWR Size [MBytes]"  from V$SYSAUX_OCCUPANTS where occupant_name = 'SM/AWR';
   
   Total space still free in the SYSAUX tablespace:
   SQL> select sum(bytes)/(1024 * 1024) "SYSAUX MBytes free"  from dba_free_space where tablespace_name = 'SYSAUX';
   
   Total space used in SYSAUX:
   SQL> select sum (space_usage_kbytes) from v$SYSAUX_OCCUPANTS; 
4. Set the desired AWR retention period
   
   The corresponding SQL commands are (this example assumes 6 weeks = 42 days (NUM_DAYS)):
   OS> sqlplus / as sysdba
   SQL> DEFINE NUM_DAYS = 42
   SQL> select retention from dba_hist_wr_control;
   SQL> begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* &NUM_DAYS); end;
   SQL> /
   SQL> select retention from dba_hist_wr_control;
   SQL> UNDEFINE NUM_DAYS
   SQL> exit

Configuration of the AWR snapshot interval
The default setting for the AWR snapshot interval is 60 minutes, which is sufficient in most cases. You can make the relevant settings using the following command:

SQL> SELECT SNAP_INTERVAL FROM DBA_HIST_WR_CONTROL;
SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (INTERVAL => 60); END;
SQL> /
SQL> SELECT SNAP_INTERVAL FROM DBA_HIST_WR_CONTROL;
If you require snapshots in shorter intervals for a more exact analysis of a specific situation, you can shorten this interval, for example, to 30 minutes:
SQL> DEFINE SNAP_INTERVAL=30
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MONTH-DD HH24:MI:SS') FROM DUAL;
SQL> SELECT SNAP_INTERVAL "CURRENT SNAP_INTERVAL" FROM DBA_HIST_WR_CONTROL;
SQL> BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( INTERVAL =>  &&SNAP_INTERVAL); END;
SQL> /
SQL> SELECT SNAP_INTERVAL "NEW SNAP_INTERVAL" FROM DBA_HIST_WR_CONTROL;

In most cases, we recommend that you set the standard interval again when the short interval is no longer required.




===================================================


C:\Users\SAPADMIN> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 31 16:10:23 2015
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> DEFINE NUM_DAYS = 42
SQL> select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00007 00:00:00.0
SQL> begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* &NUM_DAYS); end;
  2  /
old   1: begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* &NUM_DAYS); end;
new   1: begin dbms_workload_repository.modify_snapshot_settings ( retention => 60*24* 42); end;
PL/SQL procedure successfully completed.
SQL> select retention from dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00042 00:00:00.0
SQL> UNDEFINE NUM_DAYS
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options