LOG_FILE_SYNC Wait Event – Possible Solution

You might experience this issue which means LGWR (LogWriter) takes too long to write the content of Log Buffer Cache into REDO Log files. There are two possible causes.

  1. Overloaded storage array – check your trace files
  2. Overcommitting – this is because of you commit too much in your database 🙂

I will discuss here only choice number 2 – overcommitting. Let’s check your parameters that are set for two particular parameters.

SELECT name, issys_modifiable
FROM v$parameter
AND name IN ('commit_wait','commit_logging');

If it is set to IMMEDIATE this might be the issue.  Let me explain their purpose.

  • commit_logging – can have two possible values
    • immediate – default value; writes every operation immediately (might cause the issue above)
    • batch – writes data in batches; could fix the issue above but in case of a crash there is a risk of data loss
  • commit_wait – can have three possible values
    • wait – is forced to wait and write redo data in batches
    • force wait – very similar to the one above
    • nowait – writes data on commit immediately

To fix the issue above, try to alter the system as shown below and observe the results.

ALTER SYSTEM SET commit_logging=batch;
ALTER SYSTEM SET commit_logging=wait;