RE: log_buffer 10gr2
Date: Thu, 28 May 2009 10:43:37 -0400
Message-ID: <2F161F8A09B99B4ABF8AE832D546E78903DCBDEA17_at_us194mx002.tycoelectronics.net>
Where are your online redo logs located? Are they using the same disk that supports your data files for the database? Many Admins will tell you that hardware capabilities will allow you to collocate data files with redo logs - but that is not the real world I have seen - even using HP enterprise class disk...
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Thursday, May 28, 2009 10:12 AM
To: Cary Millsap; ujang.jaenudin_at_gmail.com Cc: Oracle Discussion List
Subject: RE: log_buffer 10gr2
Cary read my mind. The problem is almost certainly related to commit frequency. Excessive commits will *kill* performance and scalability.
From: Cary Millsap [mailto:cary.millsap_at_method-r.com]
Sent: Thursday, May 28, 2009 10:09 AM
To: ujang.jaenudin_at_gmail.com
Cc: Bobak, Mark; Oracle Discussion List
Subject: Re: log_buffer 10gr2
Does the application need to be committing as often as it does?
Cary Millsap
Method R Corporation
http://method-r.com http://carymillsap.blogspot.com http://twitter.com/cary_millsap
On Thu, May 28, 2009 at 8:55 AM, Ujang Jaenudin <ujang.jaenudin_at_gmail.com<mailto:ujang.jaenudin_at_gmail.com>> wrote: bobak,
log file sync is the main reason.
_log_io_size is 0 (default??)
strength on my db when user session only 200 but log file sync can reach 800 session waitings (from dbconsole). when checking the io log file parallel write max is 4ms, but log file sync can reach 22ms.
average redo blocks per write is 13 which means average buffer to write to redolog buffer is only 6656 nytes.
yes the application is heavily commit activites (now in the "load testing" stage)
any direction why too many spikes (strange) on log file sync?
-- thanks and regards ujang | oracle dba | mysql dba http://ora62.wordpress.com On Thu, May 28, 2009 at 8:49 PM, Bobak, Mark <Mark.Bobak_at_proquest.com<mailto:Mark.Bobak_at_proquest.com>> wrote:Received on Thu May 28 2009 - 09:43:37 CDT
> Ok, first, take a step back.
>
> What is motivating you to look at log_buffer in the first place? Why do you think it needs to be adjusted? Starting w/ 10g, the log_buffer is maintained by Oracle, and should not need to be adjusted. (See MetaLink Doc ID 351857.1.) What is the current value of _log_io_size? Why do you think you need to adjust it?
>
> Main question:
> What is it that is leading you to believe that log_buffer and _log_io_size need to be adjusted?
>
> -Mark
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Ujang Jaenudin
> Sent: Thursday, May 28, 2009 6:14 AM
> To: Oracle Discussion List
> Subject: log_buffer 10gr2
>
> dear all,
>
> I have 10.2.0.3 on solaris sparc,
> when trying to decrease log_buffer, it could not be done.
>
> always keep 14MB, sga is 3.5GB
> I want to decrease it to 1MB
>
> according to metalink note. 351857.1
> it is expected behavior.
>
> so, is it save to change _log_io_size to 1MB ?
>
>
> --
> thanks and regards
> ujang | oracle dba | mysql dba
> http://ora62.wordpress.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l