Oracle AWR Create Baseline for Performance Monitoring DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

calendar Sep 29, 2025 / · 6 min read · Oracle Database AWR Performance Tuning Database Monitoring PL/SQL DBA Scripts Baseline Management Performance Analysis DBMS_WORKLOAD_REPOSITORY  ·
Share on: linkedin copy

Oracle AWR Create Baseline for Performance Monitoring DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE

What is Oracle AWR Create Baseline

The DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE procedure creates a baseline by preserving a specific range of AWR snapshots for future performance comparisons. A baseline protects performance data from automatic purging and allows database administrators to compare current performance against known good performance periods.

Purpose of Creating AWR Baselines

AWR baselines serve as reference points for database performance analysis by capturing system behavior during optimal or significant time periods. These preserved snapshots help identify performance degradation over time and provide a foundation for comparing different workload patterns. Baselines are excluded from automatic AWR purging processes and remain available indefinitely for analysis.

Code Breakdown

Query:

1exec dbms_workload_repository.create_baseline (<start snap>, <endsnap>,'<name>')

Syntax Components

The CREATE_BASELINE procedure accepts the following parameters for execution:

  • start_snap_id: The beginning snapshot sequence number that marks the start of the baseline period
  • end_snap_id: The ending snapshot sequence number that marks the end of the baseline period
  • baseline_name: A unique text name assigned to identify this specific baseline
  • dbid: Optional database identifier that defaults to the local database if not specified
  • expiration: Optional number of days before the baseline expires, with NULL meaning no expiration

Parameter Details

The procedure requires existing snapshots in the system for the specified snapshot range. An error returns if the baseline name already exists or if the specified snapshots do not exist in the database. The procedure can be used as either a function that returns a number or as a standalone procedure.

Practical Implementation Example

1EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
2  start_snap_id => 105, 
3  end_snap_id => 107, 
4  baseline_name => 'oltp_peakload_bl'
5);

This example creates a baseline named 'oltp_peakload_bl' between snapshots 105 and 107 for the local database. After execution, the new baseline appears in the DBA_HIST_BASELINE view for verification.

Alternative Time-Based Syntax

1DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
2  start_time => TO_DATE('23-APR-2012 17:00', 'DD-MON-YYYY HH24:MI'),
3  end_time => TO_DATE('23-APR-2012 18:00', 'DD-MON-YYYY HH24:MI'),
4  baseline_name => 'peak_hour_baseline',
5  expiration => 60
6);

Oracle 11g introduced the ability to create baselines using date and time values instead of snapshot IDs. This enhancement simplifies baseline creation by specifying exact time periods rather than looking up snapshot numbers.

Types of AWR Baselines

Fixed Baselines

Fixed baselines represent a specific contiguous time period in the past when the system operated at optimal performance levels. These baselines provide a static reference point for comparing against periods of degraded performance. The time period selected should represent the database functioning under normal, acceptable conditions.

Moving Window Baselines

Moving window baselines include all AWR data within the current retention period, which defaults to 8 days. Oracle Database automatically maintains a system-defined moving window baseline that adjusts as new data enters the retention window. These baselines work particularly well with adaptive thresholds by providing sufficient historical data for threshold calculations.

Repeating Baselines

Repeating baselines automatically capture data during recurring time intervals, such as every Monday from 1:00 PM to 3:00 PM. These templates enable Oracle Database to generate baselines on a scheduled basis without manual intervention. Repeating baselines help track performance for regular workload patterns like weekly batch jobs or monthly processing windows.

Key Benefits and Insights

Performance Comparison

Baselines enable database administrators to compare current performance against historical good performance periods. The AWR Compare Periods report uses baselines to identify performance changes and pinpoint the root causes of degradation. This comparison helps determine whether system changes improved or degraded database performance.

Adaptive Threshold Monitoring

Oracle 11g introduced adaptive thresholds that use baseline data to dynamically set alert levels. Instead of fixed thresholds that may not reflect actual system behavior, adaptive thresholds adjust based on historical patterns captured in baselines. This approach reduces false alerts and improves monitoring accuracy for databases with varying workload patterns.

Data Preservation

Snapshots included in baselines bypass the automatic AWR purging mechanism. This preservation ensures critical performance data remains available for long-term analysis and troubleshooting. Organizations can maintain baselines for regulatory compliance, capacity planning, and historical performance trending.

Best Practices

Database administrators should create baselines during periods when the system operates normally without performance issues. The baseline period should be representative of typical workload characteristics to provide meaningful comparison data. Consider using snapshot intervals of at least 15 minutes to balance data granularity with repository storage requirements.

Create baselines before major system changes such as upgrades, migrations, or configuration modifications. These pre-change baselines provide reference points for validating that changes delivered expected performance improvements. For databases with predictable patterns, use repeating baseline templates to automatically capture data during regular peak periods.

Common Use Cases

Organizations create baselines to document performance during critical business periods such as month-end processing or peak shopping seasons. When users report slow performance, baselines from similar time periods help identify what changed. Capacity planning teams use baselines to project future resource requirements based on historical growth patterns.

Development teams create baselines before and after application releases to measure performance impact. Database upgrades benefit from baseline comparisons that validate improved performance or identify regression issues. Troubleshooting performance problems becomes more effective when comparing current metrics against baseline data from properly functioning periods.

Verification and Management

After creating a baseline, verify its existence by querying the DBA_HIST_BASELINE view. This view displays all baselines with their snapshot ranges, names, and expiration settings. Monitor baseline storage consumption as part of overall AWR space management.

Drop baselines that are no longer needed using the DROP_BASELINE procedure to free repository space. Review baseline expiration settings periodically to ensure important baselines have appropriate retention policies. For RAC databases, baselines capture data across all instances for comprehensive cluster performance analysis.

References

Posts in this series