Re: Redo per transaction inconsistency when running SLOB

It looks like one of my replies to this got lost in transmission - can't even find it in my Sent box.

The point may come where you have to dump a couple of minutes of redo log file to see what operations are appearing as this may give some idea of what's going on.

There's an example of syntax for dumping a small volume based on SCNs. https://jonathanlewis.wordpress.com/2019/06/11/redo-dumps/

Regards
Jonathan Lewis

On Fri, Apr 24, 2020 at 10:41 AM Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> wrote:

> Thanks for the responses.
>
>
>
> I have a third “identical” database on another server which is also slow,
> so I thought I would play with that. It also generates more redo, and redo
> I/O seems to be the bottleneck.
>
>
>
> I took the pfile from the “fast” database and applied it to this one (Just
> had to change the name, and the location of some files). Redo is still
> generated at twice the rate. This rules out standby as a cause of the
> issue. I suppose I need to investigate Jonathans suggestion to see whether
> SLOB is doing something different between the two servers, but it really
> shouldn’t because the configuration is identical. Also any workload seems
> slower, and potentially redo I/O bound. I will have to do that on Monday.
>
>
>
> Nothing looks strange in AWRs calculations. Redo does seem to be the
> limiting factor, so if I could get the slow db to generate less redo like
> the fast one, it would have a big impact on performance. I checked the redo
> block size and it is 512 on both. Investigating whether 4K is better for
> the SAN is another rabbit hole, but isn’t a cause of difference here.
>
>
>
> Statistic
>
> Fast DB
>
> Slow DB
>
> Db block changes
>
> 79,758,656
>
> 27,719,598
>
> redo size
>
> 32,053,006,128
>
> 34,648,068,868
>
> user commits
>
> 592,015
>
> 170,871
>
> user rollbacks
>
> 2
>
> 1
>
>
>
>
>
> *From:* Mark W. Farnham <mwf_at_rsiz.com>
> *Sent:* 23 April 2020 19:14
> *To:* harel.safra_at_gmail.com; Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk>
> *Cc:* oracle-l_at_freelists.org; jonathan_at_jlcomp.demon.co.uk;
> tim.evdbt_at_gmail.com
> *Subject:* RE: Redo per transaction inconsistency when running SLOB
>
>
>
> · a physical standby database. The faster one doesn’t
>
> seems like the likely difference to me. Did I miss the protection mode?
> How far away (ping latency) is the standby?
>
>
>
> Do you have the ability to set up on the new box with the only change
> being no physical standby?
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Harel Safra
> *Sent:* Thursday, April 23, 2020 12:31 PM
> *To:* Paul.Houghton_at_uis.cam.ac.uk
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: Redo per transaction inconsistency when running SLOB
>
>
>
> Hi,
>
> Any chance the slower one with the standby is set to force logging while
> the faster one isn't? Are there nologging operations in the database?
>
>
>
> Harel
>
>
>
> On Thu, Apr 23, 2020 at 2: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