AWR Baselines

Oracle AWR baselines deep dive

AWR BaselinesHow to preserve good periods, compare bad periods, and make AWR history far more useful

AWR baselines are one of those Oracle features that look simple at first glance and become much more valuable once you start using them intentionally. They let you preserve important AWR periods, build repeatable reference windows, compare current behavior to known-good behavior, and stop useful snapshots from disappearing just because the default purge policy reached them.

Contents

01 Why baselines matter02 AWR fundamentals and licensing03 Baseline types04 Retention and moving window05 Creating static baselines06 Baseline templates07 Using baselines to compare periods08 Views and administration09 End-to-end demo10 Knowledge check

Section 01

Why AWR baselines matter

An AWR report tells you what happened between two snapshots. A baseline turns a period you care about into a reference point you can come back to later. That sounds modest, but operationally it is powerful: the moment you preserve a known-good workload window, troubleshooting shifts from guessing toward comparison.

Key idea

A baseline is valuable because it preserves context. Instead of asking, "Why is the database slow today?" you can ask, "How does today differ from a period when this same workload behaved acceptably?"

Normal business period

Preserve a representative daytime OLTP window so you can compare future degradation against it.

Peak period

Capture month-end, payroll, batch close, or flash-sale windows that stress the system differently from normal hours.

Special maintenance periods

Keep baselines for backups, ETL, overnight jobs, and patch windows so unusual but expected behavior has context too.

Recollect: Baselines do not create new performance data. They preserve and label already-collected AWR history so you can reason about it later.

Section 02

AWR fundamentals and the practical licensing note

AWR snapshots are captured automatically on a schedule and retained for a configurable time. By default, Oracle databases capture snapshots once every hour and keep them for eight days, although both values can be changed. Baselines sit on top of that snapshot history: they refer to selected AWR ranges and keep them from being purged automatically.

Licensing note

AWR and related functionality are part of the Oracle Diagnostics Pack, and Oracle’s licensing materials make clear that access through reports, APIs, or the underlying data requires the appropriate pack license. The CONTROL_MANAGEMENT_PACK_ACCESS parameter governs whether the diagnostic pack is enabled.

SQL - Check AWR settings and pack access

SHOW PARAMETER control_management_pack_access

SELECT snap_interval,
       retention,
       topnsql,
       tablespace_name
FROM   dba_hist_wr_control;

The SNAP_INTERVAL and RETENTION values in DBA_HIST_WR_CONTROL are stored as interval types, so they display in day/hour/minute format rather than a plain number. That view is the first place to look when you need to understand whether your AWR history is deep enough to support useful baselines.

Section 03

The three baseline types you actually need to understand

Oracle’s baseline model is richer than “saved snapshot pair.” In practice, learners should understand three kinds of baselines: static, moving window, and generated baselines from templates. The dictionary view DBA_HIST_BASELINE exposes these as STATIC, MOVING WINDOW, and GENERATED.

Static

You create it explicitly from snapshot IDs or from a start/end time. It stays until expiration or manual drop.

Moving window

Oracle maintains this automatically across recent AWR retention history. Its size is measured in days and tied to retention rules.

Generated

Created by templates so recurring periods like Monday batch or Friday payroll are preserved repeatedly without manual effort.

Section 04

Retention, moving-window size, and the math you should not ignore

One of the most important practical details is that the moving-window baseline cannot exceed AWR retention. Oracle documents the moving-window baseline size with DBA_HIST_BASELINE_DETAILS, and the retention itself with DBA_HIST_WR_CONTROL. If you want a longer moving reference window, you often must enlarge AWR retention first.

Why this matters

If your retention is only eight days but you need a 30-day rolling reference for weekly workload comparisons, the database cannot invent missing history. Baseline strategy starts with retention strategy.

SQL - View retention and moving window

SELECT snap_interval,
       retention,
       topnsql
FROM   dba_hist_wr_control;

SELECT baseline_name,
       baseline_type,
       moving_window_size,
       start_snap_id,
       end_snap_id,
       expiration
FROM   dba_hist_baseline_details
ORDER BY baseline_id;

If you need to change retention or snapshot interval, use DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS. Oracle’s package reference documents the interval and retention units as minutes.

SQL - Change AWR retention and interval

-- Example: retain AWR for 30 days and keep snapshots every 60 minutes
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 43200,
    interval  => 60
  );
END;
/

Recollect: Baselines preserve selected ranges from purge, but they do not replace the need for an AWR retention policy that matches how your business actually investigates problems.

Section 05

Creating static baselines by snapshot ID or by time

Static baselines are the workhorse feature for most DBAs. You can create them by giving start and end snapshot IDs or by giving start and end times. Oracle’s package reference and examples support both approaches. Time-based creation is convenient when you know the business window; snapshot-based creation is safer when you have already reviewed the exact snapshots you want.

First, find the right snapshots

SQL - Find candidate snapshots

SELECT snap_id,
       begin_interval_time,
       end_interval_time,
       flush_elapsed
FROM   dba_hist_snapshot
WHERE  begin_interval_time BETWEEN
         TO_DATE('11-MAR-2026 09:00', 'DD-MON-YYYY HH24:MI') AND
         TO_DATE('11-MAR-2026 18:00', 'DD-MON-YYYY HH24:MI')
ORDER BY snap_id;

Create by snapshot IDs

SQL - Create baseline by snap IDs

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id  => 586,
    end_snap_id    => 594,
    baseline_name  => 'NORMAL_LOAD',
    expiration     => 365
  );
END;
/

Create by time window

SQL - Create baseline by time range

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_time     => TO_DATE('11-MAR-2026 09:00', 'DD-MON-YYYY HH24:MI'),
    end_time       => TO_DATE('11-MAR-2026 17:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name  => 'NORMAL_LOAD_WEDDAY',
    expiration     => 90
  );
END;
/

If you omit expiration, the baseline is indefinite. That can be useful for historically important periods, but it also means you should curate names and lifecycle carefully so the baseline list does not become cluttered. The package supports renaming and dropping baselines as well.

Section 06

Baseline templates: the underrated feature for recurring workload windows

Templates are where AWR baselines become operational instead of ad hoc. Oracle supports a single template for one future window and repeating templates for patterns such as every Monday 08:00-12:00 or every last Friday batch period. Those templates create generated baselines automatically. The dictionary view DBA_HIST_BASELINE_TEMPLATE is the control panel for seeing what is scheduled.

Create a one-time future baseline template

SQL - Create single template

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
    start_time     => TO_DATE('31-MAR-2026 20:00', 'DD-MON-YYYY HH24:MI'),
    end_time       => TO_DATE('31-MAR-2026 23:30', 'DD-MON-YYYY HH24:MI'),
    baseline_name  => 'MONTH_END_BATCH_MAR2026',
    template_name  => 'MONTH_END_ONCE',
    expiration     => 180
  );
END;
/

Create a repeating weekly template

SQL - Create repeating template

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
    day_of_week    => 'MONDAY',
    hour_in_day    => 8,
    duration       => 240,
    start_time     => TO_DATE('16-MAR-2026 08:00', 'DD-MON-YYYY HH24:MI'),
    end_time       => TO_DATE('30-JUN-2026 08:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name_prefix => 'MONDAY_OPEN',
    template_name  => 'MONDAY_OPEN_TEMPLATE',
    expiration     => 60
  );
END;
/

SQL - Review baseline templates

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
ORDER BY template_name;

Section 07

Using baselines to compare bad periods against good periods

Baselines become especially valuable during regression analysis. The broad workflow is simple: preserve a known-good period, identify a degraded current period, and compare them using AWR reports, AWR compare-period reporting, and SQL-level follow-up. Oracle provides compare-period reporting through scripts such as awrddrpt.sql and package functions such as AWR_DIFF_REPORT_HTML and AWR_DIFF_REPORT_TEXT.

Good troubleshooting pattern

Use the baseline to choose your “good period” deliberately. Then compare that period to the current degraded period. The real question is not simply “What is high now?” but “What changed relative to a period where the workload behaved well?”

Three comparison questions that matter

Did load shape change?

If the business workload doubled, a baseline difference may be expected rather than pathological.

Did time move?

Compare DB time, wait classes, top SQL, and top segments to see where the system is spending time differently.

Did one class of SQL regress?

If yes, the baseline helps you prove it quickly and then pivot into SQL-level analysis.

SQL*Plus - Run AWR compare-period report

-- From SQL*Plus, the script walks you through begin and end snapshots for both periods
@?/rdbms/admin/awrddrpt.sql

SQL - Programmatic compare report

SELECT *
FROM   TABLE(
         DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT(
           dbid1      => <dbid>,
           inst_num1  => 1,
           bid1       => <good_begin_snap>,
           eid1       => <good_end_snap>,
           dbid2      => <dbid>,
           inst_num2  => 1,
           bid2       => <bad_begin_snap>,
           eid2       => <bad_end_snap>));

Recollect: A baseline is not the report. A baseline helps you choose the right comparison period so the report tells a more meaningful story.

Section 08

Views and administration you should know cold

If you want baselines to become an operational habit instead of a one-time trick, learn the key views and lifecycle procedures. These are the objects you return to repeatedly during routine use.

Object	What it tells you	Why it matters
DBA_HIST_WR_CONTROL	Snapshot interval, retention, and AWR control settings	Baseline strategy depends on retention depth.
DBA_HIST_SNAPSHOT	All snapshots and their time ranges	Used to find exact ranges before creating or comparing baselines.
DBA_HIST_BASELINE	Existing baselines and core metadata	Your day-to-day inventory of baseline objects.
DBA_HIST_BASELINE_DETAILS	More detailed baseline information including moving window size	Helpful when managing the moving baseline.
DBA_HIST_BASELINE_TEMPLATE	Single and repeating template definitions	Essential for automated baseline generation.

SQL - Baseline inventory query

COLUMN baseline_name FORMAT a30
COLUMN baseline_type FORMAT a18

SELECT baseline_id,
       baseline_name,
       baseline_type,
       start_snap_id,
       start_snap_time,
       end_snap_id,
       end_snap_time,
       creation_time,
       expiration
FROM   dba_hist_baseline
ORDER BY baseline_id;

SQL - Rename or drop a baseline

BEGIN
  DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE(
    old_baseline_name => 'NORMAL_LOAD',
    new_baseline_name => 'NORMAL_LOAD_OLTP');

  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
    baseline_name => 'TEMP_TEST_BASELINE',
    cascade       => FALSE);
END;
/

Rename proactively. Baseline names such as baseline1 or good_period stop being helpful in six months. Names that encode workload identity - for example PAYROLL_CLOSE_Q1, MONDAY_OPEN_8AM, or POST_PATCH_STABLE - age much better.

Section 09

End-to-end demo: preserving a month-end batch baseline and comparing a later slowdown

This sample use case stays close to how baselines are actually used in real operations. Imagine an ERP database where the month-end batch close normally runs every last business day from 20:00 to 23:00. In April, users report that the same close runs much longer. The goal is to preserve a known-good March month-end period, verify it exists, and compare it with the degraded April run.

Demo principle

This demo uses real package procedures, dictionary views, and report mechanisms. Where results are environment-dependent, the article shows the exact verification query and the exact success condition to look for instead of inventing fake numbers.

Step 1: identify the “good” March snapshot range

SQL - Find month-end snapshots

SELECT snap_id,
       begin_interval_time,
       end_interval_time
FROM   dba_hist_snapshot
WHERE  begin_interval_time BETWEEN
         TO_DATE('31-MAR-2026 19:00', 'DD-MON-YYYY HH24:MI') AND
         TO_DATE('31-MAR-2026 23:59', 'DD-MON-YYYY HH24:MI')
ORDER BY snap_id;

Choose the snapshot IDs that cover the normal batch window completely. The correct result is not a specific snap ID number - that depends on your system - but a continuous set of snapshots that brackets the batch period you want to preserve.

Step 2: create the static baseline for the known-good month-end

SQL - Preserve known-good month-end

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id => <good_begin_snap>,
    end_snap_id   => <good_end_snap>,
    baseline_name => 'MONTH_END_CLOSE_MAR2026',
    expiration    => 365
  );
END;
/

A successful execution returns the normal PL/SQL completion message. The important verification step is next: confirm the baseline exists and that its snapshot range is what you intended.

SQL - Verify baseline row

SELECT baseline_name,
       baseline_type,
       start_snap_id,
       end_snap_id,
       start_snap_time,
       end_snap_time,
       expiration
FROM   dba_hist_baseline
WHERE  baseline_name = 'MONTH_END_CLOSE_MAR2026';

The correct result here is one row with baseline type STATIC and the snapshot/time range you intended to preserve.

Step 3: find the degraded April period

SQL - Find the bad period snapshots

SELECT snap_id,
       begin_interval_time,
       end_interval_time
FROM   dba_hist_snapshot
WHERE  begin_interval_time BETWEEN
         TO_DATE('30-APR-2026 19:00', 'DD-MON-YYYY HH24:MI') AND
         TO_DATE('30-APR-2026 23:59', 'DD-MON-YYYY HH24:MI')
ORDER BY snap_id;

Step 4: run an AWR compare-period report

SQL*Plus - Compare good and bad periods

@?/rdbms/admin/awrddrpt.sql

When prompted, choose the “good” March begin/end snapshots for one side of the compare and the “bad” April begin/end snapshots for the other. The result is a compare report showing where DB time, waits, SQL, or I/O behavior changed.

Step 5: verify the baseline still matches your intended comparison period

SQL - Cross-check snapshots from baseline

SELECT b.baseline_name,
       b.start_snap_id,
       s1.begin_interval_time AS good_begin_time,
       b.end_snap_id,
       s2.end_interval_time   AS good_end_time
FROM   dba_hist_baseline b
       JOIN dba_hist_snapshot s1 ON s1.snap_id = b.start_snap_id
       JOIN dba_hist_snapshot s2 ON s2.snap_id = b.end_snap_id
WHERE  b.baseline_name = 'MONTH_END_CLOSE_MAR2026';

Step 6: create a template so future month-end windows are preserved automatically

SQL - Schedule future month-end capture windows

-- For recurring weekly or periodic windows, use templates instead of manual baseline creation
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
    day_of_week          => 'THURSDAY',
    hour_in_day          => 20,
    duration             => 180,
    start_time           => TO_DATE('07-MAY-2026 20:00', 'DD-MON-YYYY HH24:MI'),
    end_time             => TO_DATE('31-DEC-2026 20:00', 'DD-MON-YYYY HH24:MI'),
    baseline_name_prefix => 'THU_BATCH_CLOSE',
    template_name        => 'THU_BATCH_TEMPLATE',
    expiration           => 90
  );
END;
/

What counts as a successful demo outcome?

Success is not a made-up report ratio. Success is: the baseline exists, the snapshot range is correct, the compare-period report is generated, and the comparison now has a trustworthy “good period” anchor for investigation.

Recollect: The most accurate end-to-end baseline demo is one that shows exact commands plus exact verification points, not invented output numbers.

Section 10

Knowledge check

These questions are designed to build judgment, not just recall. Submit answers and read the explanations carefully.

Q1. What is the main practical value of an AWR baseline?

It increases CPU speed during peak hours.

It preserves a meaningful AWR period so you can compare future behavior against it.

It disables AWR purging entirely.

It replaces AWR reports with ADDM findings.

Correct answer: Baselines preserve context. Their real power is that they give you a trusted reference period for future comparison and stop that period from disappearing through normal AWR purge activity.

Q2. Why must AWR retention be considered before planning a longer moving-window baseline?

Because moving-window baselines are unrelated to retention.

Because snapshot interval controls the baseline name.

Because the moving-window size cannot exceed the amount of retained AWR history.

Because all static baselines are deleted when retention changes.

Correct answer: Retention sets the ceiling. You cannot maintain a rolling historical reference period that is longer than the underlying AWR history you actually keep.

Q3. When is a template better than a manual static baseline?

When the workload window repeats regularly and you want Oracle to preserve future occurrences automatically.

When no AWR snapshots exist.

Only when the database is in RAC.

Only when expiration is NULL.

Correct answer: Templates automate recurring windows. They are ideal when the same workload pattern matters week after week or month after month.

Q4. Which statement about comparing a degraded period to a baseline is the healthiest mindset?

Only compare average CPU usage; everything else is noise.

Any difference automatically proves a bug.

If elapsed time is lower, there is nothing more to inspect.

Ask what changed relative to a known-good period, then inspect DB time, waits, SQL, and load shape together.

Correct answer: Comparison is interpretive. The baseline gives you a better reference point, but you still need to understand whether the load itself changed and where time moved.

Q5. What is the safest way to verify that a newly created static baseline is correct?

Assume success because the PL/SQL block completed.

Query DBA_HIST_BASELINE and cross-check the start and end snapshots and times.

Restart the database and see whether the name still exists.

Only look at V$INSTANCE.

Correct answer: Trust, then verify. The package call may succeed, but the baseline is only useful if it covers the exact period you intended.

Q6. Why are clear baseline names so important?

Because unclear names make the package run slower.

Because Oracle requires names to include the instance number.

Because future troubleshooting is easier when names encode workload identity, time, or business context.

Because baseline names control snapshot interval.

Correct answer: Good names preserve meaning. Names like MONTH_END_CLOSE_MAR2026 tell a much better story six months later than baseline1.

Q7. What is the most accurate statement about the end-to-end month-end demo in this guide?

It shows verified commands and verification points, while avoiding invented report numbers that would vary by environment.

It proves every month-end slowdown has the same root cause.

It guarantees the compare report will always show higher CPU in the bad period.

It does not require AWR snapshots to exist.

Correct answer: Accurate demos avoid fake certainty. The goal is to give you exact commands and exact success checks without pretending every environment will produce identical numbers.