Re: LOG FILE SYNC wait event

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@xxxxxxxxx> 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"
--
http://www.freelists.org/webpage/oracle-l

Other related posts: