Oracle AWR , Baseline, Snapshot and Statistics


Database Statistics using AWR:

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

SQL> show parameter statistics_level

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 package 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. 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 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 => 17824,high_snap_id => 17836, dbid => 6754388080);
END;

Baselines (Fixed):
A 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.
    • Get the snap id to create the baseline on using DBA_HIST_SNAPSHOT
    • Create the base line as below 


BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 17924,end_snap_id => 17925, baseline_name => 'peak baseline',                   dbid => 6754388080);
END;

    • View the baseline create using the scipt below

SQL> select dbid,baseline_id,baseline_name from DBA_HIST_BASELINE;

  • Renaming a Baseline:
    • Review the existing baselines in the DBA_HIST_BASELINE view as mentioned above to determine the baseline that you want to rename.
    • 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 => 6754388080);
 END;

  • Dropping the Baseline:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'off peak baseline',
                  cascade => FALSE, dbid => 6754388080);
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.

  • Retention Period
    • Viewing AWR Retention Period:

SQL> SELECT retention FROM dba_hist_wr_control;

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 => 6754388080);
END;

Baseline Templates:
Baseline Templates are used for creating the baselines any time in the future. It 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_240518',
                   template_name => 'template_240818', 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('01-Dec-11 00:00', 'DD-MON-YY HH24:MI'),
     end_time => TO_DATE('01-Dec-11 03:00', 'DD-MON-YY HH24:MI'),
      baseline_name_prefix => 'baseline_2011_mondays_',
      template_name => 'template_2011_mondays',
      dbid => 6754388080);
END;

The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEK, HOUR_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;