This discussion is archived
9 Replies Latest reply: Mar 22, 2013 3:09 AM by Kh$n RSS

How to tune ‘log file sync’ waits.

Kh$n Journeyer
Currently Being Moderated
os version is solaris 10
db version 11.2.0.3

how to tune log file sysnc waits in 11gr2 what parameters are involving in tuning log file sync and what would be its optimal values.
  • 1. Re: How to tune ‘log file sync’ waits.
    SalmanQureshi Expert
    Currently Being Moderated
    H,
    Commit as less as you can because on each commit, log writer has to write data into the redo log file on the disk (and disk writing is the most expensive process). If your sessions are loging in ang logging out very fast (may be because of connection pooling), then you maya lso see this among the top events.

    Check MOS note ID 1376916.1

    Salman
  • 2. Re: How to tune ‘log file sync’ waits.
    Kh$n Journeyer
    Currently Being Moderated
    can you name some parameter name which is involve in tuning log file sync or tuning log files??
  • 3. Re: How to tune ‘log file sync’ waits.
    John Stegeman Oracle ACE
    Currently Being Moderated
    Not everything is tuned by changing parameters.
    what would be its optimal values
    If there were an optimal value for all cases, don't you think that Oracle would set it to that automatically?

    Why do you think you are having a problem with log file sync waits? As Salman told you already, you wait for log file sync when you commit.

    John
  • 4. Re: How to tune ‘log file sync’ waits.
    CKPT Guru
    Currently Being Moderated
    Asadullah699 wrote:
    os version is solaris 10
    db version 11.2.0.3

    how to tune log file sysnc waits in 11gr2 what parameters are involving in tuning log file sync and what would be its optimal values.
    Some good links, Read Jonathan Lewis comments
    log file sync" while performing COMMIT and ROLLBACK operations
  • 5. Re: How to tune ‘log file sync’ waits.
    SalmanQureshi Expert
    Currently Being Moderated
    can you name some parameter name which is involve in tuning log file sync or tuning log files??
    As explained by other people, there is not parameter related to this. Do you have any performance problem which made you think "tuning" of this? Why do you want to "tune" this. How you came to know that this is the "problem"

    Salman
  • 6. Re: How to tune ‘log file sync’ waits.
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi

    a few recommendations:

    1) make sure log file sync is a real problem (i.e. it's affecting end user experiencing and lowering the business productivity of the application) -- e.g. don't tune it just because someone reported "seeing it" or it popped up in top-5 events in an AWR report; only invest your time in this if you can expect a noticeable reduction in response time of code whose performance is important to business
    2) find out the root cause of log file sync waits. for this you need to consider following factors:
    - amount redo per second produced by your application
    - log file parallel write events, their number and average wait times (better yet, distribution of wait times)
    - commit statistics
    - CPU usage on the server
    - log file sync waits: distribution of wait times
    - log file switch frequency

    All of the information above can be found in various sections of an AWR report(load profile, OS stats, background timed events, timed event histograms; for log file swithes, do a test search on the report, I don't remember which section it belongs to).

    If you consider all the information above, you should get some basic idea of what is going on in your system and how it is causing log file sync waits. If you have difficulties in this analysis, you can post the listed AWR sections here for help.

    3) generally, the measures that help reduce log file sync waits are:
    - improve redo disk I/O performance
    - reduce commit frequency (in particular, you should remove all commits from database code unless they are absolutely necessary -- this not only affects performance, but also violates basic ACID principles and jeopardizes data consistency); in any case you should remove all commits inside loops, this is absolutely evil
    - if redo log switch occurs more often than once per 10-15 minute, increase redo log file size
    - if log file sync waits are caused by CPU scheduling latency, check for signs of CPU starvation and address the root cause of it, if found (if not found, it could be a scheduling priority issue -- talk to your SA about that)

    But don't just jump to trying various solutions before getting a good idea of what is happening and working out some consistent remediation strategy -- such "desperate switch flipping" approach most likely will only make things worse.

    Best regards,
    Nikolay
  • 7. Re: How to tune ‘log file sync’ waits.
    Mohamed Houri Pro
    Currently Being Moderated
    In addition to the link supplied by CKPT, it is worth reading the following two posts on 'log file sync' waits tuning to get comprehensive understanding of that wait event

    http://orainternals.wordpress.com/2008/07/07/tuning-log-file-sync-wait-events/
    http://orainternals.wordpress.com/2008/11/24/log-file-synch-tuning-2/

    Best regards
    Mohamed Houri
    www.hourim.wordpress.com
  • 8. Re: How to tune ‘log file sync’ waits.
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Mohamed Houri wrote:
    In addition to the link supplied by CKPT, it is worth reading the following two posts on 'log file sync' waits tuning to get comprehensive understanding of that wait event

    http://orainternals.wordpress.com/2008/07/07/tuning-log-file-sync-wait-events/
    http://orainternals.wordpress.com/2008/11/24/log-file-synch-tuning-2/
    Very good post from Riyaj, there.
    One other excellent post for understanding lgwr processing: http://kevinclosson.wordpress.com/2007/07/21/manly-men-only-use-solid-state-disk-for-redo-logging-lgwr-io-is-simple-but-not-lgwr-processing/

    Regards
    Jonathan Lewis
  • 9. Re: How to tune ‘log file sync’ waits.
    Kh$n Journeyer
    Currently Being Moderated
    Thanks

    Salman Qureshi      
    John Stegeman
    CKPT      
    Nikolay Savvinov      
    Mohamed Houri      
    Jonathan Lewis

    for your support and sharing knowlege.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points