Re: log_buffer size in Oracle 10g Rel.2

  • From: "Syed Jaffar Hussain" <sjaffarhussain@xxxxxxxxx>
  • To: "Kurth, Michael J." <Michael.Kurth@xxxxxxxx>
  • Date: Wed, 9 Aug 2006 17:04:59 +0300

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@xxxxxxxx> 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@xxxxxxxx


-----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Riyaj Shamsudeen Sent: Wednesday, August 09, 2006 8:30 AM To: sjaffarhussain@xxxxxxxxx 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."
--
//www.freelists.org/webpage/oracle-l


Other related posts: