Wednesday, May 9, 2012

Oracle 11g AWR (Automatic Workload Repository)

Oracle has provided many performance monitoring tools over the years. They started off with UTLBSTAT/UTLESTAT scripts followed by Statspack. Starting from Oracle 10g Automatic Workload Repository (AWR) is the new comprehensive tool for statistics gather and reporting


Database Statistics using AWR:

Gathering database statistics like wait events, time model statistics, ASH and system/session stats is enabled by defult using a STATISTICS_LEVEL parameter:

SQL> show parameter statistics_level


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

the parameter should be set to either TYPICAL or ALL to enable statistics gathering by AWR. If the parameter is set to BASIC, it will disable many database features. AWR can still be executed when the parameter is set to BASIC using DBMS_WORKLOAD_REPOSITORY pacakge but the statistics gathered will not be complete

Snapshots:

Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. See below mentioned link for ADDM details.
Oracle Database 11g Automatic Database Diagnostic Monitor (ADDM)


By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. 

Creating Snapshots:

To  manually create a snapshots in order to capture statistics at times different than those of the automatically generated snapshots. Use the below script

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;

query the below SQL and go at the end to view the newly created snapshot

SQL> select * from DBA_HIST_SNAPSHOT

 Dropping Snapshots:

To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 11024, 
                           high_snap_id => 11036, dbid => 1544388080);
END;

Baselines (Fixed):

baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

Creating a Baseline:


Based on the snapshots we created above, we'll create the baseline.

1. get the snap id to create the baseline on using DBA_HIST_SNAPSHOT
2. Create the base line as below 

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 11022, 
                   end_snap_id => 11023, baseline_name => 'peak baseline', 
                   dbid => 1544388080);
END;

view the baseline create using the script below

SQL> select dbid,baseline_id,baseline_name from DBA_HIST_BASELINE;

      DBID BASELINE_ID BASELINE_NAME
---------- ----------- ----------------------------------------------------------------
1544388080           1 peak baseline
1544388080           0 SYSTEM_MOVING_WINDOW

Renaming a Baseline:

To rename a baseline:

1. Review the existing baselines in the DBA_HIST_BASELINE view as mentioned above to determine the baseline that you want to rename.

2. Use the scripte below the rename the baseline


BEGIN 

 DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE ( old_baseline_name => 'peak baseline', new_baseline_name => 'off peak baseline', dbid => 1544388080 );

 END;

Dropping the Baseline:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'off peak baseline',
                  cascade => FALSE, dbid => 1544388080);
END;

Moving Window Baseline:

Oracle 11g introduces the concept of a moving window baseline, which is used to calculate metrics for the adaptive thresholds. The window is a view of the AWR data within the retention period.

Viewing AWR Retention Period:

SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

 The default size of the window matches the default AWR retention period of 8 days, but it can be set as a subset of this value. Before you can increase the size of the window you must first increase the size of the AWR retention period.

Modifying the AWR retention period:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
                   window_size => 30, 
                   dbid => 1544388080);
END;
Baseline Templates:

Baseline Templates are used for creating the baselines any time in the future. Baseline Templates can be single baseline and repeating baseline.

Single baseline:

Single baseline is created when the AWR data is to be collected at any time in the future e.g. you are testing a system over a week-end and want to capture the statistic data during the testing.

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   start_time => TO_DATE('12-MAY-12 00:00', 'DD-MON-YY HH24:MI'), 
                   end_time => TO_DATE('12-MAY-12 03:00', 'DD-MON-YY HH24:MI'), 
                   baseline_name => 'baseline_120518', 
                   template_name => 'template_120818', expiration => 30, 
                   dbid => 1544388080);
END;
Repeating baseline:

A repeating baseline template can be used to create and drop baselines based on a repeating time schedule. For example, you may want to capture the AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval such as one month.

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
    day_of_week => 'monday', hour_in_day => 17,
     duration => 3, expiration => 30,
     start_time => TO_DATE('13-MAY-12 00:00', 'DD-MON-YY HH24:MI'), 
     end_time => TO_DATE('13-MAY-12 03:00', 'DD-MON-YY HH24:MI'), 
      baseline_name_prefix => 'baseline_2012_mondays_', 
      template_name => 'template_2012_mondays',
      dbid => 1544388080);
END;
The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEKHOUR_IN_DAY and DURATION parameters define the day (MONDAY - SUNDAY or ALL) the baselines are generated on and the start and end point of the baseline.

Viewing Baseline templates:

SQL> select template_name, template_type,expiration  from DBA_HIST_BASELINE_TEMPLATE;

TEMPLATE_NAME                  TEMPLATE_ EXPIRATION
------------------------------ --------- ----------
template_120818                SINGLE            30
template_2012_mondays          REPEATING         30


Additional Links:

2 comments: