FAQ
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 (1.140.674 cs redo synch
time from v$sesstat)
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

Thank you in advance,
Laimis N.
Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer

Search Discussions

  • Schultz, Charles at Jun 9, 2006 at 4:30 pm
    This is very similar to an issue we experienced under 9i and Solaris 8.
    We spent a few weeks pointing fingers (and having fingers pointed at
    us), but ultimately it came down to the buffered writes to the disk.

    As this was in 2004, I am mining the email for more details. We were
    helped by TUSC (*wink wink* to the guys that hang out here). We made two
    changes that had a dramatic effect; we switched from EMC disk to local
    (Sun) disk and dumped Veritas File Manager for UFS, but only for the
    mounts which housed the redo logs (independent of everything else, we
    are good OFA boys). We never had approval to go back and test to see if
    either one change or the other did the trick, so I can only recommend
    you do both. Later in life, we switched to faster disk from EMC (DMX)
    and Veritas DirectIO, again not knowing if both were needed or not.

    Let me know if you want more details, as your situation may be different
    than ours.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Laimutis Nedzinskas
    Sent: Friday, June 09, 2006 11:01 AM
    To: oracle-l_at_freelists.org
    Subject: Log file sync spike

    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 (1.140.674 cs redo synch
    time from v$sesstat)
    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

    Thank you in advance,
    Laimis N.
    Fyrirvari/Disclaimer
    http://www.landsbanki.is/disclaimer
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Jun 10, 2006 at 11:42 am
    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"

    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 at Jun 12, 2006 at 9:04 pm
    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 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"

    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
  • Laimutis Nedzinskas at Jun 12, 2006 at 8:48 am
    From: "Jonathan Lewis"

    *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.

    Thank you, Jonathan.

    "Dramatically" in my case is 10-15 times bigger wait time per period (I
    take snapshots at 20 min intervals.)
    I am just wondering where lays the cause for this behavour. I can only
    speculate that the way LGWR batches log sync requests can cause a longer
    wait times.

    Brgds, Laimis N.

    Fyrirvari/Disclaimer
    http://www.landsbanki.is/disclaimer

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 9, '06 at 4:01p
activeJun 12, '06 at 9:04p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase