Re: Log file sync spike

Jonathan,

I've been exeprimenting how average "log file sync" and concurency scales. I.e. running multiple sessions doing simple insert/commits and how much is their average log file sync time.

In theory, "log file sync" should increase to at most twice it's single session time, as any "syncs" are combined and send as a batch.

However as the amount of data per "request" increases, the disk also slows down.

My observation was that the "log file sync" event will increase much more then twice when multiple sessions are writing to disk.

Is the following assumption correct in your opinion (assuming dedicated spindles for redo logs):
"log file sync should never exceed twice the average wait for it's underlying device" ?

-- 
Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/





On 6/10/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:


>

> The stats

>    redo synch writes

>    redo synch time

> appear for a session that sends a "sync" call

> to the log writer.

>

> The stats

>    redo writes

>    redo write time

> appear for LGWR and record the number and

> duration of writes.

>

> Over a period as long as fifteen minutes, it is

> possible for a change in timing (two jobs running

> concurrently instead of serially) to cause the redo

> synch times (and log file sync waits) to change

> dramatically while the redo write time doesn't

> change at all.

>

>

> Regards

>

> Jonathan Lewis

> http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

>

> The Co-operative Oracle Users' FAQ

> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>

> Cost Based Oracle: Fundamentals

> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

>

>

>

> > Subject: Log file sync spike

> > Date: Fri, 9 Jun 2006 16:01:03 -0000

> > From: "Laimutis Nedzinskas" <Laimutis.Nedzinskas_at_landsbanki.is>

> >

> > I am trying to understand what is causing "log file sync" 5-15 minutes

> > duration spike in my db.

> > What puzzles me is that I can not see spike in the other activities on

> > my db.

> >

> > It is strange that:

> >

> > - "redo write time" statistics does not follow "redo synch time" at the

> > time of the spike

> > - v$sysstat value of  "redo write time" is half of the "redo synch

> > time".

> >

> > If  "redo synch time" spike happens because of hw problems then why

> > "redo write time" does not follow it?

> > If it is not hw issue then what else LGWR can be doing 50% of time(see

> > the difference between redo write time and redo synch time) ?

> > Or am I hitting yet another V$-bug?

> >

> > Database statistics in centiseconds follows:

> >

> > 56,105,687 cs redo synch time from v$sysstat

> > 54,796,921 cs log file sync from v$system_event

> >

> > 24,163,771  cs redo write time  from v$sysstat

> >

> > 162,672     cs log file switch completion from v$system_event

> >

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>



-- 
Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
http://www.freelists.org/webpage/oracle-l