11.2 changes in AWR report ( counting parses and W/A MB processed ) ...

From: John Hurley <hurleyjohnb_at_yahoo.com>
Date: Tue, 26 Mar 2013 16:09:33 -0700 (PDT)
Message-ID: <1364339373.56379.YahooMailClassic_at_web181202.mail.ne1.yahoo.com>



FYI: Same approximate workload one week in between on 11.1 and now 11.2.

Oracle definitely has the calculation hosed up for W/A MB processed in 11.1 and as noted earlier ... looks like they are now counting parses differently in 11.2 ( not counting hits in session_cached_cursors ).

11.1.0.7.12 ...

Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):                1.4                0.0       0.00       0.00
       DB CPU(s):                0.7                0.0       0.00       0.00
       Redo size:          109,808.0            2,749.8
   Logical reads:           13,060.0              327.0
   Block changes:              725.7               18.2
  Physical reads:              593.6               14.9
 Physical writes:               44.2                1.1
      User calls:            4,492.2              112.5
          Parses:            1,813.0               45.4
     Hard parses:                2.0                0.1
W/A MB processed:        1,089,100.0           27,272.6
          Logons:                1.7                0.0
        Executes:            1,871.9               46.9
       Rollbacks:                0.2                0.0
    Transactions:               39.9
    

11.2.0.3.5 ...

Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):                0.7                0.0       0.00       0.00
       DB CPU(s):                0.6                0.0       0.00       0.00
       Redo size:           98,996.9            2,831.7
   Logical reads:           11,926.5              341.2
   Block changes:              618.0               17.7
  Physical reads:              310.8                8.9
 Physical writes:               46.6                1.3
      User calls:            4,770.8              136.5
          Parses:               98.2                2.8
     Hard parses:                2.0                0.1
W/A MB processed:                1.0                0.0
          Logons:                1.7                0.1
        Executes:            1,998.8               57.2
       Rollbacks:                0.1                0.0
    Transactions:               35.0

  • On Tue, 3/26/13, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

> From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
> Subject: Re: AWR report ... what does W/A MB processed represent?
> To: "John Hurley" <hurleyjohnb_at_yahoo.com>, oracle-l_at_freelists.org
> Date: Tuesday, March 26, 2013, 3:38 PM
>
>
> John,
>
> A quick sanity check - if you look at the workarea histogram
> figures on the
> report then W/A MB probably ought to be in the same ballpark
> as the
> mid-point of each range multiplied by the number of
> executions.for the
> range.  (Though maybe that approximation will be
> spoiled by one-pass and
> multipasses.)
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com/all-postings
>
> Author: Oracle Core (Apress 2011)
> http://www.apress.com/9781430239543
>
> ----- Original Message -----
> From: "John Hurley" <hurleyjohnb_at_yahoo.com>
> To: <oracle-l_at_freelists.org>;
> "Jonathan Lewis"
> <jonathan_at_jlcomp.demon.co.uk>
> Sent: Tuesday, March 26, 2013 7:32 PM
> Subject: Re: AWR report ... what does W/A MB processed
> represent?
>
>
> | Thanks Jonathan but while I do have a potentially larger
> PGA we seem to
> be using about as much as before.
> |
> | I am now pretty convinced that 11.1.0.7 is just "not
> dividing" the (
> bytes processed from v$pgastat ) by 1024*1024 for the AWR
> report but
> 11.2.0.3 has this calculation fixed and is calculating MB
> correctly.
> |
> | My last 11.1 system is migrated but I was getting numbers
> in there that
> were huge.
> |
> | Can some other people do some double checking please?
> |
> | Thanks John
> |
> |
> | -----
> | No virus found in this message.
> | Checked by AVG - www.avg.com
> | Version: 2013.0.2904 / Virus Database: 2641/6202 - Release
> Date: 03/24/13
> |
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 27 2013 - 00:09:33 CET

Original text of this message