Re: High Number of undo writes

From: Purav Chovatia <puravc_at_gmail.com>
Date: Wed, 16 May 2012 13:44:10 +0530
Message-ID: <CADrzpjGnE0NVCV4iDz+gGHt4iWYJOrymSWB+n3TAS0EnXBKgaQ_at_mail.gmail.com>



Hi,
More details about the setup and observations: SetupA / SetupB:
DB server: Intel 2.80GHz / 3.33 GHz
Oracle: 10.2.0.4 / 10.2.0.5
Storage: HP P2000 G3 / Sun 2540 M2
Volume data: 12x146 RAID10 / 10x300 RAID 10 Volume redo: 2x146 RAID1 / 2x300 RAID1

SGA and all other memory params are same or very close except log buffer (because it is a derived value) at 6M in A and 14M in B.

The observation is that the data volume is experiencing 10-15 times more IOPS in B as compared to A. Total data written in A and B is same. And hence the avg IO size in B is 1/10th of that in A. So I looked at the AWR report to check if Oracle indeed do more IOPS or was it something else and that is when I found that undo writes are far more in B. And this behaviour is consistent in 5 runs of 1 hour each done till now. So dont understand why should Oracle do more writes for the same amount of changes. In this context, I observed the foll. but cant make sense out of it (A / B):

physical write IO requests               464,606 /    3,315,359
physical write total IO requests          14,271,435 /  10,307,912
physical write total multi block requests  13,844,347 /    7,311,634
physical writes                    5,050,624 /    4,993,546
So as we see, the major difference is in physical write IO requests but I cant understand why? If we see the next 2 stats, then IOPS in B should have been less as compared to A.

Another observation is that the redo volume is experiencing half the IOPS in B as compared to A. Redo per sec in B is 35% more than that in A and that is very surprising. For a moment I thought that it could be because of redo wastage but that is not so. And rightly so because the IOPS (i.e. the redo writes on disk) have actually halved and redo wastage would increase only if there are more writes. [the redo writes in this sentence is not the "redo write" statistics of AWR report. I am referring to the writes on the redo volume].

I was expecting similar performance on both setups because the characteristics of the storage that matter are largely same i.e. both storage have dual controller with both volumes having write cache enabled. The RAID configuration is exactly the same. Disks are of same speed and type (make is different).

Thanks.

On Tue, May 15, 2012 at 9:10 PM, Purav Chovatia <puravc_at_gmail.com> wrote:

> Hi,
>
> We are running a workload on 2 different setups to compare performance.
> And we have some very unusual observations for exactly the same workload.
> There are a few differences between the 2 setups, but none of those are
> able to justify the difference.
>
> I will provide more details later (sorry but got to leave), but one major
> difference is that setupA runs 10.2.0.4 whereas setupB runs 10.2.0.5. And
> the observation is that in B, the no. of writes to undo tablespace is
> comparatively very high (Av writes/s is 57 in A and 854 in B). Is this
> observed previously by anybody? There are other unusual differences but
> those seem to be because of these high undo writes.
>
> Thanks.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 16 2012 - 03:14:10 CDT

Original text of this message