Thanks Jonathan (and the others who replied).
I did one update and compared the log dumps just for that (Mostly to have a
look at them). It is clear both from the dumps, and v$database.supplemental*
that it is supplemental logging that is the culprit. It is switched off in the
“fast” database. It is on in production because we have a logical standby. Your
other email that mentioned reasons for extra logging made me realise that is
what it would likely be.
It is set to log unique and primary key columns. Since the index on the SLOB
table isn’t unique, it has to add all columns to the log. I expect has a larger
impact on SLOB than on our production workload because slob updates 2 columns,
but has another 18 that have to be added to the log, 17 of which are
varchar2(128), and all are full of “X”.
Once I switched off supplemental logging in the “slow” database, the redo per
transaction dropped to the same level as that in the “fast” one. I think we
need supplemental logging switched on in all our databases so developers can
get a greater understanding of how their code will perform in production.
The databases with supplemental logging on were restored using RMAN, where the
one with it off is a SAN snapshot, and the control file created via SQL. I
infer the supplemental logging configuration must be stored in the control file.
I will write this up if I get a chance.
Cheers
PaulH
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Jonathan Lewis
Sent: 25 April 2020 09:49
To: oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxx<mailto:Paul.Houghton@xxxxxxxxxxxxx>> 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@xxxxxxxx<mailto:mwf@xxxxxxxx>>
Sent: 23 April 2020 19:14
To: harel.safra@xxxxxxxxx<mailto:harel.safra@xxxxxxxxx>; Paul Houghton
<Paul.Houghton@xxxxxxxxxxxxx<mailto:Paul.Houghton@xxxxxxxxxxxxx>>
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>;
jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>;
tim.evdbt@xxxxxxxxx<mailto:tim.evdbt@xxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Harel Safra
Sent: Thursday, April 23, 2020 12:31 PM
To: Paul.Houghton@xxxxxxxxxxxxx<mailto:Paul.Houghton@xxxxxxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx<mailto:Paul.Houghton@xxxxxxxxxxxxx>> 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
