Re: High Number of undo writes

  • From: Purav Chovatia <puravc@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 16 May 2012 13:44:10 +0530

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@xxxxxxxxx> 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.
>


--
//www.freelists.org/webpage/oracle-l


Other related posts: