Oracle DBA Lesson 95 Part 2 — Retain comparable performance evidence

A performance investigation needs evidence from the slow run and a useful reference from a good run. Design retention around when a problem will be noticed and how long the review takes. Choose the reference by the work performed and the environment, rather than by its short elapsed time alone.

Oracle DBA Lesson 95 Part 2 — Retain comparable performance evidence

Check the current policy

Read the automatic snapshot interval and ordinary retention for each repository database and container:

SELECT dbid, con_id, snap_interval, retention
FROM dba_hist_wr_control
ORDER BY dbid, con_id;

DBID identifies the repository database. CON_ID distinguishes the container context represented by the row: 0 applies to the whole container database or a non-container database, 1 to the root, and other values to individual pluggable databases. Verify the connection and repository scope before interpreting the values. Container visibility and automatic collection configuration affect what is available. A row does not prove that every expected capture succeeded.

SNAP_INTERVAL is the automatic capture interval. RETENTION is the period for which ordinary history is kept. Both columns have interval types. An interval displayed as +00008 00:00:00.0 represents eight days, and +00000 01:00:00.0 represents one hour. Display formatting can vary by client.

Oracle documents hourly snapshots and eight-day retention as defaults. Check the actual settings and the snapshots that remain available. Retention describes policy. It does not establish an exact purge time or guarantee a capture during every interval.

See DBA_HIST_WR_CONTROL.

AWR reports, DBA_HIST_WR_CONTROL, DBA_HIST_BASELINE, and DBMS_WORKLOAD_REPOSITORY require the relevant Diagnostics Pack rights. A feature setting does not establish contractual entitlement. Oracle documents specific exceptions for some other DBA_HIST_ views. Those exceptions do not include DBA_HIST_WR_CONTROL or DBA_HIST_BASELINE. When those rights are absent, keep your own timestamped measurements and avoid AWR- or ASH-derived measurements.

See licensing information.

Work out how long evidence must last

A month-end batch runs on day 0 and its slowdown is noticed on day 12. With eight-day ordinary retention, its unprotected captures may already have been purged.

For future runs, add the detection delay, the review time, and headroom:

Planning input	Assumed duration
Maximum detection delay	12 days
Investigation and review	7 days
Headroom	2 days
Proposed ordinary retention	21 days

12 + 7 + 2 = 21 days. The target covers that detection and review process. Increasing retention keeps available and future history for longer. It cannot recreate captures that have already been purged. Revisit the target when the detection delay or the review process changes.

Twenty-one days of ordinary retention does not by itself keep the previous month's good run available for a later monthly investigation. Retain a full workload cycle where that comparison is needed, or preserve the selected reference interval with a baseline. Oracle recommends considering at least a complete workload cycle when planning AWR retention.

See gathering database statistics.

Estimate capacity for the proposed retention

At one automatic capture per hour:

• 24 snapshots per day × 21 days = approximately 504 snapshots.
• At 15-minute intervals, the count becomes approximately 2,016 snapshots over 21 days, four times the hourly count.

Counts are approximate. Manual captures, collection gaps, and boundaries can change them. The storage cost per capture depends on the workload. Four times the snapshot count does not establish an exact fourfold storage requirement.

For a budget illustration, assume ordinary-history growth averages 2 MB per hourly capture. The estimate is 504 × 2 MB = 1,008 MB, before separately preserved baseline data, repository overhead, and reserve. This average is for the arithmetic. It is not an Oracle sizing guarantee. Measure representative repository growth and retained baseline usage, record the units and the measurement period, and have the storage owner review the estimate. Include collection overhead as well as disk capacity when considering a shorter interval.

Keep the capture interval fine enough to isolate the workload of interest, and keep retention long enough for detection and investigation. More frequent captures add detail and data.

Preserve a good reference interval

A fixed AWR baseline identifies an existing contiguous snapshot range and protects its snapshots from ordinary automatic AWR purging. Select a period in which the relevant workload performed well, while those snapshots still exist.

Read the saved ranges:

SELECT dbid, con_id, baseline_name, baseline_type,
       start_snap_id, end_snap_id, creation_time,
       expiration
FROM dba_hist_baseline
ORDER BY dbid, con_id, baseline_name;

Interpret the columns together:

• START_SNAP_ID and END_SNAP_ID identify the saved boundaries. Verify the associated times, database, instance, startup continuity, collection errors, and container scope using the snapshot inventory from Part 1.
• BASELINE_TYPE = 'STATIC' identifies a manually created fixed baseline. MOVING_WINDOW identifies the system's dynamic recent window, and GENERATED identifies a baseline created from a template.
• EXPIRATION is the baseline expiration interval in days. NULL means no expiration. It is not a count of days remaining. Use the creation time and the intended lifecycle when assessing whether the baseline will remain available for the investigation.
• The moving window follows recent AWR history. Its configured size must not exceed ordinary retention. It is not a permanently fixed copy of a chosen month-end run.

A deliberately selected good monthly interval might have a 60-day baseline expiration. That can cover a next monthly cycle of up to 31 days, 12 days of detection delay, seven days of review, and additional headroom. Check the actual calendar and the baseline creation time. A baseline with no expiration remains until removed, and it needs an owner and a capacity review.

An AWR statistical baseline preserves performance history. A SQL plan baseline belongs to SQL plan management and controls accepted execution plans. Do not substitute one for the other.

See DBA_HIST_BASELINE.

Create and remove a fixed baseline

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE names an existing contiguous snapshot range. The selected snapshot pair must already exist, and the baseline name must not already be in use. Creating a baseline changes repository retention behavior for that range and may increase retained space. Replace 700, 701, and 42424242 with the verified snapshot boundaries and database identifier:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id => 700,
    end_snap_id   => 701,
    baseline_name => 'BATCH_REFERENCE_TEST_01',
    dbid          => 42424242,
    expiration    => 60);
END;
/

EXPIRATION => 60 gives this baseline a finite expiration in days. Query the inventory again and verify the saved range and type. Creation does not prove that the workload is comparable or that the performance was good.

Remove that baseline definition with DROP_BASELINE:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
    baseline_name => 'BATCH_REFERENCE_TEST_01',
    cascade       => FALSE,
    dbid          => 42424242);
END;
/

CASCADE => FALSE removes the baseline definition without explicitly deleting its underlying snapshots. Once the baseline protection is removed, ordinary purge rules can apply unless another baseline still protects those captures. Verify that the definition is gone.

See DBMS_WORKLOAD_REPOSITORY.

Decide whether two runs are comparable

Sunday's light load processes 10,000 rows. Monday's month-end batch processes 1,000,000. Sunday's shorter elapsed time alone is a poor reference for the batch, because the volume and potentially the work differ.

Prefer a good run of the same task. Record:

Workload or environment property	What to compare
Input data and bind values	Similar volume, data distribution and selectivity
Executions and concurrency	Execution count, overlapping work and competing load
Service and scope	Same application service and consistent database/container coverage
Hardware and topology	CPU/storage capacity, instance placement and relevant changes
Execution plans	Access paths and plan changes that could affect the work
Cache state	Warm/cold conditions and why they differ
Metric definitions	Same elapsed-time boundary, logical-I/O definition and units

Compare elapsed time and logical reads with the execution volume and these conditions visible. Per-execution or per-row metrics can help, but dividing by volume does not remove differences in selectivity, plans, contention, or cache state. Record material differences and treat a proposed explanation as a hypothesis to test.

Retain measurements without Diagnostics Pack

Without Diagnostics Pack rights, retain timestamped workload measurements and the scripts that produced them, using facilities available outside the pack. A Statspack deployment may be another source; its installation, privileges, scope, and retention need their own documented setup. Do not substitute AWR or ASH data for those measurements.

For a bounded workload, keep:

• Known input data, bind values, and execution count, measured with a consistent elapsed-time method.
• If logical I/O is collected, the counter source, privilege, units, and whether the counter is per session, execution, or interval.
• Start and end timestamps and time zone; database, instance, and container identity; restart identity; and service.
• Input volume, concurrency, hardware, plan, and cache conditions, together with the measurement script and the saved measurements.

Repeat the measurement under comparable conditions. For cumulative counters, use compatible start and end captures within one uninterrupted instance or session. Do not subtract across resets. Preserve both inputs and the calculation. Choose the retention period from the detection delay, the review time, and headroom. Estimate storage for the expected record volume plus reserve, and preserve a useful good-run reference through the next comparison cycle.

Recap

Size ordinary retention from the detection delay, review time, and headroom, then estimate the snapshot count and repository space that target implies. A longer policy keeps future history. It does not rebuild snapshots that have already been purged. A fixed AWR baseline protects a selected existing range from ordinary automatic purging for its lifecycle. Comparability still depends on the work and the environment. Without Diagnostics Pack rights, keep timestamped measurements, their definitions, and the scripts that produced them.

Quiz

1. Is last Sunday's light-load interval automatically a valid baseline for Monday's month-end batch?

Yes, because it finished faster. Yes, because Sunday is the previous day. No; compare similar work and account for material environment differences. No; only the current run can be a baseline.

2. Detection can take 12 days, review seven days, and headroom two days. What ordinary-retention target follows from these assumptions?

Eight days. Twelve days. Nineteen hours. Twenty-one days, subject to capacity review.

3. What happens when retention is increased after the relevant snapshots have already been purged?

The old captures are rebuilt. Missing history remains missing; the new policy can retain available and future history longer. A fixed baseline automatically recreates the captures. The capture interval becomes zero.

4. What does a fixed AWR baseline do?

Protects a selected existing snapshot interval from ordinary automatic purging for its lifecycle. Proves that the chosen workload is comparable. Forces a chosen SQL execution plan. Eliminates repository storage cost.

5. What should be retained with pack-free elapsed-time measurements?

Only the fastest elapsed value. Only the date and a screenshot. Timestamps, identity/restart/scope, metric definitions, workload inputs and conditions, plus the measurement script. AWR-derived values relabelled as application measurements.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily