AWR Baseline Enhancements in Oracle Database 11g

AWR Baseline Enhancements in Oracle Database 11g : Compare AWR reports during issue period

In the world, there are 'n' number production databases are running. But one common question you may be listened, "My application is slow". Some time people used to tell database is very slow etc.

Myth # Database was never slow. Neither it was slow nor It will be slow. Some performance may be degraded.

Performance degradation of the database over time happens when your database was performing optimally in the past, such as 3 months ago, but has gradually degraded to a point where it becomes noticeable to the users. The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time. While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods. The two time periods selected for the AWR Compare Periods report can be of different durations. The report normalizes the statistics by the amount of time spent on the database for each time period and presents statistical data ordered by the largest difference between the periods.

For example, a batch workload that historically completed in the maintenance window between 10:00 p.m. and midnight is currently showing poor performance and completing at 2 a.m. You can generate an AWR Compare Periods report from 10:00 p.m. to midnight on a day when performance was good and from 10:00 a.m. to 2 a.m. on a day when performance was poor. The comparison of these reports should identify configuration settings, workload profile, and statistics that were different in these two time periods. Based on the differences identified, you can more easily diagnose the cause of the performance degradation.

Note:
Most of the procedures and functions in the DBMS_WORKLOAD_REPOSITORY package accept a DBID parameter, which defaults to the local database identifier. For that reason the following examples will omit this parameter.

This Topic contains the following sections:

1) Managing Baselines
2) Running the AWR Compare Periods Reports
3) Using the AWR Compare Periods Reports

1) Managing Baselines:

Baselines are an effective way to diagnose performance problems. AWR supports the capture of baseline data by enabling you to specify and preserve a pair or a range of snapshots as a baseline. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.
A moving window baseline corresponds to all AWR data that exists within the AWR retention period. Oracle Database automatically maintains a system-defined moving window baseline. The default size of the window is the current AWR retention period, which by default is 8 days.

This section contains the following topics:

1.1) Creating a Baseline
1.2) Deleting a Baseline
1.3) Computing Threshold Statistics for Baselines

1.1) Creating a Baseline:
Before creating a baseline, carefully consider the time period you choose as a baseline because it should represent the database operating at an optimal level. In the future, you can compare these baselines with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.

You can create the following types of baseline:

1.1.1) Fixed Baselines
1.1.2) The Moving Window Baseline
1.1.3) Baseline Templates

1.1.1) Fixed Baselines:

The fixed, or static, baseline functionality is a little more flexible in Oracle 11g compared to that of Oracle 10g. Originally, the DBMS_WORKLOAD_REPOSITORY package included a single CREATE_BASELINE procedure allowing you to define baselines using specific snapshot IDs. It now includes overloaded procedures and functions allowing baselines to be created using start and end times, which are used to estimate the relevant snapshot IDs. The functions have the same parameter lists as the procedures, but return the baseline ID. By default baselines are kept forever, but the new expiration parameter allows them to be automatically expired after a specified number of days.

E.g.,

-- To Create :

SQL>
exec DBMS_WORKLOAD_REPOSITORY.create_baseline(start_snap_id => 15807,end_snap_id => 15808,baseline_name => 'When_Good_perf',expiration => 60);

OR

SQL>
 exec DBMS_WORKLOAD_REPOSITORY.create_baseline(start_time => TO_DATE('16-JUL-2014 15:30', 'DD-MON-YYYY HH24:MI') ,end_time => TO_DATE('17-JUL-2014 15:30', 'DD-MON-YYYY HH24:MI') ,baseline_name => 'Tmp_when_good_perf',expiration => NULL );

-- To View :

SQL> SELECT baseline_id, baseline_name, START_SNAP_ID, 
       TO_CHAR(start_snap_time, 'DD-MON-YYYY HH24:MI') AS start_snap_time,       
       END_SNAP_ID,            
       TO_CHAR(end_snap_time, 'DD-MON-YYYY HH24:MI') AS end_snap_time
FROM   dba_hist_baseline
WHERE  baseline_type = 'STATIC'
ORDER BY baseline_id;

output:

BASELINE_ID BASELINE_NAME   START_SNAP_ID START_SNAP_TIME            END_SNAP_ID END_SNAP_TIME
-----------             -------------------------- ----------------------------------------------                          ----------- --------------------------
          1 When_Good_perf          15807 16-JUL-2014 10:30                15808 16-JUL-2014 11:30
 2 Tmp_when_good_perf         15812 16-JUL-2014 15:30                15836 17-JUL-2014 15:30
            

-- To Rename : Baselines are renamed using the RENAME_BASELINE procedure.

SQL>
exec DBMS_WORKLOAD_REPOSITORY.rename_baseline(old_baseline_name => 'Tmp_when_good_perf',new_baseline_name => 'when_good_perf2');

-- To Drop : Baselines are dropped using the DROP_BASELINE procedure.

SQL>
exec DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'Tmpt_when_good_perf');

Note:
The current AWR retention period can be displayed by querying the RETENTION column of the DBA_HIST_WR_CONTROL view.

SQL> SELECT retention FROM dba_hist_wr_control;

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

The retention period is altered using the MODIFY_SNAPSHOT_SETTINGS procedure, which accepts a RETENTION parameter in minutes.

SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 43200);  -- Minutes (= 30 Days).

1.1.2) The 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. 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.

The current moving window size is displayed by querying the DBA_HIST_BASELINE view.

SQL> SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

Output :

MOVING_WINDOW_SIZE
------------------
                 8

1 row selected.

The size of the moving window baseline is altered using the MODIFY_BASELINE_WINDOW_SIZE procedure, which accepts a WINDOW_SIZE parameter in days.

-- To Change value

SQL> DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size => 25);


SQL> SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                25

1 row selected.

Note :
Oracle recommend of window size greater than or equal to 30 days when using adaptive thresholds.

1.1.3) Baseline Templates

Baseline templates allow you to define baselines you would like to capture in the future. Overloads of the CREATE_BASELINE_TEMPLATE procedure define the capture of individual baselines, or repeating baselines. Creating a single baseline template is similar to creating a time-based baseline, except the time is in the future.

SQL> 
begin
DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
    start_time    => TO_DATE('17-JUL-2014 10:00', 'DD-MON-YYYY HH24:MI'),
    end_time      => TO_DATE('17-JUL-2014 18:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name => '17jul2014_05_baseline',
    template_name => '17jul2014_05_template',
    expiration    => 100);
END;
/

Templates for repeating baselines are a little different as they require some basic scheduling information. 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. Since the template will generate multiple baselines, the baseline name is derived from the BASELINE_NAME_PREFIX concatenated to the date. The following example creates a template that will run for the next six months, gathering a baseline every Monday between 00:00 and 05:00.


BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
   day_of_week          => 'MONDAY',
   hour_in_day          => 0, 
   duration             => 5,
   start_time           => SYSDATE,
   end_time             => ADD_MONTHS(SYSDATE, 6),
   baseline_name_prefix => 'mon_morning_baseline',
   template_name        => 'mon_morning_template',
   expiration           => NULL);
END;
/

Information about baseline templates is displayed using the DBA_HIST_BASELINE_TEMPLATE view.

SELECT template_name,template_type, baseline_name_prefix, start_time,
       end_time, day_of_week, hour_in_day, duration, expiration
FROM   dba_hist_baseline_template;


TEMPLATE_NAME                  TEMPLATE_TYPE BASELINE_NAME_PREFIX           START_TIME  END_TIME    DAY_OF_WEEK HOUR_IN_DAY   

DURATION EXPIRATION
------------------------------ ------------- ------------------------------ ----------- ----------- ----------- ----------- ---------- ----------
17jul2014_05_template          SINGLE        17jul2014_05_baseline          7/17/2014 1 7/17/2014 6                                           100

SQL>

Fig: awr_baseline.jpg

-- To drop base line template

Baseline templates are dropped using the DROP_BASELINE_TEMPLATE procedure.

SQL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => '17jul2014_05_baseline');
SQL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => 'mon_morning_template');

2) Running the AWR Compare Periods Reports

Comparing a Baseline to Another Baseline or Pair of Snapshots: When performance degradation happens to a database over time, you should run the AWR Compare Periods report to compare the degraded performance, captured as a new baseline or a pair of snapshots, to an existing baseline. You will need a baseline that represents the system operating at an optimal level. If an existing baseline is not available, then you can compare database performance between two periods of time by using two arbitrary pairs of snapshots, as described in "Comparing Two Pairs of Snapshots"

AWR Compare Periods Report
– awrddrpt.sql – single instance
– awrgdrpt.sql - RAC

While taking compare period AWR reports, you have to enter begin and end snap values of Good performace period first and then your issue period snap start and end value.

See : awr_baseline_graph.jpg

3) Using the AWR Compare Periods Reports:

You may face below statements as you are working as DBA:

... My database was running fine yesterday but it is really slow today? What has changed? ....

The best way is, Use AWR Compare Periods Report to Identify Changes in Database Performance. As you have good performance snap periods for comparison, now you can take advantage of it.

See below figures while comparing two snaps:

compare_top_timed_ev.jpg

compare_timemodel.jpg

compare_loadprofile.jpg

The Load Profile/ Time model shows a reduction in DB Time per second and per transaction after SQL query tuning. overall performance has improved. The tuning activity was a successful.

Hope this document may help you.

Click here to read more