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 statis