Mightn't "redo size" be affected by the number of log archive destinations?
If the standby is MAX PROTECTION or MAX AVAILABILITY , then LGWR
process(es) have to write out to the standby along with writes down to
the online redo log files.
I can't imagine any process other than LGWR to calculate and store the
"redo size" statistic, so my question is whether LGWR tallies bytes read
from the Log Buffer, or whether it tallies bytes written to the
destinations (i.e. online redo plus MAX PROT/AVAIL standby dests)?
On 4/23/2020 6:24 AM, Jonathan Lewis wrote:
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>
On Thu, Apr 23, 2020 at 12: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
