Re: Redo per transaction inconsistency when running SLOB

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Thu, 23 Apr 2020 14:24:20 +0100
Message-ID: <CAGtsp8m2D0YHdkej=0up9f7MAHvVMdMA364POUg0=84K-iJU+g_at_mail.gmail.com>



I think the first place to look would be the AWR Instance Activity for the underlying figures:

redo size
user commit
user rollbacks

The redo size per transaction figure in the load profile is "redo size" / ("user commits" + "user rollbacks") - so you need a better handle on whether or not there is a real difference in the total redo size, and/or in the number of commit/rollbacks.

If no other figures leap out as interesting when you're scanning the Instance Activity I would add a logoff trigger to the SLOB processes (if this can be done) to make them record their redo size and commits/rollback session stats to see if they were actually doing something differently.

Regards
Jonathan Lewis

<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Thu, Apr 23, 2020 at 12:29 PM Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> wrote:

> Hi
>
>
>
> We have some new hardware which seems slower from an IO perspective than
> the old hardware, so I downloaded SLOB to investigate, and ran it on two
> databases which are copies of each other. Both on RHEL7 OS with Oracle
> 12.2.0.1 and the April critical patch. Both are in archive log mode.
> Looking into the AWR, it seems the slower one on the new hardware is
> generating more redo per transaction than the old one. I set up SLOB
> identically in both databases (./setup.sh IOPS 8) and ran it identically
> (./runit.sh 8). The tablespace is a smallfile and I specified it’s
> location, but otherwise I used the defaults. I set up the admin user as
> “sys/sys as sysdba” and for this run changed the run time to 900 seconds.
>
>
>
> There are some differences in configuration. The slow one is supposed to
> be more like production than the faster one, so it has:
>
> · bigger buffers the log buffer is 10M as opposed to 5M in the
> faster one
>
> · uses huge pages, where the fast one doesn’t
>
> · LOST_WRITE_PROTECT is NONE in both environments.
>
> · a physical standby database. The faster one doesn’t
>
> There are applications running against these databases, but I can’t see
> that there was any application SQL that would make this much difference.
>
>
>
> I am looking in the load profile section of the AWR report that is
> generated. Redo size per transaction is 54K for the faster one, and 203k
> for the slower one, so about 4x as much. What could be causing this?
>
> I’d be grateful for some pointers as to where I can look to see what is
> causing the extra redo.
>
>
>
> Thanks
>
>
>
> PaulH
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 23 2020 - 15:24:20 CEST

Original text of this message