| 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 FILE SYNC wait event
Hi Alberto,
Thanks for you help.
Did the following experiment
SQL> sho parameter sga_target
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 100M
SQL> sho parameter log_buffer
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_buffer integer 2899456SQL> alter system set log_buffer=500000 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1246492 bytes Variable Size 71305956 bytes Database Buffers 29360128 bytes Redo Buffers 2945024 bytesDatabase mounted.
SQL> sho parameter log_buffer
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_buffer integer *2899456*SQL>
Just want to whether Oracle automatically increases the log_buffer value?
On 9/4/07, Alberto Dell'Era <alberto.dellera_at_gmail.com> wrote:
>
> I've committed to memory this great explanation of LGWR processing:
>
>
> http://kevinclosson.wordpress.com/2007/07/21/manly-men-only-use-solid-state-disk-for-redo-logging-lgwr-io-is-simple-but-not-lgwr-processing/
>
> since you say that you can't lessen the commit frequency
> neither move to faster disks,
> you might focus on reducing CPU starvation for the LGWR
> process, something that the blog entry (actually more a paper than
> a blog entry) discusses in detail. The author (Kevin Closson)
> suggests that this is very frequently one of the major contributor
> to the "log file sync" event - in the author's final test case,
> it was the *only* contributor (look at what happens when
> He disables logging at all at the end!)
>
> BTW The log buffer is not managed by the Automatic Shared Memory
> Management
> in 10gR2:
>
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams192.htm
>
> "The following pools are manually sized components and are not
> affected by Automatic Shared Memory Management:
> * Log buffer
> ...
> "
> Anyway, an undersized log_buffer would make the processes wait for
> "log buffer space" and not "log file sync". The former means "the
> log buffer is full and I cannot write the changes I've made to the
> datafile
> blocks into it, so I'm waiting for some free log buffer space", the latter
> means "I've written the changes into the log buffer, and I'm waiting for
> LGWR to persist them in the online redo logs files".
>
> HTH
> Alberto
>
> On 9/3/07, DBA Deepak <oracle.tutorials_at_gmail.com> wrote:
> > Hi All,
> >
> > We are having a lot of Log file sync waits because of frequent commits
> > issued from the third party application. What are the solutions to fix
> this
> > apart from the follwing one...
> >
> > > To move the redo logs to faster disks(Not feasible in our case).
> >
> > We are using AUTO SGA (10g R2) which can tune log buffer on its
> own(Please
> > correct me if I am wrong).
> >
> >
> > --
> > Regards,
> >
> > Deepak
> > Oracle DBA
>
>
> --
> Alberto Dell'Era
> "the more you know, the faster you go"
>
-- Regards, Deepak Oracle DBA -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 04 2007 - 10:21:52 CDT
 |
 |