Oracle AWR Compare Periods Report :

Diagnosing Database Performance Degradation

Introduction :

AWR Compare Periods Report (officially “AWR Compare Periods”) is part of Oracle’s Diagnostics Pack. Its purpose is clear: take two time windows, compare the key performance data from both periods, and show you exactly which SQL statements, which wait events, and which I/O paths have changed between the two periods .

When a database’s performance gradually degrades, or when performance drops sharply at a certain point, ADDM (from the previous topic) tells you “what the problem is now,” but it doesn’t tell you “what changed compared to when things were good.” AWR Compare Periods fills exactly this gap.

Theoretical Foundation :

Relationship with Previous Tools :

AWR Compare Periods relies on AWR snapshots. You specify two snapshot ranges: one for the “baseline period” (when performance was good), and one for the “problem period” (when performance degraded). The report will calculate the differences in each metric and rank them in descending order of the degree of change .

Key Sections of the Report :

The core sections of AWR Compare Periods include:

1. SQL Statistics Comparison

The report lists the top 10 SQL statements ranked by DB Time difference, CPU Time difference, Buffer Gets difference, Physical Reads difference, Execution Count difference, and Parse Count difference. If a SQL statement consumed a large amount of DB Time during the problem period but almost no DB Time during the baseline period, it is most likely the SQL statement that caused the degradation .

2. Instance Activity Statistics

Compares the differences in key instance-level statistics between the two periods. This helps you determine whether the problem is at the system level (e.g., a sudden surge in hard parses) or at the SQL level.

3. I/O Statistics

Compares I/O changes at the tablespace and data file levels. If I/O on a certain data file suddenly spikes, it may mean that a missing index has caused a full table scan, or a hot table has experienced unexpected growth .

4. Wait Statistics

Compares the differences in wait events between the two periods. This section directly tells you “what the sessions are now waiting for.”

Step 1 – Determine Two Time Windows :

Before running the report, you first need to identify:

• Baseline period: The time window when performance was normal. Usually the same time the day before, or a period you clearly remember as “when it was fast.”

• Problem period: The time window when performance degraded.

The two windows should be as similar as possible in length and business type (e.g., both are weekday mornings, or both are month-end processing) to ensure the comparison is meaningful.

Step 2 – Generate the Report Using SQL*Plus :

Command-Line Method :

Connect to the database and run:

@?/rdbms/admin/awrddrpt.sql

The script will prompt you to enter the following information step by step :

1. Report type: Choose html or text

2. Number of days to list snapshots (e.g., 2 days)

3. Start snapshot ID and end snapshot ID for the first period

4. Start snapshot ID and end snapshot ID for the second period

5. Report name (can be left to default)

How to Find Snapshot IDs

If you’re not sure which snapshot ID corresponds to which time, you can check first:

SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY;

Choose the snapshot IDs that match your time windows.

Step 3 – Read the Report: Start with SQL Statistics :

After the report is generated, the first thing to look at is the Top 10 SQL Comparison by DB Time section.

Logic for Reading It :

SQL statements in this section are ranked by DB Time difference. The SQL statement at the top is the one that consumed the most DB Time during the problem period compared to the baseline period. If you see:

This SQL statement consumed almost no resources during the baseline period but consumed 1200 seconds during the problem period. It is the primary suspect.

Common Causes

• Execution plan changed (statistics expired, index was dropped, bind variable peeking triggered a different plan)

• Data volume surged (a certain table suddenly grew significantly, causing the original plan to become ineffective)

• SQL statement was modified (developer made changes)

• Parameter changes (optimizer parameters, cursor_sharing, etc.)

After identifying the SQL statement, you can use DBMS_XPLAN.DISPLAY_AWR to check the execution plans for both periods, or use SPM (from the previous topic) to fix the plan.

Step 4 – Read Wait Statistics :

The Wait Statistics section in the report shows the difference in wait events between the two periods.

What to Look For :

• db file sequential read surging significantly: Usually means an index scan has become inefficient, or a full table scan has occurred (if it’s db file scattered read).

• enq: TX – row lock contention surging: There’s a blocking problem (review the blocking/deadlock topics from earlier).

• log file sync surging: Commit frequency has increased, or redo write performance has degraded.

Step 5 – Read I/O Statistics :

The I/O Statistics section compares I/O at the tablespace and data file levels.

How to Use It :

If you see that a certain data file’s normalized I/O has increased significantly during the problem period, combined with the SQL Statistics section, you can pinpoint which table’s access pattern has changed.

For example: a full table scan on the ORDERS table increased significantly, causing I/O on the tablespace containing the ORDERS table to spike. This means either an index was dropped, or statistics caused the optimizer to abandon the index.

Comparison with SPA :

Practical Considerations :

Snapshot Retention :

AWR’s default retention period is 8 days . If you need to compare with a time period from a month ago, you must ensure that snapshots from that period are still present, or use an AWR Baseline to fix snapshots from specific time periods for long-term retention.

Time Window Selection :

The closer the business characteristics of the two time windows, the more reliable the comparison. Comparing “Monday morning” with “Saturday early morning” is meaningless. It’s best to compare weekday mornings with weekday mornings, or month-end with the previous month-end.

Don’t Rely Solely on DB Time :

If the problem period has higher concurrent sessions than the baseline period, DB Time itself will be higher. In this case, look at per-execution averages rather than total values, or look at CPU Time and Buffer Gets differences to cross-validate.

Summary :

AWR Compare Periods is one of the most practical tools for diagnosing “why did it suddenly slow down.” It doesn’t predict the future like SPA, nor does it automatically analyze like ADDM. It places two time periods side by side and lets you see for yourself what changed.