This discussion is archived
5 Replies Latest reply: Feb 14, 2013 1:11 AM by Jonathan Lewis RSS

Does "log file sync" wait have something to do with number of processes?

872581 Newbie
Currently Being Moderated
Hi, all.

The oracle is 11.2.0.3 on a linux machine.

From time to time, my db suffers "log file sync" waits, but not much, only for technical curiosity.

--------------------------

The followings are my observation.

1. There was no rapid changes in "user commits", "redo size", "redo entries".

2. There was no massive and concurrent connection.

3. LGWR was not waiting for "Log file parallel write" wait.

4. disk io resource was not busy.
=> no rapid change in physical reads/writes.

5. there was a rapid increase in "redo sync time" statistics.
=> I suspect this statistics, but I do not understand what the "sync" means.

-------------------------

One thing I suspect is about the number of dedicated server processes.
I have too much processes (about 6000), compared to the number of active session.

As far as I know, when a commit occurs, oracle must scan data structures of all dedicated server processes,
because redo entries must be flushed to redo log file in the order of SCN.

(not sure, I have read it long ago, but forgot .. )

Is this right?

Reducing the number of processes could help to reduce "log file sync" wait?

Thanks in advance.
Best Regards.
  • 1. Re: Does "log file sync" wait have something to do with number of processes?
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    log file sync happened for :

    Log File Sync waits occur when sessions wait for redo data to be written to disk
    typically this is caused by slow writes
    or committing too frequently in the application
    CPU overburning(very high demand => LGWR on run queue)
    improper Operating System configuration(check 169706.1)
    BUGs in Oracle(especially with RAC option) and 3rd Party software(like ODM/DISM)


    Check
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:363764000346087807
  • 2. Re: Does "log file sync" wait have something to do with number of processes?
    KuljeetPalSingh Guru
    Currently Being Moderated
    Reducing the number of processes could help to reduce "log file sync" wait?
    short answer is No.
  • 3. Re: Does "log file sync" wait have something to do with number of processes?
    872581 Newbie
    Currently Being Moderated
    to osama.

    CPU was not a problem.

    Thanks for your reply.
  • 4. Re: Does "log file sync" wait have something to do with number of processes?
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    I just post the reasons
  • 5. Re: Does "log file sync" wait have something to do with number of processes?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    869578 wrote:

    One thing I suspect is about the number of dedicated server processes.
    I have too much processes (about 6000), compared to the number of active session.

    As far as I know, when a commit occurs, oracle must scan data structures of all dedicated server processes,
    because redo entries must be flushed to redo log file in the order of SCN.

    (not sure, I have read it long ago, but forgot .. )
    I think this used to be true a long time in the past - possibly up to v7, with two variants on what was scanned.
    For a very long time, though, the implementation has been to post only those sessions that are waiting.

    >
    Reducing the number of processes could help to reduce "log file sync" wait?
    The question is ambiguous. If you mean just the parameter processes, then this will have no different.
    If you mean the number of processes that are actually running against the instance, then you may see some benefit, but it may simply be an unlucky event based on the number of simultaneous commits.

    Scenario 1:
    When its write is complete, lgwr posts all waiting sessions to resume - but that simply means they join the end of the run queue.
    If there are lots of processes (even those doing nothing) ahead of them then they will have to wait some time to resume - and the log file sync time (which is also reported as the redo sync wait time, by the way) includes the time spent in the run queue.

    Scenario 2:
    There aren't many processes in the run queue - but 20 sessions have committed at about the same time, so lgwr posts them all to resume. If you have only 1 CPU then one of the sessions can't record its log file sync time until the previous 19 have started up, done their thing, and got out of the way. (If you've got 2 CPUs then 2 sessions have to wait for 9 others, etc.)

    Regards
    Jonathan Lewis

Legend

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