Re: Multi-threaded LGWR

From: Frits Hoogland <frits.hoogland_at_gmail.com>
Date: Wed, 27 Apr 2016 11:03:42 +0200
Message-Id: <B1AD43D4-283D-4487-A4F5-4DCCD7737DD4_at_gmail.com>



Hi Mladen, as far as I know, there isn’t a lot of information on logwriter internals. Because of this I investigated the inner working for a bit, as can be found on my blog (profiling the logwriter and database writer version 2). Jonathan Lewis has written about how the private strands working in his book 'oracle core’.

The foreground processes signal the lgwr when there is a need to persist their redo. If there are logwriter slaves, the lgwr signals these to do the writing. What I observe is the slave processes are signalled based on their number (lg01, lg02, etc), meaning that the lowest number gets a task first. The slave processes also perform the task of informing the write progress. The single logwriter/write slave mechanism is automatically invoked and abandoned.

There is also something else in play: foreground posting and foreground polling. The “old” mechanism for the foreground to understand it’s redo has been written is it waits on a semaphore, and needs to be posted by the logwriter. A new mechanism (available at least in version 11.2, but switched on by default in 11.2.0.3) is polling. With polling, the foreground process does not wait on a semaphore, but instead looks for the logwriter process itself. Again, both methods are used and switched automatically (it can be seen in the logwriter tracefile).

My guess is the logwriter slave mechanism works by assigning LWN batches to write slaves. This should take are of ordering and still gives the instance the ability to parallelise the writing, solving the single writer bottleneck.

I haven’t been able to find out how the adaptive switching works for both methods, which is the essence of your question.

Hope this helps,

Frits Hoogland

http://fritshoogland.wordpress.com <http://fritshoogland.wordpress.com/> frits.hoogland_at_gmail.com <mailto:frits.hoogland_at_gmail.com> Mobile: +31 6 14180860

> On 26 Apr 2016, at 23:10, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:
>
> Oracle 12c introduced a new feature, namely multi-threaded LGWR. Of course, as it usually happens, you only find out about the new feature when it doesn't work. The problems I encountered are documented in the following support documents:
> 1957710.1
> 21915719.8
> I was unable to find any controlling parameter in the documentation, short of "_use_single_log_writer" which is not really what I want. Does anyone have any details on that mechanism? How big must LOG_BUFFER be to start an additional thread? How do threads write? My guess is that they split the file into peaces and that each thread writes into its own peace of file. Obviously, there are some issues. In 11G, we saw private redo strands, now we have multi-threaded log writer. Did anyone do any research on that? This is a job for systemtap, but I have been traveling and didn't have much time.
> Regards
> --
> --
> Mladen Gogala
> Oracle Consultant
> http://mgogala.freehostia.com <http://mgogala.freehostia.com/>
>
> DISCLAIMER: I am solely responsible for any opinion expressed in this email

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 27 2016 - 11:03:42 CEST

Original text of this message