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
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
