This content has been marked as final. Show 6 replies
The Only to avoid this would be reducing the frequency of commits because once the record is commited it has to be written to redolog from the log_buffer before the log_buffer gets changed with other data.Upon completion of writing to Redolog LGWR ackknowledges then log_buffer is overwritten .So when you commit frequenly you will have Log file sync wait events
Just to clarify my concepts would you comment this reply?
The log write process will wake up when someone commit or rollback a transaction like you said. But when the log writer process writes the contents on disk the users can make reuse of the free space into the log buffer.
So, if you can reuse the log buffer and you get an wait if he is full, you could try to avoid the wait by getting a bigger size on log_buffer parameter, that means the log buffer size?
I'm not saying that I have complete sure on what I have posted on this reply, that's why I will ask you to clarify if I'm wrong.
Thanks for your help and have a nice day!
Please have a read of this page,
You are right in assuming that if the space would be high than there wontbe a wait.But as if you read the page,you would be clear that when the data is posted for LGWR to be written to redo logs,the process sleeps for 1 second.This wont be effected even if the log buffer would be high in size too.
From the same page,
Can the log buffer be too big?
However, if the log buffer is very big, then the default logio_size threshold will be big also, and so background writes may seldom be triggered. This means that all the redo will have to be flushed by sync writes, and so log file sync waits will take longer than otherwise. This impacts commit response time, and possibly DBWn performance as well.
Hope this anwers the question in a better way.