redo log space wait time, redo buffer allocation retries, redo blocks written and log_buffer

The redo log space wait time statistic records the total amount of time spent (since the instance was started) waiting for space in the redo log buffer. It is recorded in hundredths of a second:

 

SQL> select to_number(value)/100 "Seconds Waited"

  2  from v$sysstat

  3  where name = 'redo log space wait time'

  4  /

 

Seconds Waited

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

         96.63

 

SQL>

 

The redo buffer allocation retries statistic shows the total number of times a user process has had to wait for space in the redo log buffer (since instance startup again):

 

SQL> select value from v$sysstat

  2  where name = 'redo buffer allocation retries'

  3  /

 

VALUE

------

2463

 

SQL>

 

The lower these two statistics are, the better. However, you need to look at them in relation to the length of time the instance has been running. The figures above would be bad in an instance started 10 minutes ago but this Oracle 10.2.0.1.0 one was started in June:

 

SQL> select startup_time from v$instance

  2  /

 

STARTUP_TIME

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

15-JUN-13

 

SQL>

 

…and it is December now so the instance has been up for almost six months:

 

SQL> select sysdate from dual

  2  /

 

SYSDATE

---------

13-DEC-13

 

SQL>

 

You also need to compare them with the amount of redo activity. This instance has had its fair share so the wait statistics start to look even better:

 

SQL> select value from v$sysstat

  2  where name = 'redo blocks written'

  3  /

 

VALUE

----------

167877393

 

SQL>

 

Finally, you need to check whether the statistics are currently increasing. If they are high but stable right now, they may have caused a performance problem in the past but they are not causing one at present.

 

If these statistics caused problems in Oracle 9, you could try increasing the size of the log_buffer initialization parameter. You can check its value as follows:

 

SQL> select value from v$parameter

  2  where name = 'log_buffer'

  3  /

 

VALUE

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

2104320

 

SQL>

 

However, in more recent versions, Oracle calculates the value of this parameter for you. Fortunately, these statistics have never caused a problem in any databases I have had to monitor.