Re: redo log sync time

"Andreas Goldammer" <agoldammer_at_viega.de> wrote in message news:3a5d4d91$0$8777$73bec562_at_personalnews.de.uu.net...
>
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> schrieb im Newsbeitrag
> news:3A5C72C2.2D2C_at_yahoo.com...
> > Andreas Goldammer wrote:
> > >
> > > A few weeks ago I found a high average redo log sync time (40 ms)
> > > for my AIX database. I looked at the documentation and
> > > everything I found was tuning the I/O subsystem (SSA) or
> > > reduce the number of commits (impossible, we are using SAP R/3).
> > >
> > > Two weeks ago I moved the database to new hardware
> > > (new fast box, new fast adapters, new fast disks) and
> > > removed the AIX mirror from the online redo log filesystems
> > > to avoid the MWC impact (now I use ORACLE to mirror the log)
> > >
> > > The average redo log sync time remains steady, whereas
> > > other file access counters have decreased
> > >
> > > Any other ideas beside I/O and commits ?
> > >
> > > Thanks in advance
> > > Andreas
> > > mailto: agoldammer_at_viega.de
> >
> > As well as Brian's info - you may get some benefit by lowering your log
> > buffer size to get LGWR to wake up "earlier" in the transaction cycle.
> >
> > HTH
> > --
> > ===========================================
> > Connor McDonald
> > http://www.oracledba.co.uk (mirrored at
> > http://www.oradba.freeserve.co.uk)
> >
> > "Some days you're the pigeon, some days you're the statue"
>
> Woudn't a lower log buffer size leed to problems in the
> ORACLE server processes (waiting for logspace) ?
>

this is not necessarily a problem. remember that the contents of the log buffer are not written (by LGWR) to the redo log file until (1) the buffer is 1/3 full, or (2) a commit statement is issued.

as Connor points out, with an overly large log buffer, LGWR may be waiting for the log buffer to become 1/3 full, and then may have a lot of i/o (sequential writes) to do to catch up.

aside from reducing the frequency of commits, the cure for "high" redo log sync times is indeed tuning I/O, which means (1) reducing contention on the disk drives, and (2) avoiding any unnecessary overhead, like that imposed by a file system. Received on Thu Jan 11 2001 - 08:57:50 CST