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!!

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: