Re: log_buffer size in Oracle 10g Rel.2

On 8/9/06, Riyaj Shamsudeen <rshamsud_at_jcpenney.com> wrote:
>
> Syed
>
> >>We are oftenly getting 'log file sync' and 'log file parallel wait'
> wait event.
> How much time is spent on these waits by the foreground processes ? Can
> you post top 5 wait event section from statspack report or AWR report ?
> Are you trying to tune a specific process or instance wide tuning ?
>
> >>I feel, 16M is very big value for log_buffer.
> That is not an universally true statement to make. You can have 100M log
> buffer and still have stellar performance. Log buffer size must be
> determined in conjunction with redo generation rate and commit rate.
>
> >>redo_buffer can't be resized, as it depends on no. of datafiles.
> Would you please post the document ID so that we can review this, please
> ? This doesn't sound correct. I don't know why log_buffer size would be
> dependent upon # of datafiles.
>
> >>The _log_io_size is 0. Can we play with this hidden parameter to bring
> >> back the redo_buffer size to 3M?
> LGWR flushes the log buffer when there is more than 1M to write or 1/3rd
> full. So, any value for _log_io_size above 1M is meaningless.

Riyaj,

Your assumptions may have been invalidated by 10g R2, as that is the version that the poster stated in the header.

http://www.orafaq.com/usenet/comp.databases.oracle.server/2005/10/27/1581.htm

I have nothing further to say on this, but likely have to include additional material to not get flagged by the lameness filter.

Paul

But, if
> you set it too low, then LGWR might be overactive introducing latch
> contention issues. Unless commit rate is excessive in your application
> or an I/O problem with LGWR, there is no need to tweak these parameters.
>
> In essence, does statspack report/AWR report /sqltrace shows that these
> waits are excessive and must be tuned ?
>
> Thanks
> Riyaj
>
> Syed Jaffar Hussain wrote:
> > Hello List,
> >
> > We have recently upgrade our 9i Rel.2 database to 10g Rel.2, on AIX 5L
> > OS.
> > We are oftenly getting 'log file sync' and 'log file parallel wait'
> > wait event.
> > In 9i, we have solved this by enabling CIO to the filesystem.
> > Surprisingly, the log_buffer size is set to 16M, by default.
> > We tried to set multiple value, but, by default it takes 16M.
> > I feel, 16M is very big value for log_buffer.
> > When I go and search in the metalink, it says, its a bug and
> > redo_buffer can't be resized, as it depends on no. of datafiles.
> > Our server has 16cpus and 160 datafiles.
> > The _log_io_size is 0. Can we play with this hidden parameter to bring
> > back the redo_buffer size to 3M?
> >
>
>
>

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