RE: "LGWR any worker group" wait event

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Friday, November 17, 2017 4:53 PM
To: oracle-l_at_freelists.org
Subject: Re: "LGWR any worker group" wait event  

On 11/17/2017 04:27 PM, Jay.Miller_at_tdameritrade.com 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


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