Mladen’s work-around sounds like a work-around to try.
Notice that your wait is APPARENTLY writing log_buffer to files, but, are you
also writing to dataguard instances from LGWR (as opposed to ARCH)?
(Yes, I see that the AWR wait event is labelled LGWR, but DBAs can’t be
paranoid since quite often history has shown world is actually out to get them.)
In theory (suggested some many years ago TO Oracle actually before the
existence of “Dataguard”), in maximum protection mode Oracle could use LGWR
(the suggestion back then was about ARCH, because in roll your own physical
standbys there was no opportunity to do this before ARCH) to write the relevant
bits of the log_buffer from each particular commit as a separate stream on
possibly an independent network connection to the remote database’s archive
logs that were being continuously applied in recovery mode.) Whew. That was a
lot. If you read it a couple times it will probably make sense unless I have a
bad typo.
I have no inside info on what they have actually done, so this could be
completely wrong.
Anyway, the real reason I’m writing is to quibble with Mladen’s joke guess
about getting it right by 2525 (haunting song from my youth, by the way, and it
makes more sense if you’ve read Brave New World, The Jungle, and Slaugherhouse
Five before you listen to it). I predict that before they ever get the
complexity of multi-threaded log write correct they will realize that writing
the log is not the problem, that it will actually go faster in bigger serial
chunks because you don’t gotta check nothin’ before you write and they will
just write bigger chunks from the buffer to the log. You can read Kevin’s stuff
if you want to know exactly why writing to the redo log should never be the
problem.
Writing to log_buffer in a multithreaded pattern and even having multiple
log_buffers is a collecting “LGWR” that write “enough” serially are all
potential ways to make everything faster that are superior to trying to sort
out multi-threaded log writing. (Superior meaning less error prone to write the
code correctly and by orders of operation and required “check if I’m right”
latencies [that you don’t have to do the better way] theoretically faster.)
end of rant.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Friday, November 17, 2017 4:53 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: "LGWR any worker group" wait event
On 11/17/2017 04:27 PM, Jay.Miller@xxxxxxxxxxxxxxxx wrote:
After upgrading one of our main transaction heavy RAC databases from 12.1 to
12.2 we had a small but noticeable decrease in overall performance. Looking at
AWR reports I saw a new wait event popped up into the Top Timed Events which I
had never seen before - "LGWR any worker group". Metalink isn't very helpful,
just pointing to an IBM/AIX bug (we're on RedHat Linux running on x86).
I can't find a definition of what the wait event signifies much less what might
cause it.
Anyone here familiar with it?
Thanks!
Jay Miller
Sr. Oracle DBA
Hi Jay,
First, Metalink no longer exists. Nowadays, it's Your Oracle Support. And yes,
it is becoming increasingly useless. They no longer publish any documents or
papers about the inner workings of Oracle RDBMS. Since Oracle 12.1.0.1, Oracle
is experimenting with multi-threaded log writer. They apparently cannot get it
right. Since Oracle has now changed the system of versions, I would advise you
to use single threaded LGWR, by setting "_use_single_log_writer=true". Oracle
will probably get it right by the year 2525 and a support document which will
document the behavior will be available few centuries after that. So, use
patience my friend and don't use multi-threaded LGWR yet. There was a huge bug
which would hang RAC databases every now and then and the cure provided by YOS
was to revert back to single threaded LGWR. That killer bug is probably
resolved by now, but I doubt that the performance bugs have been resolved. For
now, I would decline to be a beta tester for Oracle and revert back to the
single threaded LGWR. Everything will be documented in the next book by
Jonathan Lewis, should he decide to write one.
Regards
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
