Managing Baselines

By default, Oracle Database automatically maintains a system-defined moving window baseline. When necessary, you can manually create, drop, or rename a baseline and view the baseline threshold settings. Additionally, you can manually resize the window size of the moving window baseline.

This section describes how to manage baselines and contains the following topics:

See Also:

"Baselines" for information about baselines

User Interface for Managing Baselines

The primary interface for managing baselines is Oracle Enterprise Manager Cloud Control (Cloud Control). Whenever possible, manage baselines using Cloud Control.

If Cloud Control is unavailable, then manage baselines using the DBMS_WORKLOAD_REPOSITORY package in the command-line interface. The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.

See Also:

Creating a Baseline

By default, Oracle Database automatically maintains a system-defined moving window baseline. However, you may want to manually create a fixed baseline that represents the system operating at an optimal level, so you can compare it with other baselines or snapshots captured during periods of poor performance.

To create baselines using command-line interface, use the CREATE_BASELINE procedure as shown in the following example: 

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270, 
                                              end_snap_id   => 280, 
                                              baseline_name => 'peak baseline', 
                                              dbid          => 3310949047, 
                                              expiration    => 30);
END;
/

In this example, a baseline is created on the database instance with the database identifier of 3310949047 with the following settings:

  • The start snapshot sequence number is 270.

  • The end snapshot sequence number is 280.

  • The name of baseline is peak baseline.

  • The expiration of the baseline is 30 days.

Oracle Database automatically assigns a unique ID to the new baseline when the baseline is created.

Tip:

To determine the range of snapshots to include in a baseline, use the DBA_HIST_SNAPSHOT view to review the existing snapshots

See Also:

Dropping a Baseline

To conserve disk space, consider periodically dropping a baseline that is no longer being used. The snapshots associated with a baseline are retained indefinitely until you explicitly drop the baseline or the baseline has expired.

To drop a baseline using command-line interface, use the DROP_BASELINE procedure as shown in the following example: 

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

In the example, the baseline peak baseline is dropped from the database instance with the database identifier of 3310949047 and the associated snapshots are preserved.

Tip:

To determine which baseline to drop, use the DBA_HIST_BASELINE view to review the existing baselines.

Tip:

To drop the associated snapshots along with the baseline, set the cascade parameter to TRUE.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_WORKLOAD_REPOSITORY package

Renaming a Baseline

To rename a baseline using command-line interface, use the RENAME_BASELINE procedure. The following example shows a RENAME_BASELINE procedure call. 

BEGIN
    DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (old_baseline_name => 'peak baseline', 
                                              new_baseline_name => 'peak mondays', 
                                              dbid              => 3310949047);
END;
/

In this example, the name of the baseline on the database instance with the database identifier of 3310949047 is renamed from peak baseline to peak mondays.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_WORKLOAD_REPOSITORY package

Displaying Baseline Metrics

To display the summary statistics for metric values in a baseline period using the command-line interface, use the SELECT_BASELINE_METRICS function: 

DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (baseline_name IN VARCHAR2,
                                                  dbid          IN NUMBER DEFAULT NULL,
                                                  instance_num  IN NUMBER DEFAULT NULL)
   RETURN awr_baseline_metric_type_table PIPELINED;

See Also:

Resizing the Default Moving Window Baseline

By default, Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. In certain circumstances, you may want to modify the window size of the default moving window baseline.

To modify the window size of the default moving window baseline using the command-line interface, use the MODIFY_BASELINE_WINDOW_SIZE procedure as shown in the following example: 

BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (window_size => 30, 
                                                          dbid        => 3310949047);
END;
/

In this example, the default moving window is resized to 30 days on the database instance with the database identifier of 3310949047.

Note:

The window size must be set to a value that is equal to or less than the value of the AWR retention setting. To set a window size that is greater than the current AWR retention period, you must first increase the value of the retention parameter as described in "Modifying Snapshot Settings".

See Also: