It’s been a while since you’ve taken an Oracle course, and especially the one where you learned about the basics, the fundamentals.
Maybe some of the basic concepts have been forgotten…and you might need a refresher.
Why is it so important to know the fundamentals? Well, in order to be a good DBA, you must have a good foundation of the Oracle basic components.
The log buffer is one of those components, and is the subject of today’s post.
What Is The Log Buffer?
The Log Buffer is one of the most important memory components in the SGA (System Global Area). The Log Buffer is a circular buffer, that holds information and changes, that will be written to the online redo logs.
The size of the Log Buffer is determined by specifying a value for the init parameter log_buffer.
This parameter is not dynamic, meaning if you want to change its value, you must restart the database.
Usually the size of the log buffer is small compared to the buffer cache or shared pool. The size of the log buffer is in the order of MB.
What Process Is Using The Log Buffer?
The Oracle server processes that makes changes to the data blocks in the buffer cache, also write the information out to the Log Buffer.
Then log writer or LGWR background process comes along and writes out changes from the log buffer to the online redo logs. There is only one LGWR process for each instance.
When does LGWR write?
- when the log buffer is 1/3 full
- every 3 seconds
- at checkpoint
- when a session issues a commit or rollback
As changes are written to redo logs, those changes in the log buffer can be overwritten. You want to make sure that there is enough space in the log buffer for the new changes to be written. That is why the online redo logs should be placed on fast disk, in order for LGWR to write out the changes fast to disk.
Sometimes a larger sized log buffer can potentially mask a slower disk problem, or an I/O problem. Also some of the wait events associated with the log buffer could be misleading.
That was the case with my encounter with the following wait event:
log buffer space
When you see this event, as one of the top wait events, it doesn’t necessarily mean that the log buffer is small and needs to be increased.
Instead it could be an indicator of other I/O problems in the system or network. Increasing the log buffer size could actually have negative effects. Have a look at the below screenshots.
Even if the log buffer space was one of the top events, the problem was hiding in the system, more precisely in the network. The System I/O (all the blue) is an indicator of that.
