This content has been marked as final. Show 5 replies
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)
869578 wrote:I think this used to be true a long time in the past - possibly up to v7, with two variants on what was scanned.
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 .. )
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.
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.
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.)