Re: LGWR tuning

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: paul@xxxxxxxxxxxxx
  • Date: Wed, 15 Feb 2006 00:17:44 -0500

On 02/14/2006 11:09:36 AM, Paul van den Bogaard wrote:
> During a previous benchmark I learned that the LGWR will chop writes
> into chunks of max 1MB. This results in multiple AIO send to the Solaris
> kernel in quick succession.
> The transactions were throttled by 'log file sync' event. Another
> benchmark will occurr that drives this application to even higher limits.
> I wonder/expect there is a (hidden) parameter that resets this 1MB limit
> to something more appropriate.

If your version is appropriate, you can use "COMMIT NOWAIT". Here is what the 
manual says:

IMMEDIATE The IMMEDIATE parameter initiates I/O, causing the redo for the 
commit of the transaction to be written out immediately by sending a message to 
the LGWR process. If you specify neither IMMEDIATE nor BATCH, then IMMEDIATE is 
the default.

BATCH The BATCH parameter causes the redo to be buffered to the redo log. No 
I/O is initiated.

WAIT The WAIT parameter ensures that the commit will not return until the 
corresponding redo is persistent in the online redo log. If you specify neither 
WAIT nor NOWAIT, then WAIT is the default.

NOWAIT The NOWAIT parameter allows the commit to return before the redo is 
persistent in the redo log.

You can do COMMIT NOWAIT and not wait for those pesky commits. When you are 
within PL/SQL loop, you are doing "COMMIT BATCH". That is all published in Tom 
architecture book. Of course, one could argue that "COMMIT" means waiting for 
the real end of transaction and waiting for the transaction to become permanent 
but there is also philosophy which treats commits as a hurdle on the way to the 
better performance. This is one of the 
reasons why I am a strong supporter of 2nd amendment and the rights it gave us. 
developers know that and they are refraining from "COMMIT NOWAIT" or "COMMIT 
commands. Yesterday was st. Valentines, so let me finish by quoting one of the 
who have made that date so famous:
"You can get further with a kind word and a gun than you can with just a kind 
(Al Capone)

Mladen Gogala


Other related posts: