Find in this Blog

Monday, August 31, 2015

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




No comments:

Post a Comment

Ask Your Questions ?#