Re: Redo per transaction inconsistency when running SLOB

The first thing that stands out is that there's a significant difference in the number of db block changes per commit (i.e. per transaction) - 135 per commit for the fast db, 162 per commit for the slow db. If there's little else going on at the same time as SLOB then that's an important clue - we need to see "redo entries" as well as a further clue.

I've written previously about anomalies that result in changes to things like db block changes, redo entries and performance for the same code. Off the cuff the things that spring to mind are: (a) private redo disabled - so no "large" redo entries generated in private, (b) some quirky little bug when auditing was enabled (c) a couple of features that change "update" into "select for update/ update" (d) trigger declarations - even NULL ones (e) supplemental logging

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
>
>
>
>
>
>

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