How To Compare Two AWR Reports?

Often we generate AWR reports for performance tuning our system. Mostly we have a baseline AWR when the system was performing fine and an AWR when the system was showing degraded performance. Comparing these two reports would give us an idea of where the problem could be and we can fine tune those parameters.
Lets compare AWR reports of two days , say 26 and 27th Aug for the time
period 1:00 to 2:00AM , we have 2 ways to do this from the sqlplus:

1. Using the default script awrddrpt.sql. Steps are shown below:

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

Enter value for report_type: html

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
DB11     DB1                  19676 26 Aug 2011 00:00      1
19677 26 Aug 2011 01:00      1
19678 26 Aug 2011 02:00      1

Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 19677
First Begin Snapshot Id specified: 19677

Enter value for end_snap: 19678
First End   Snapshot Id specified: 19678
Enter value for num_days2: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
DB11     DB1                19700 27 Aug 2011 00:00      1
19701 27 Aug 2011 01:00      1
19702 27 Aug 2011 02:00      1

Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap2: 19701
Second Begin Snapshot Id specified: 19701

Enter value for end_snap2: 19702
Second End   Snapshot Id specified: 19702

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_19677_1_19701.html

2. From the sqlplus using DBMS_WORKLOAD_REPOSITORY package.

Unlike the above method, we need to have the DBID, INSTANCE NUMBER, and the two paisr of snapshot ids.

To get the DBID

SQL> select dbid from V$database;

DBID
———-
1224460560

To get the instance ID

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
—————
1
To get snap shot ID and time period

SQL> select snap_id, snap_level, to_char(begin_interval_time, ‘dd/mm/yy hh24:mi:ss’) from dba_hist_snapshot where INSTANCE_NUMBER =1 order by 1;

Choose the snap shots we require. Here in this case , we need the snapshot ids of 26th 27th 1:00 AM to 2:00AM.
19678          1 26/08/11 01:00:36
19679          1 26/08/11 02:00:16
…
19702          1 27/08/11 01:00:11
19703          1 27/08/11 02:00:03

Now the spool the output in a spool file.

SQL> Spool path/file-name. html
SQL> select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html

(1224460560,1,19678,19679,1224460560,1,19702,19703));
SQL> spool off
The outfile clearly gives the difference and difference percentage of the first and second set of values.

Research and Article contribution by:  Divya