Enhancements in AWR Baselines
A baseline is any set of snapshots taken over a period of time. The snapshots are selected such that they yield a set of baselines that change during the period of time that you’re collecting data. The baseline captures the time-based variations for a set of baseline statistics and alerts you when the current values differ significantly from the baseline values. An AWR baseline contains a set of AWR snapshots collected over a period of time that provides a frame of reference for a known “good period,” which you can then use as a reference period to compare performance during another time period of interest. The snapshots in an AWR baseline are grouped to provide a set of baseline values that change over time. For example, the I/O rate is highest during the peak usage times in the database. One of the most difficult problems you have in setting alert thresholds is setting those thresholds to the correct levels for appropriate alerts. Arbitrary alerts that remain identical throughout are not optimal because they will likely miss the natural peaks and valleys in the workload of a real production database. Baselines, on the other hand, are ideal for setting time- dependent alert thresholds because the baselines let the database compare apples with apples and oranges with oranges, by enabling the comparison of present performance with baseline data from a similar time period.
Oracle Database 11g enables you to collect two types of baselines: static baselines and moving window baselines. A static baseline can be a single baseline collected over a single fixed time period (for example, from Jan 1, 2008 at 10:00 A.M. to Jan 1, 2008 at 12:00 P.M.) or a repeating baseline collected over a repeating time period (for example, every first Monday in a month from 10:00 A.M. to 12:00 P.M. for the year 2008). The moving window baseline captures data over a window that keeps moving over time. Oracle Database 11g creates a system-defined moving window baseline by default. This default moving window corresponds to the AWR data captured during the AWR retention period, which is now eight days, rather than seven days, as it was in the Oracle Database 10g release.
Baselines help you set alert thresholds, monitor performance, and compare advisor reports. This is a definite improvement over the Oracle Database 10g release, where all you can really do with an AWR baseline is to just create and drop static single baselines. Oracle Database 11g provides several enhancements to the AWR baselines feature, including the following:
Baseline templates to schedule the creation of a baselineMoving window baselines from which you can specify adaptive thresholdsAWR Baseline Metric ThresholdsSingle AWR Baseline
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.
SET SERVEROUTPUT ON
DECLARE
l_return NUMBER;
BEGIN
-- Using procedures.
DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id => 2490,
end_snap_id => 2491,
baseline_name => 'test1_bl',
expiration => 60);
DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_time => TO_DATE('09-JUL-2008 17:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('09-JUL-2008 18:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => 'test2_bl',
expiration => NULL);
-- Using functions.
l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_snap_id => 2492,
end_snap_id => 2493,
baseline_name => 'test3_bl',
expiration => 30);
DBMS_OUTPUT.put_line('Return: ' || l_return);
l_return := DBMS_WORKLOAD_REPOSITORY.create_baseline(
start_time => TO_DATE('09-JUL-2008 19:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('09-JUL-2008 20:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => 'test4_bl',
expiration => NULL);
DBMS_OUTPUT.put_line('Return: ' || l_return);
END;
/
Return: 8
Return: 9
PL/SQL procedure successfully completed.
The new baselines are visible in DBA_HIST_BASELINE view.
COLUMN baseline_name FORMAT A15
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;
BASELINE_ID BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME
----------- --------------- ------------- ----------------- ----------- -----------------
6 test1_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00
7 test2_bl 2490 09-JUL-2008 17:00 2491 09-JUL-2008 18:00
8 test3_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:00
9 test4_bl 2492 09-JUL-2008 19:00 2493 09-JUL-2008 20:00
4 rows selected.
The DBA_HIST_BASELINE_DETAILS view shows details about all AWR baselines.
SQL> desc dba_hist_baseline_details
Name Null? Type
------------------------------ -------- --------------
DBID NUMBER
INSTANCE_NUMBER NUMBER
BASELINE_ID NUMBER
BASELINE_NAME VARCHAR2(64)
BASELINE_TYPE VARCHAR2(13)
START_SNAP_ID NUMBER
START_SNAP_TIME TIMESTAMP(3)
END_SNAP_ID NUMBER
END_SNAP_TIME TIMESTAMP(3)
SHUTDOWN VARCHAR2(3)
ERROR_COUNT NUMBER
PCT_TOTAL_TIME NUMBER
LAST_TIME_COMPUTED DATE
MOVING_WINDOW_SIZE NUMBER
CREATION_TIME DATE
EXPIRATION NUMBER
TEMPLATE_NAME VARCHAR2(64)
Information about a specific baseline can be displayed by using the BASELINE_ID with the SELECT_BASELINE_DETAILS pipelined table function
SELECT *
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_details(6));
Baselines are renamed using the RENAME_BASELINE procedure.
BEGIN
DBMS_WORKLOAD_REPOSITORY.rename_baseline(
old_baseline_name => 'test4_bl',
new_baseline_name => 'test5_bl');
END;
/
Baselines are dropped using the DROP_BASELINE procedure.
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test1_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test2_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test3_bl');
DBMS_WORKLOAD_REPOSITORY.drop_baseline(baseline_name => 'test5_bl');
END;
/
Baseline Templates
The time period spanned by a baseline template can lie in the future or it can encompass a past timeline. No matter which timeframe you choose, the manageability infrastructure automatically generates a task and creates a baseline right away.Each night, the MMON (Memory Monitor background process) task checks to see whether the end time has passed for any baseline templates you created. If it discovers that a template for baseline generation contains a completed time range, it will create the baseline for the period specified by the baseline template. Besides it will delete the expired Baseline Templates,the information refer to the DBA_HIST_BASELINE_TEMPLATE view.You can create two types of baseline templates—a single baseline template or a repeating baseline template.
You can schedule the creation of an AWR baseline for a contiguous future time period such as a known heavy usage period. Using the single AWR baseline template, you can then automatically capture a baseline of the performance during the period you specify. The following example shows how to create a single baseline template using the CREATE_BASELINE_TEMPLATE procedure:
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline_template(
start_time => TO_DATE('01-DEC-2008 00:00', 'DD-MON-YYYY HH24:MI'),
end_time => TO_DATE('01-DEC-2008 05:00', 'DD-MON-YYYY HH24:MI'),
baseline_name => '01_dec_008_00_05_bl',
template_name => '01_dec_008_00_05_tp',
expiration => 100);
END;
/
The optional expiration parameter specifies that this baseline will expire in 100 days. The value you set for the expiration parameter specifies the length of time for which the database will maintain a baseline. If you don’t specify an expiration time period (NULL), the baseline will never expire. The baseline_name and template_name parameters are self-explanatory. The start_ time and end_time parameters specify the beginning and ending snapshot time periods. You can also specify a DBID parameter, but its value defaults to NULL if you omit it, as in this case.
You can create a repeating baseline template to schedule the creation of an AWR baseline for a known period such as around 3:00 P.M. every Friday evening for an entire year. The database will automatically create a new baseline every Friday and you can have the database also automatically remove older baselines after a specified expiration time. Here’s how you create a repeating baseline template using the CREATE_BASELINE_TEMPLATE procedure again:
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 => 'monday_morning_bl_',
template_name => 'monday_morning_tp',
expiration => NULL,
DBID => 12345);
END;
/
The following is a brief explanation of the values of the various parameters in the CREATE_BASELINE_TEMPLATE procedure:
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_ BASELINE_NAME_PREFIX START_TIME
------------------------------ --------- ------------------------------ --------------------
END_TIME DAY_OF_WE HOUR_IN_DAY DURATION EXPIRATION
-------------------- --------- ----------- ---------- ----------
01_dec_008_00_05_tp SINGLE 01_dec_008_00_05_bl 01-DEC-2008 00:00:00
01-DEC-2008 05:00:00 100
monday_morning_tp REPEATING monday_morning_bl_ 11-JUL-2008 14:43:36
11-JAN-2009 14:43:36 MONDAY 0 5
2 rows selected.
Notice the BASELINE_NAME_PREFIX column holds either the prefix or full baseline name depending on the type of baseline being captured.
Baseline templates are dropped using the DROP_BASELINE_TEMPLATE procedure.
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => '01_dec_008_00_05_tp');
DBMS_WORKLOAD_REPOSITORY.drop_baseline_template (template_name => 'monday_morning_tp');
END;
/
Moving Window AWR Baselines
Oracle Database 11g offers you a choice between a static baseline and a moving window baseline. It also allows you to create both a single static baseline and a repeating static baseline. You can create a moving window AWR baseline instead of a mere fixed baseline corresponding to a fixed, contiguous past period in time. Oracle creates and maintains a system-defined moving window baseline by default. A moving window baseline encompasses AWR data during the AWR retention period, which is, by default, eight days. (In Oracle Database 11g, the default retention period has been increased to eight days from the previous retention period of seven days.) This default moving window baseline is called the system_moving_window.
Oracle schedules the statistics collection for this window every Sunday at midnight. The setting for days is always null for this baseline, thereby making the window size exactly match the duration of the AWR retention setting. Enterprise Manager uses the system-defined baseline as the default to compare performance with the current database performance.
Moving window baselines are especially useful when you’re using adaptive thresholds because you can then utilize the data from the entire AWR retention period to compute the values for the metric thresholds you’ve selected. By default, the adaptive thresholds feature uses statistics on the default moving window baseline (SYSTEM_MOVING_WINDOW baseline). However, Oracle advises you to use a larger moving window such as 30 days rather than the default AWR retention period of 8 days, if you’re considering using adaptive thresholds. Because a moving window baseline depends on the AWR data, it can range over the length of the AWR retention period or a shorter time span. If you want to increase the size of the moving window, make sure that you first increase the size of the AWR retention period. Use the MODIFY_BASELINE_WINDOW_SIZE procedure to resize the default moving window baseline size of 8 days. Here’s an example that sets the moving window baseline size to 20 days:
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
1 row selected.
The retention period is altered using the MODIFY_SNAPSHOT_SETTINGS procedure, which accepts a RETENTION parameter in minutes.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200); -- Minutes (= 30 Days).
END;
/
SQL> SELECT retention FROM dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0
1 row selected.
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';
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.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(
window_size => 20);
END;
/
SQL> SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';
MOVING_WINDOW_SIZE
------------------
20
1 row selected.
Setting AWR Baseline Metric Thresholds
Sometimes, you want to examine the metric threshold settings for the time period spanned by a baseline. Using the AWR data contained in the baseline, you can compute the metric threshold values. Use the SELECT_BASELINE_METRICS function to display the metric value statistics during the period covered by a baselineSELECT *FROM TABLE(DBMS_WORKLOAD_REPOSITORY.select_baseline_metric('peak_baseline'));
The previous code will display the metric thresholds for the baseline named peak_baseline.
Oracle Database 11g provides a built-in alert infrastructure that warns you about potential problems in the database. The default alerts include alerts pertaining to tablespace usage, recovery area space problem, suspended resumable sessions, and the “snapshot too old” error. However, you can also specify a custom performance alert based on performance-related metric thresholds. For example, a blocked_user threshold issues an alert when the number of users blocked by any one session exceeds the metric threshold you set.
Performance alert thresholds can be difficult to determine because the expected metric values do vary by the type and amount of the workload.Using baselines, you can capture metric value statistics. If the baseline is static, you can manually compute the metric value statistics over the baseline. If you’re using a system moving window, the database can automatically compute the metric value statistics over the moving window. You can then use the baseline metric statistics to define the alert thresholds specific to the baseline.
Baselines capture metric values, which the database will then compare against current performance metrics to judge how current performance measures up against performance during a known good period. If there’s a serious discrepancy—that is, if the expected values are very different from the actual present statistics—the database will issue a performance alert. Whether you use a manually computed static baseline or a baseline automatically computed over the system moving window, the baseline values are compared to present statistics to see if an alert is justified. Adaptive thresholds are so named because the thresholds aren’t fixed, but vary according to the conditions in the database—they adapt to the type and amount of the workload.
The database computes statistics from the system moving window according to the BSLN_MAINTAIN_STATS_SCHED schedule.
The metric statistics that you capture over a baseline enable you to set thresholds for comparing baseline statistics to current activity. You can use three different threshold types, as explained here:
Significance level Thresholds based on significance level use statistical significance todetermine whether the current levels observed are unusual compared to baseline values, thus meriting an alert. For example, if you set the significance level to 0.99 for a metric, the alert threshold will be setwhere 1 percent of the observed metric values are outside the value set for the metric. The database will thus issue an alert when 1 percent of the metric values are different from the expected metric value. Note that the higher the significance level, the fewer the number of alerts that will be issued by the database. For example, a significance level of 0.9999 would cause fewer alerts to be raised than a significance level of 0.99.Oracle Database 11g fully integrates the selection of adaptive thresholds for performance metrics with the AWR baselines, with the baselines serving as the source of the metrics. The database determines the alert thresholds by examining the metric statistics captured over the baseline time period. Thus, the database sets the thresholds based on data provided by the database itself, and you don’t have to know any system-specific metrics. The database sets the thresholds based on system data itself and some metadata provided by you. Using the Enterprise Manager, you can choose a starter set of thresholds based on either the OLTP or the Data Warehouse workload profile.
Once you select the appropriate workload profile, the database will automatically configure and maintain the adaptive thresholds based on the default SYSTEM_MOVING_WINDOW baseline. The adaptive thresholds will cover all metrics suitable for the chosen workload profile. Once you configure the adaptive thresholds, you can edit the thresholds levels. When you’re editing the threshold levels, Oracle recommends that you set the initial significance level thresholds conservatively in the beginning because a very high significance level will keep the number of alerts low.
