“In the old times” the LGWR had to write to the redo-logs with every COMMIT.
Starting with ORACLE 10gR2 we can use the initialization parameter
COMMIT_WRITE to change the commit behaviour
on SYSTEM- as well as on SESSION-LEVEL.
We can allow LGWR to consider itself
- when to write to disk and
- when control is returned to the client.
By specifying WAIT or NOWAIT you can influence when LGWR returns control to the client.
By specifiying IMMEDIATE or BATCH you can influence when LGWR will write to disk.
If you do not explicitly specify this advanced parameter:
- – the default is IMMEDIATE, WAIT
- the database by default will write commit records to disk for every commit
- control is returned to the client after the writing is completed.
- the client receives the massage COMMIT COMPLETE.
If you set COMMIT_WRITE to NOWAIT and you do not specify anything else:
- the default is IMMEDIATE, NOWAIT
- LGWR will write to the redologs directely after every commit
- it will not wait until the writing is completed before it sends the next writing process to the redologs
- the client will have control before the writing is completed.
If you set COMMIT_WRITE to BATCH and you do not specify anything else:
- the default is BATCH, WAIT
- LGWR will consider itself when to write to disk from the LOG_BUFFER
- LGWR will not write with every COMMIT
- the client must wait until the I/O of LGWR is completed
Changing this parameter has its drawbacks and advantages and it is you who has to descide which death to dye:
- the majour drawback is obviousely that you cannot be sure anymore that your changes are persistently saved even after commit which can mean that you might lose even committed data in the case of an instance crash.
- the advantage can be that concurrently committing sessions do not have to to wait for each other ‘s commit to complete => less wait events.
This parameter, again, is not a basic but an advanced parameter, which means that the default is o.k. in the first place and you would think about changing its value to whatever only after you encounter significant numbers of wait event with concurrent commits.
Because it is good to know that the committed changes are persistently changed and can be applied again in case of crash recovery.
You change the parameter by providing the values separated by a comma:
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT;
POSSIBLE COMBINATIONS: - IMMEDIATE,NOWAIT - IMMEDIATE,WAIT - BATCH,NOWAIT - BATCH,WAIT