Re: log writer tuning

Remigiusz,

What are the common log wait events which are encountering at the db level? Is it log file synce or log file sequential write or etc.

Having log_buffer size some time causes 'log file synce'.

Jaffar

On 2/21/07, Remigiusz Sokołowski <rems_at_wp-sa.pl> wrote:
>
> Hi!
>
> Problem:
> in monitoring (OEM) we have got "needles" on activity figure (RDBMS ver.
> 10.2.0.1).
> We believe slow log writer is responsible for them.
>
> Question:
> what can we do about it?
>
> Considerations:
> 1. we have quite high commit rate and maybe this is our main problem,
> then solution would be some new fast storage for redo logs - but this
> solution is the last one to consider due to costs
>
> 2. log_buffer is very big (64M, so 1:1 to redo log files size) - we
> could set it to lower value
> may be that is why we have got those "needles" on diagram.
> But in docs for 10gR2 they say:
> "sizing the log buffer larger than 1M does not provide any performance
> benefit. Increasing the log buffer size does not have any negative
> implications on performance or recoverability."
> Is it really no negative implications ?
> At http://www.ixora.com.au/tips/tuning/log_buffer_size.htm there is
> different opinion:
> " However, if the log buffer is very big, then the default
> /_log_io_size/ threshold will be big also, and so background writes may
> seldom be triggered. This means that all the redo will have to be
> flushed by sync writes, and so /log file sync/ waits will take longer
> than otherwise. This impacts commit response time, and possibly DBWn
> performance as well."
>
> 3. our redo logs are placed on the same mount point as datafiles - we
> could move them to some other mount point
> Does it change anything, if we move to separate mount point?
> Storage system is a JBOD in architecture RAID10 (so actually as
> indicated in SAME concept) composed as one large space. File system is
> UFS with set flag forcedirectio - according to our sysadmin writes of
> log writer doesn't block other processes on mount point.
>
> 4. Are there other parameters for log writing?
>
> Thanks for any comments
> Remigiusz
>
>
>
>
> --
> ---------------------------------------
> Remigiusz Sokolowski <rems_at_wp-sa.pl>
> WP/PTI/DIP/ZAB (+04858) 52 15 770
> MySQL v. 4.x
> Oracle v. 10.x
> ---------------------------------------
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA

http://jaffardba.blogspot.com/
http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
http://www.freelists.org/webpage/oracle-l