| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: log_buffer size in Oracle 10g Rel.2
This statement:
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.
used to correct, but I don't think ORACLE 10G follow it any more.
I saw ORACLE document say something relate to redo log buffer and flashback recovery. FYI:
for large production databases, it is revommended that you set the LOG_BUFFER initialization parameter to at least 8 MB.
> 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. 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?
> >
>
> > The information transmitted is intended only for
the
> person or entity to
> which it is addressed and may contain confidential
> and/or privileged
> material. If the reader of this message is not the
> intended recipient,
> you are hereby notified that your access is
> unauthorized, and any review,
> dissemination, distribution or copying of this
> message including any
> attachments is strictly prohibited. If you are not
> the intended
> recipient, please contact the sender and delete the
> material from any
> computer.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 09 2006 - 12:50:17 CDT
 |
 |