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 file sync

Re: log file sync

From: Harish Kalra <harish.kumar.kalra_at_gmail.com>
Date: Fri, 13 Oct 2006 23:40:25 +0100
Message-ID: <11ac22130610131540p59e66861j344ad49c8b0f56bd@mail.gmail.com>


On 10/10/06, oracle_at_digistar.com <oracle_at_digistar.com> wrote:

Hi,

Oversized log_buffer may also cause waits ong 'log file sync' as long redo enteries would be pile uo in log_buffer.

Thanks & Regards
-Harish Kalra

> hi,
>
> I am trying to uncover the root cause of log file sync waits causing an
> application to decrease in performance. 10046 trace shows hundreds of
> thousands of lines of log file sync with ela time around 1000. 1ms by
> itself isn't so bad but throughout the duration of the process it becomes
> a serious problem.
>
>
> According to the developer he has removed commits from the loops within
> the application so that one large commit is issued near the end of each
> process batch.
>
>
> I'm having a hard time identifying if the developer has in fact removed
> the commits OR if there are commits somewhere else within the application
> that he is unaware of and there are still many commits being issued which
> are causing the log file sync waits to appear in 10046 tracefiles.
>
> I have used various tools to analyze the traces and all of the tools show
> roughly 51% of the time spent in log file sync waits and the remaining
> time in CPU and other waits.
>
> Can anyone share their advice how to identify what is causing these waits
> to occur? I did raise log_buffer from 372KB to 5MB as a test but that
> yielded no benefits. The unix admins do not find any problems with the
> disk array and I do not find any waits to write to the datafiles
> themselves. The tools show a large amount of time in redo write waits but
> the redo data files are not being turned over rapidly (once every 10
> minutes for a 300MB file) so i'm puzzled where to look next. I copied one
> redo data file to the same mount as a dummy file using "time" and it only
> took 5 seconds to copy the file; I know oracle will probably use a
> different method to manipulate the file but even with this unscientific
> test I can't see how the waits are associated with the throughput to the
> disks.
>
>
> So with that said is there a way to identify in the tracefiles that the
> commits are being issued? Or any other places to look? There are over 30
> cursors involved, typically the cursor with the log file sync is #13 which
> has a rather lengthy INSERT statement.
>
>
>
> Thanks
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 13 2006 - 17:40:25 CDT

Original text of this message

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