HOL 18c AWR Diff

In this part of the lab you will create AWR diff reports. Those reports give you a first indication about issues you may see (or performance improvements). It is important to compare periods which had roughly the same load and duration.

Index

1. Generate Load

. upgr18
cd /home/oracle/scripts

sqlplus / as sysdba

At first, create an AWR snapshot BEFOREload, then AFTERload, and note down the snapshot numbers again.

@/home/oracle/scripts/snap.sql

Please NOTE down the snapshot number.

Now run the HammerDB load again as you did in:
https://mikedietrichde.com/hands-on-lab/hol-18c-load/

@/home/oracle/scripts/snap.sql

Please NOTE down the snapshot number.

AWR Diff Report

In the AWR Diff Report you will compare a snapshot period BEFORE upgrade to a snapshot period AFTER upgrade.

Call the AWR Diff script:

@?/rdbms/admin/awrddrpt.sql

And then interactively:

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

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'
Enter value for report_type:

Hit RETURN

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num	DB Name      Instance	  Host
------------ ---------- ---------    ----------   ------
* 72245725	 1	UPGR	     UPGR	  localhost.lo

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using	72245725 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  without
specifying a number lists all completed snapshots.


Enter value for num_days:

Type: 2
Hit RETURN

Now you need to define the first snapshot interval – please fill in the snapshot IDs you have noted down during the first HammerDB run:

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name	  Snap Id	Snap Started	Snap Level
------------ ------------ ---------- ------------------ ----------

UPGR	     UPGR		 64  18 Jun 2018 11:57	  1
				 65  18 Jun 2018 17:18	  1
				 66  18 Jun 2018 17:32	  1
				 67  18 Jun 2018 17:55	  1
				 68  18 Jun 2018 19:00	  1
				 69  18 Jun 2018 20:00	  1
				 70  18 Jun 2018 21:00	  1
				 71  18 Jun 2018 23:17	  1
				 72  19 Jun 2018 00:25	  1


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

Type: 68        <== Your actual snapshot ID may be different – please check your notes!
Hit RETURN

  68
First Begin Snapshot Id specified: 68

Enter value for end_snap: 69
First End   Snapshot Id specified:

Type: 69        <== Your actual snapshot ID may be different – please check your notes!
Hit RETURN

 69




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num	DB Name      Instance	  Host
------------ ---------- ---------    ----------   ------
* 72245725	 1	UPGR	     UPGR	  localhost.lo




Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using	72245725 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  without
specifying a number lists all completed snapshots.


Enter value for num_days2:

Type: 2
Hit RETURN

2

Listing the last day's Completed Snapshots
				 71  19 Jun 2018 00:25	  1
				 72  19 Jun 2018 09:36	  1
				 73  19 Jun 2018 11:00	  1
				 74  19 Jun 2018 11:15	  1


Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2:

Type: 72        <== Your actual snapshot ID may be different – please check your notes!
Hit RETURN

 72

Enter value for end_snap2:

Type: 73        <== Your actual snapshot ID may be different – please check your notes!
Hit RETURN

 73



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_68_1_72.html	To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name:

Hit RETURN

<<< Wait until the HTML output has been generated >>>

exit

In your xterm start Mozilla Firefox with the awr diff report:

firefox /home/oracle/scripts/awrdiff_1_68_1_72.html &

<== Your actual file name may be different depending on the snapshot IDs

Compare things such as Wait Events etc. Watch out for significant divergence between the two runs.

Browse also through the SQL statistics and see if you find remarkable differences between the two runs.

 


===> NEXT: SQL Performance Analyzer