HOL 18c AWR Diff - Upgrade Your Database: Index

HOL 18c AWR Diff – Upgrade your Database about:reader?url=https://mikedietrichde.com/hands-on-lab/hol-18...
mikedietrichde.com
HOL 18c AWR Diff – Upgrade your

Database
5-6 minutes
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
2. AWR Diff Report
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/
1 of 7 24/06/18, 15:54
@/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
2 of 7 24/06/18, 15:54
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
3 of 7 24/06/18, 15:54
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:

Hit RETURN
69
Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance
Host
------------ ---------- --------- ----------
------
* 72245725 1 UPGR UPGR
4 of 7 24/06/18, 15:54
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
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
5 of 7 24/06/18, 15:54
74 19 Jun 2018
11:15 1
Specify the Second Pair of Begin and End Snapshot

Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2:

Hit RETURN
72
Enter value for end_snap2:

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
6 of 7 24/06/18, 15:54
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
Edit
7 of 7 24/06/18, 15:54

HOL 18c AWR Diff - Upgrade Your Database: Index

Uploaded by

Copyright:

Available Formats

HOL 18c AWR Diff - Upgrade Your Database: Index

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

HOL 18c AWR Diff - Upgrade Your Database: Index

Uploaded by

Copyright:

Available Formats

HOL 18c AWR Diff – Upgrade your Database about:reader?url=https://mikedietrichde.com/hands-on-lab/hol-18...

HOL 18c AWR Diff – Upgrade your

2. AWR Diff Report

At first, create an AWR snapshot BEFOREload, then AFTERload,

Please NOTE down the snapshot number.

Now run the HammerDB load again as you did in:

Please NOTE down the snapshot number.

AWR Diff Report

In the AWR Diff Report you will compare a snapshot period

Call the AWR Diff script:

And then interactively:

Specify the Report Type

Instances in this Workload Repository schema

Database Id and Instance Number for the First Pair

Specify the number of days of snapshots to choose

Enter value for num_days:

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

UPGR UPGR 64 18 Jun 2018

Specify the First Pair of Begin and End Snapshot

Type: 68 <== Your actual snapshot ID may be different –

Enter value for end_snap: 69

Type: 69 <== Your actual snapshot ID may be different –

Instances in this Workload Repository schema

Database Id and Instance Number for the Second

Using 72245725 for Database Id for the second

Specify the number of days of snapshots to choose

Enter value for num_days2:

Listing the last day's Completed Snapshots

Specify the Second Pair of Begin and End Snapshot

Type: 72 <== Your actual snapshot ID may be different –

Enter value for end_snap2:

Type: 73 <== Your actual snapshot ID may be different –

Specify the Report Name

Enter value for report_name:

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

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

Compare things such as Wait Events etc. Watch out for

===> NEXT: SQL Performance Analyzer

You might also like