01 November, 2014

StatsPack and AWR Reports -- Bits and Pieces -- 2

This is the second post in a series on reading StatsPack and AWR reports.
(The first is available here)


Comparing Reports :

Here are two 9.2 StatsPack extracts from one database:

Extract A  : 9.2 StatsPack
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             56,031.63              3,084.68
              Logical reads:             68,286.24              3,759.32
              Block changes:                314.88                 17.33
             Physical reads:                842.92                 46.40
            Physical writes:                134.76                  7.42
                 User calls:                271.32                 14.94
                     Parses:                146.46                  8.06
                Hard parses:                  7.37                  0.41
                      Sorts:                 93.83                  5.17
                     Logons:                  0.33                  0.02
                   Executes:                296.70                 16.33
               Transactions:                 18.16

Extract B : 9.2 StatsPack
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             89,615.76              2,960.48
              Logical reads:            210,302.81              6,947.42
              Block changes:                541.83                 17.90
             Physical reads:              1,465.04                 48.40
            Physical writes:                161.68                  5.34
                 User calls:                213.82                  7.06
                     Parses:                125.28                  4.14
                Hard parses:                  6.13                  0.20
                      Sorts:                104.31                  3.45
                     Logons:                  0.35                  0.01
                   Executes:                664.81                 21.96
               Transactions:                 30.27

Extract B shows a higher volume of activity -- Transactions per second, Redo Size per second, Logical Reads per second.
Actually, although these are both from the same database but for two different time-windows on different dates.  The fact that the time-windows aren't the same make the comparisons difficult.  Extract A is for an 11-hour window on day 1 and Extract B is for a 6-hour window on day 6.  The two windows aren't strictly comparable.  You have to be very careful when comparing StatsPack or AWR reports, even when from the same database if they don't cover comparable time-windows with similar loads.  The first Report includes non-peak hours, the second is for Peak hours only. The level of activity averaged out over 11hours that include non-peak hours isn't strictly comparable with a 6hour peak window.

Extract C : 10.2 AWR
Load Profile
Per SecondPer Transaction
Redo size: 520,776.15 50,948.36
Logical reads: 353,525.71 34,585.98
Block changes: 1,854.93 181.47
Physical reads: 14,285.23 1,397.55
Physical writes: 295.84 28.94
User calls: 1,265.14 123.77
Parses: 822.64 80.48
Hard parses: 15.40 1.51
Sorts: 168.09 16.44
Logons: 0.16 0.02
Executes: 1,040.31 101.78
Transactions: 10.22 

Extract D : 10.2 AWR
Load Profile
Per SecondPer Transaction
Redo size: 517,862.01 54,681.39
Logical reads: 288,341.09 30,446.12
Block changes: 1,879.27 198.43
Physical reads: 12,820.96 1,353.77
Physical writes: 323.90 34.20
User calls: 1,115.78 117.82
Parses: 719.39 75.96
Hard parses: 17.28 1.82
Sorts: 95.74 10.11
Logons: 0.15 0.02
Executes: 935.33 98.76
Transactions: 9.47 

Extracts C and D are from the same database for the same time-window on 2 consecutive workdays.  They are comparable.  A difference of 10% for some statistic may not be significant.  Understand that every business, every activity, every system will have some "normal" fluctuations day-to-day.

.
.
.



No comments: