Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: log_buffer size in Oracle 10g Rel.2

Re: log_buffer size in Oracle 10g Rel.2

From: Syed Jaffar Hussain <sjaffarhussain_at_gmail.com>
Date: Wed, 9 Aug 2006 17:04:59 +0300
Message-ID: <97b7fd2f0608090704p42993902l92df015406323843@mail.gmail.com>


We have migrated on the same server. Therefore, it was the same server used for Oracle 9i and we solved it by enabling CIO to the filesystems. For 10g also, CIO is enabled for all filesystems on the server.

Siraj,

Please refer metalink note : 351857.1 Bug 4930608.

On 8/9/06, Kurth, Michael J. <Michael.Kurth_at_wfhc.org> wrote:
> These waits can also be caused by performance problems on your I/O
> Subsystem. Oracle needs to write to the online redo logs very
> frequently,
> And if it can't, it will log waits like what you are seeing.
>
>
> Mike Kurth
> Senior Oracle DBA
> Wheaton Franciscan Healthcare
> 414/465-4046 Phone
> 414/465-4080 Fax
> Michael.Kurth_at_wfhc.org
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Riyaj Shamsudeen
> Sent: Wednesday, August 09, 2006 8:30 AM
> To: sjaffarhussain_at_gmail.com
> Cc: _oracle_L_list
> Subject: Re: log_buffer size in Oracle 10g Rel.2
>
> 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?
> >
>
> Privileged/Confidential information may be contained in this message. The information contained in this message is intended only for the use of the recipient(s) named above and their co-workers who are working on the same matter. The recipient of this information is prohibited from disclosing the information to any other party unless this disclosure has been authorized in advance.
>
> If you are not intended recipient of this message or any agent responsible for delivery of the message to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of this message is strictly prohibited. You should immediately destroy this message and kindly notify the sender by reply E-Mail. Please advise immediately if you or your employer does not consent to Internet E-Mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of the firm shall be understood as neither given nor endorsed by it.
>

-- 
Best Regards,
Syed Jaffar Hussain
8i,9i & 10g OCP DBA
Banque Saudi Fransi,
Saudi Arabia

I blog at :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
Received on Wed Aug 09 2006 - 09:04:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US