List All Oracle AWR Baselines Using DBA_HIST_BASELINE

calendar Sep 28, 2025 / · 5 min read · Oracle AWR Performance Tuning Database Administration SQL Scripts Baseline Management Performance Monitoring DBA DBA_HIST_BASELINE  ·
Share on: linkedin copy

List All Oracle AWR Baselines Using DBA_HIST_BASELINE

This SQL script retrieves all AWR baselines stored in an Oracle Database, displaying critical information about each baseline including its identifier, name, and associated snapshot range. AWR baselines preserve specific periods of database performance statistics that are excluded from automatic purging, allowing database administrators to compare current performance against historical optimal periods.

Purpose

The primary purpose of this query is to display all existing AWR baselines in the database system. Baselines are essential for performance diagnostics because they preserve typical runtime statistics in the AWR repository, enabling administrators to compare current database performance against established performance benchmarks. This script helps identify which time periods have been preserved for performance analysis and troubleshooting.

Query Breakdown

Query:

1set lines 100
2col baseline_name format a40
3select baseline_id
4,baseline_name
5,start_snap_id
6,end_snap_id 
7from dba_hist_baseline
8order by 1
9/

Code Components Explained

SQL*Plus Formatting Commands: The set lines 100 command configures the line width to 100 characters, ensuring the output displays properly without wrapping. The col baseline_name format a40 command formats the baseline_name column to display up to 40 characters, providing sufficient space for baseline names.

SELECT Statement Columns: The query retrieves four key columns from the DBA_HIST_BASELINE view. The baseline_id is the internal identifier assigned by Oracle to each baseline. The baseline_name represents the user-specified name given when creating the baseline. The start_snap_id and end_snap_id define the range of AWR snapshots included in the baseline.

Data Source: The dba_hist_baseline view contains information about all baselines captured in the system, including the complete time range and baseline type for each entry. This data dictionary view is part of the AWR infrastructure that stores performance statistics.

Ordering: The order by 1 clause sorts the results by the first column (baseline_id), displaying baselines in the order they were created.

Key Points

Baseline Preservation: Baselines contain snapshots that are exempt from the automatic AWR purging process and are retained indefinitely unless manually dropped. This ensures critical performance data remains available for long-term analysis.

Snapshot Ranges: Each baseline represents a contiguous range of AWR snapshots identified by start and end snapshot IDs. These snapshots capture database performance metrics at specific time intervals, typically representing periods when the system operated optimally.

Performance Comparison: Database administrators use baselines to compare current performance metrics against historical data, helping identify performance degradation over time. This comparison capability is fundamental to diagnosing database performance issues.

Baseline Types: Oracle supports multiple baseline types including fixed baselines (specific time periods) and moving window baselines (continuously updating periods). The DBA_HIST_BASELINE view displays information for all baseline types in the system.

Insights and Best Practices

Identifying Critical Baselines: Running this query helps administrators understand which performance periods have been preserved in the database. Before dropping or modifying baselines, reviewing this view ensures important performance data is not accidentally deleted.

Performance Analysis Workflow: Administrators typically create baselines during periods of optimal database performance, then use these as reference points when investigating slowdowns or performance issues. The baseline_id values from this query can be used with other AWR reporting tools to generate comparative performance reports.

Baseline Management: Regular review of existing baselines using this query helps maintain an organized performance monitoring strategy. Database administrators can identify outdated baselines that may no longer be relevant and manage storage accordingly.

Integration with AWR Reports: The snapshot IDs retrieved by this query can be used as parameters when generating AWR reports through DBMS_WORKLOAD_REPOSITORY package procedures. This enables detailed performance analysis between specific baseline periods and current snapshots.

Enhanced Query Example

For more detailed baseline information, administrators can extend this query to include additional columns:

 1set lines 150
 2col baseline_name format a30
 3col baseline_type format a15
 4select baseline_id,
 5       baseline_name,
 6       baseline_type,
 7       start_snap_id,
 8       end_snap_id,
 9       to_char(start_snap_time, 'DD-MON-YYYY HH24:MI') as start_time,
10       to_char(end_snap_time, 'DD-MON-YYYY HH24:MI') as end_time
11from dba_hist_baseline
12order by baseline_id
13/

This enhanced version includes the baseline type and timestamp information, providing a more comprehensive view of baseline characteristics.

Common Use Cases

Troubleshooting Performance Issues: When database performance degrades, administrators query this view to identify appropriate baselines for comparison analysis. The baseline snapshots can then be used to generate AWR comparison reports that highlight performance differences.

Capacity Planning: Historical baselines help predict future resource requirements by showing performance trends during peak and normal operating periods. This information supports infrastructure planning and optimization decisions.

Change Management: Before implementing database changes, administrators create baselines to preserve current performance characteristics. After changes are deployed, new snapshots can be compared against these baselines to validate improvements or identify regressions.

References

Posts in this series