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';

NAME                                    VALUE

----------------------------------- ---------

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.