|Lesson 16||Detecting redo log buffer contention|
|Objective||Detect and resolve contention for the redo log buffer. |
Detecting redo log Buffer Contention
The redo log buffer is an area in memory where processes place redo log entries
that need to be written to disk. If the buffer is too small and you have a lot of update activity, processes may have to wait in line to place
data into the buffer. That's called redo log buffer contention. If it happens too often, it can affect performance. You can check for redo log buffer contention by monitoring a statistic named redo buffer allocation retries. The value for that statistic is incremented each time a process is forced to wait. You can check the value by querying a data dictionary view known as V$SYSTAT. Use a query
like the one shown in this example:
SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name = 'redo buffer allocation retries';
redo buffer allocation retries 43982
Ideally, this statistic should be as close to zero as possible. If you see it growing consistently over time, then you may be experiencing redo log buffer contention. To reduce the contention, increase the size of the redo log buffer. That's done by increasing the value of the
log_buffer initialization parameter. Remember to stop and restart the database so that the parameter change will take effect.
The next lesson is the module wrap-up. You will get a brief review of the topics we have covered in this module. Also, you can take a quiz
that will help you verify what you have learned and discover topics that you might want to review in a more detailed manner.