0

We are continuously doing heavy insert and update query, but after 10 hours data base is returning answer late in minutes normally it takes around 1 ms. I observed that "free buffer wait" and "redo log check point complete" is taking most of the time.

Database have redolog size of 512 MB and 4 group defined and each have four files. So do we need to add more size or DBWR or LGWR thread has throughput issue (How I can identify), or hard limittation.

Thanks

  • You might want to indicate the Oracle version and platform. – mustaccio Jun 5 '14 at 12:16
  • Oracle version is 11.2.0.2.0 on linux – Ashu Jun 9 '14 at 4:35
1

"Free buffer wait" event usually means the buffer cache is not big enough. Increasing the size of the SGA will alleviate this wait event by allowing more changes to happen before all buffers are dirty.

"Redo log check point complete" wait event happens when a block that has been changed in the buffer cache has not yet been written to the disk. The online redo log that contains this change cannot be archived until this has happened. Adding extra log files or increasing the size of the online redo logs will alleviate this wait event in the same way as increasing the size of the SGA - more changes can be made before this error is encountered.

However, both of these events have the same root cause - too many dirty blocks in the buffer cache. This is caused by poor DBWR performance as you suggest above. Try adding extra DBWR processes. If this doesn't help then you probably have an I/O problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.