Diff’ing AWR reports

November 4th, 2015

I don’t know if you are ever asked to compare to AWR periods. AWR period comparison is pretty easy if you have access to the two periods in the same AWR repository. AWR in the same repository can be compared with

SELECT * FROM TABLE(
    dbms_workload_repository.awr_diff_report_text(
         [db_id ],
         [instance id],
         120, -- start snapshot id
         121, -- end snapshot id
         [db_id of target,
         [instance id] ,
         122, -- start snapshot id
         123  -- end snapshot id));

and it can be run for single instance as

SELECT * FROM TABLE(
       dbms_workload_repository.awr_diff_report_text(
              (select dbid from v$database),
              1,
              120, -- start snapshot id
              121, -- end snapshot id
              (select dbid from v$database),
              1,
              122, -- start snapshot id
              123  -- end snapshot id));

This puts out a bit of a messy but useful report.

Here is an example of using it from Doug Burns

A similar, but cleaner simpler report that I partially designed can be run from OEM

but what if someone sends you two AWR reports? How can they be compared? These days I’m receiving at least a couple a week to compare, so I put together a compare script.
usage:

awrdiff.sh [type] file1 file2

where type

  • sevt = system events , ie wait events
  • stats = system statistics
  • load = load profile section
  • init = init.ora

for example

udiffs.sh sevt awr1.txt awr2.txt
... Statistics requested is load
... 1st report.txt
... 2nd report.txt

============================= load_psec ==============================
Name                               Ratio 1/2   Value1     Value2     Delta
Physical_reads:                   :    0.29:    266.20:    905.33:    639.13
Physical_writes:                  :    0.70:    585.32:    836.75:    251.43
Logons:                           :    0.86:      1.27:      1.48:      0.21
Logical_reads:                    :    1.04: 747342.68: 718259.28:  -29083.4
Redo_size:                        :    1.17:3516126.09:2995591.47:   -520535
Sorts:                            :    1.31:   3981.16:   3027.78:   -953.38
User_calls:                       :    1.38:  16476.53:  11948.71:  -4527.82
Parses:                           :    1.39:   4541.51:   3279.06:  -1262.45
Executes:                         :    1.44:  10619.75:   7350.55:   -3269.2
Hard_parses:                      :    1.89:      0.17:      0.09:     -0.08
Block_changes:                    :    2.38:  18936.62:   7942.27:  -10994.3

============================= load_ptrx ==============================
Name                               Ratio 1/2   Value1     Value2     Delta
Logons:                           :    0.00:      0.00:      0.01:      0.01
Physical_reads:                   :    0.11:      0.43:      3.94:      3.51
Physical_writes:                  :    0.26:      0.95:      3.64:      2.69
Logical_reads:                    :    0.39:   1218.11:   3123.70:   1905.59
Redo_size:                        :    0.44:   5730.99:  13027.80:   7296.81
Sorts:                            :    0.49:      6.49:     13.17:      6.68
User_calls:                       :    0.52:     26.86:     51.96:      25.1
Parses:                           :    0.52:      7.40:     14.26:      6.86
Executes:                         :    0.54:     17.31:     31.97:     14.66
Block_changes:                    :    0.89:     30.87:     34.54:      3.67

of course if your AWR report is an html file, then the current script won’t work. One workaround is to run the html through a text converter like
http://www.nirsoft.net/utils/htmlastext.html

Again the script is available here:  awrdiff.sh


This script was originally written back before statspack and was based on utlstat. If you look closely you will even see that the code is actually modified by Connie Dialeris, aka the writer of statspack. Before Connie put together statspack, she was looking at the usability of my scripts. I had written a couple of scripts, collect.sh and utlstat.sh. The idea of these scritps was to continuously looped collecting database statistics to flat files. Flat files were used to avoid an extra overhead of inserting data into the database. The data could be formatted the into a utlstat like report with utlstat.sh. Instead of writting a diff report on the raw data, I wrote a diff report that could be used for two different utlstat reports from customers as well as the raw data. This strategy was lucky because it was easy to update the diff script for statspack and AWR.


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. Foued
    | #1

    Thank you for sharing. The awrdiff.sh is indeed useful when we have just reports, but regarding the 2 first selects where we extract from dbms_workload_repository.awr_diff_report_text , isn’t it the same as running awrddrpt.sql or there are some differences in functionality ?
    Kind regards,
    Foued

  3. Tim carroll
    | #2

    I think the best utility would be one that reads the awr and tells the
    Dba what the issues are Not a giant report of data that a majority of
    Dba’s cannot understand

    I know spanalyzer is not bad. But. I think someone should have written
    This by now.

  4. | #3

    Hi,
    Very nice-appreciated.
    Brg
    Damir


7 + six =