2 Replies Latest reply: Jan 23, 2013 3:03 AM by doctordba RSS

    'log file sync' and Commits

    doctordba
      Hi all,

      There something i am trying to figure out, maybe you guys can help me with that.

      When a user session commit he is waiting on 'log file sync' untill LGWR sends the message back to user session after writing log buffer content into redo log file.

      As far as i understand this is serialize operation(one at a time).

      So how come i have 7 ms average 'log file sync' wait time and i can still perform 200 commits per sec ?

      7 ms * 200 waits = 1400ms = 1.4 sec
        • 1. Re: 'log file sync' and Commits
          Jonathan Lewis
          Ron Assa wrote:

          When a user session commit he is waiting on 'log file sync' untill LGWR sends the message back to user session after writing log buffer content into redo log file.

          As far as i understand this is serialize operation(one at a time).

          So how come i have 7 ms average 'log file sync' wait time and i can still perform 200 commits per sec ?

          7 ms * 200 waits = 1400ms = 1.4 sec
          Roughly speaking:

          If 20 sessions issue a commit simultaneously, LGWR will get (at least) one message that wakes it up to do a write. It will check the current high-water mark in the log buffer and (ignoring any details about holes and multiple log buffers) it will write as much of the log buffer as it can up to roughly that point. It may then detect that it has written all the log needed by all 20 of the waiting sessions, and signal them all that they can resume. This effect of this mechanism is know informally as the "piggyback commit".

          If you want to know more about this sort of thing, I wrote a book about it

          Regards
          Jonathan Lewis
          • 2. Re: 'log file sync' and Commits
            doctordba
            Thanks a lot, very helpful.