How to generate awrdiff report
Performance issues often peepout if there are load on the database and bulk transactions pull the database back from moving front.It is a hectic task to identify the bottleneck of the issue.
So generating an AWRDIFF report would give us more insights on the bottom level of the database.It compares the two different time period between good and bad performance.
To generate an awrdiff report:
we can manually generate snapshots by using dbms_repository() package and generate the awr difference from script
Give the begin time snap id and end time snap id one at the time of performance issues and another at the normal working of database to compare the difference
Elapsed: 00:00:00.00
SQL> !uptime
22:23:03 up 1:18, 1 user, load average: 0.09, 0.12, 0.13
SQL> EXECUTE dbms_workload_repository.create_snapshot();
Elapsed: 00:00:00.85
SQL> @?/rdbms/admin/awrddrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
1559282684 1559282684 ORCL 1 1 orcl
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Type Specified: html
Elapsed: 00:00:00.00
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1559282684 1 ORCL orcl orcl
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1559282684 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 47 24 May 2020 21:17 1
48 24 May 2020 22:21 1
49 24 May 2020 22:22 1
50 24 May 2020 22:23 1
51 24 May 2020 22:32 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 47
First Begin Snapshot Id specified: 47
Enter value for end_snap: 51
First End Snapshot Id specified: 51
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1559282684 1 ORCL orcl orcl
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1559282684 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days2: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 47 24 May 2020 21:17 1
48 24 May 2020 22:21 1
49 24 May 2020 22:22 1
50 24 May 2020 22:23 1
51 24 May 2020 22:32 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 47
Second Begin Snapshot Id specified: 47
Enter value for end_snap2: 51
Second End Snapshot Id specified: 51
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_47_1_47.html To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrdiff_1_47_1_47.html
</body></html>
Report written to awrdiff_1_47_1_47.html
[oracle@orcl ~]$ locate awrdiff_1_47_1_47.html
/home/oracle/awrdiff_1_47_1_47.html
[oracle@orcl ~]$ ls -lrt *awr*
-rw-r--r-- 1 oracle oinstall 1084151 May 24 22:59 awrdiff_1_47_1_47.html <-----
Once it is generated export the html file using winscp to your local drive to analyse and interpret the performance stats of your database!!