How to generate Oracle AWR Difference Report (AWR Diff Report) ?

 You can generate an AWR Difference Report (AWR Diff Report) using Oracle's standard script awrddrpt.sql available under the Oracle Home directory.

1. Connect as SYSDBA

sqlplus / as sysdba

or

sqlplus sys/password as sysdba

---

2. Run the AWR Difference Report Script

The script is located at:

$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Execute it from SQL*Plus:

SQL> @?/rdbms/admin/awrddrpt.sql

(? automatically resolves to ORACLE_HOME)

---

3. Provide Required Inputs

The script will prompt for:

Source Database

Select:

• DBID
• Instance Number
• Begin Snapshot ID
• End Snapshot ID

Example:

Enter value for dbid : 123456789

Enter value for inst_num : 1

Enter value for begin_snap : 100

Enter value for end_snap : 101

Target Database / Comparison Period

Provide another snapshot range:

Enter value for dbid : 123456789

Enter value for inst_num : 1

Enter value for begin_snap : 200

Enter value for end_snap : 201

This can be:

• Same database, different time period
• Different RAC instance
• Different database (if AWR data exists)

---

4. Choose Report Format

The script will ask:

Specify the Report Type

Enter 'html' for an HTML report

Enter 'text' for plain text report

Example:

html

---

5. Specify Output File

Example:

Enter value for report_name: awr_diff_peak_vs_normal.html

The report gets generated in the current SQL*Plus working directory.

---

Finding Snapshot IDs

Before running the report, identify snapshot IDs:

SELECT

snap_id,

begin_interval_time,

end_interval_time

FROM dba_hist_snapshot

ORDER BY snap_id;

For RAC:

SELECT

instance_number,

snap_id,

begin_interval_time

FROM dba_hist_snapshot

ORDER BY instance_number, snap_id;

---

Useful Report Types

Compare Good vs Bad Performance

Example:

Period	Snap Range
Good Performance	100-101
Slow Performance	200-201

Generate AWR Diff Report to identify:

• SQL elapsed time differences
• Wait event changes
• CPU usage increases
• I/O bottlenecks
• Execution plan regressions

---

Non-Interactive Generation

You can also call the package directly:

SELECT *

FROM TABLE(

dbms_workload_repository.awr_diff_report_html(

123456789,

1,

100,

101,

123456789,

1,

200,

201

));

For text format:

SELECT *

FROM TABLE(

dbms_workload_repository.awr_diff_report_text(

123456789,

1,

100,

101,

123456789,

1,

200,

201

));

Quick Check of Available AWR Scripts in Oracle Home

cd $ORACLE_HOME/rdbms/admin

ls awr*.sql

Common scripts:

awrrpt.sql -- Single AWR report

awrgrpt.sql -- RAC Global AWR report

awrddrpt.sql -- AWR Difference report

awrgdrpt.sql -- Global AWR Difference report (RAC)

ashrpt.sql -- ASH report

ashrpti.sql -- ASH report for specific instance

For a RAC environment, use awrgdrpt.sql instead of awrddrpt.sql when you want to compare cluster-wide performance across snapshot ranges.