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.
- Overloaded storage array – check your trace files
- 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
WHERE 1=1
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;