Re: AWR report ... what does W/A MB processed represent?
Date: Tue, 26 Mar 2013 18:12:12 -0000
Message-ID: <E070F82A3DD94F269C9BFA9C50833268_at_Primary>
John,
Regarding drop in w/a mob - you said you'd got a larger pga aggregate target. This may mean that more of your sorts complete in memory, and multipasses drop to one-pass. If Oracle is doing something funny counting bytes (in an out) then this might explain a signficant drop. For example, what if on a one-pass sort Oracle counts the data as it reads it, counts it again as it passes it do disc, counts it again as it reads it back and counts it again as it merges and delivers the streams. (I haven't checked this, but if Oracle's managing to double count on an optimal sort, how wrong could it go on a one-pass !) This change "sort 5MB of data" into 20MB processed.
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 6:06 PM Subject: Re: AWR report ... what does W/A MB processed represent?
Thanks Jonathan:
For parses if this is just some kind of change in how oracle counts statistics for the parses between 11.1 and 11.2 ( now ignoring session_cached_cursor hits ? ) the change in the values reported makes sense to me.
If that is correct then that change also changes dramatically the ratios reported immediately after ( Execute to Parse / Parse CPU to Parse Elapsed% ).
For W/A MB processed:
Starting to think that perhaps in 11.1 Oracle was not dividing the value
for "bytes processed" by 1024*1024 ( was not calculating MB correctly ) in
the AWR report and has now fixed the calculation?
- 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: hurleyjohnb_at_yahoo.com, oracle-l_at_freelists.org
> Date: Tuesday, March 26, 2013, 1:42 PM
>
> I believe it's the change in the "bytes processed"
> statistics from
> v$pgastat;
> I ran up a quick check to test that, and it seems to be
> true; however, as
> so often happens when you look too closely the "bytes"
> processed is a
> little bizarre.
> I sorted 44,000 rows of data in memory, and got a report
> that I had needed
> about 7MB of memory to complete the sort
> (v$sql_plan_statistics_all and
> 10032 trace).
> The "bytes processed" figure changed by 14MB - it looks like
> Oracle "count
> them out, then counts them all back in" when it's reporting
> bytes
> processed.
> I didn't try to find out what happened if it did a multipass
> spill to disc.
>
>
> The parse count may simply be a change in accounting - I
> think I've got a
> note somewhere that parse calls that became session cursor
> cache hits no
> longer get recorded under the parse count. (Or maybe
> it's some other
> specific case use - I can't remember the details, but I know
> I spotted some
> such anomaly).
>
>
> 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>
> Sent: Tuesday, March 26, 2013 4:44 PM
> Subject: AWR report ... what does W/A MB processed
> represent?
>
>
> | Can anyone point to or give an explanation of what this
> AWR report
> statistic means and/or where it comes from please?
> |
> | We just moved an OLTP database from 11.1 to 11.2 and my
> numbers in that
> statistic have decreased dramatically ( down to 1 MB per
> second ).
> |
> | Parses per second also dropped dramatically.
> |
> | The change in the database server ( 11.2.0.3.4 from
> 11.1.0.7.12 ) also
> has additional memory in the SGA and PGA. This change
> was made in
> conjunction with an application server upgrade ( went from
> 10.2.0.4 client
> to 11.2.0.3 client ).
> |
> | The changes are all extremely positive just trying to sort
> out why so
> much better!
> |
> |
> | --
> | http://www.freelists.org/webpage/oracle-l
> |
> |
>
>
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-lReceived on Tue Mar 26 2013 - 19:12:12 CET