Re: LGWR tuning

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 good 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 committing within PL/SQL loop, you are doing "COMMIT BATCH". That is all published in Tom Kyte's 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. My developers know that and they are refraining from "COMMIT NOWAIT" or "COMMIT BATCH" commands. Yesterday was st. Valentines, so let me finish by quoting one of the people 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 word" (Al Capone)

-- 
Mladen Gogala
http://www.mgogala.com

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