DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: log file sync

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    log file sync

    DBAs,

    We recently got a lot of complaints about one database for its slowness.
    I took a snapshot during the slow time whose time window is 15 minuets.
    From the stats report, in the top 5 waits, we saw the log file sync.
    How can we know it is abnormal or normal?

    The important part in the report is as follows:
    Code:
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                Buffer  Hit   %:   99.24    In-memory Sort %:   99.96
                Library Hit   %:   85.99        Soft Parse %:   44.28
             Execute to Parse %:   15.49         Latch Hit %:   98.70
    Parse CPU to Parse Elapsd %:   89.44     % Non-Parse CPU:   55.74
    
    
    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~                                             Wait     % Total
    Event                                               Waits  Time (cs)   Wt Time
    -------------------------------------------- ------------ ------------ -------
    log file sync                                      61,458        7,386   47.32
    latch free                                         20,065        3,686   23.61
    db file sequential read                            17,752        1,641   10.51
    log file parallel write                            56,093        1,610   10.31
    db file parallel write                              1,610          908    5.82
              ------------------------------------------------------
    
    Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
    ---------------------------- ------------ ---------- ----------- ------ ------
    log file sync                      61,458          0       7,386      1    1.0
    latch free                         20,065     15,166       3,686      2    0.3
    db file sequential read            17,752          0       1,641      1    0.3
    log file parallel write            56,093          0       1,610      0    1.0
    db file parallel write              1,610          0         908      6    0.0
    
    
    Statistic                                    Total   per Second    per Trans
    --------------------------------- ---------------- ------------ ------------
    CPU used by this session                    35,105         39.0          0.6
    Any idea from this report?

    Thnaks for the help.
    Last edited by marist89; 09-28-2005 at 02:49 PM. Reason: formatting

  2. #2
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    In this report,
    CPU used by this session 35,105
    Does it mean cpu during the 15 minuet time window has been
    used for 35,105 unit -- cs ?
    log file sync wait time is 7,386 cs?

  3. #3
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Good old Tom covers both these events in detail:

    http://asktom.oracle.com/pls/ask/f?p...16470446503580

  4. #4
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    Soft Parse %: 44.28
    Execute to Parse %: 15.49


    This is very low.

  5. #5
    Join Date
    Apr 2003
    Posts
    353
    One. Decrease your log buffer Say to 1mb (I dont know your current size but 1 m is normally good).

    Two. You might be keeping other datafiles or temp files in the redo log area. Move redo to a separate device. Do not keep them in RAID 5.

    Three. See the log file size, if frequent switches are there increase the size.

    I also face frequent log switches in one of my db with solaris 8,
    might be the undo the reason for the redo fast filling.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by engiri
    One. Decrease your log buffer Say to 1mb (I dont know your current size but 1 m is normally good).

    Two. You might be keeping other datafiles or temp files in the redo log area. Move redo to a separate device. Do not keep them in RAID 5.

    Three. See the log file size, if frequent switches are there increase the size.

    I also face frequent log switches in one of my db with solaris 8,
    might be the undo the reason for the redo fast filling.
    Your points 1.) and 3.) have nothing to do with log file sync wait event.

    What the original poster realy need to check is if there realy is the need for apparent high commit rate. The most likely reason for log file sync event is when user issues a commit (or rollback). So he/she need to check who (or what) is causing frequent commits.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by jmodic
    Your points 1.) and 3.) have nothing to do with log file sync wait event.
    Point 1 may help - by reducing the buffer size you increase the chance that the log writer will asynchronously write to the log file without the session having to wait on a log file sync. Now if the transactions are very small then this is going to be of limited help, but it likewise is unlikely to do harm.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    Thanks for all your valuable input.

    I am still digesting the log file sync thread in asktom.
    trying to understand snapshot more deeply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width