Examine redo log wait Statistics(Are processes waiting?)

Lesson 6	Determine if processes are waiting for space in the redo log buffer
Objective	Examine redo log wait statistics.

(Oracle) Examine redo log wait Statistics

Decrease wait time

As we discussed in the previous lesson, if

the redo log space requests or
the redo log space wait time values

in V$SYSSTAT are too high, there may be some waiting as concurrent Oracle processes attempt to write into the log buffer.
If redo buffer allocation retries is too high, we may want to increase the size of our redo log files.
The remedy is to increase the log_buffer init.ora parameter. The script below will show the redo log space request and the redo log space wait time. If they appear too large and your log_buffer is less than one megabyte, then you may want to increase the log_buffer init.ora parameter.

PROMPT
PROMPT
PROMPT         ========================= 
PROMPT         REDO LOG BUFFER 
PROMPT         ========================= 
PROMPT
SET HEADING OFF
COLUMN VALUE FORMAT 999,999,999
SELECT 
   substr(name,1,30),
   value
FROM 
   v$sysstat 
WHERE 
   NAME = 'redo log space requests'
   OR
   NAME = 'redo buffer allocation retries'
   OR
   NAME = 'redo log space wait time';
Following is the output from that query:

=========================
REDO LOG BUFFER
=========================
 
redo buffer allocation retries        1,807 
redo log space requests                  29
redo log space wait time                102

Now that we see the front-end redo log functions, let us look at principles behind sizing the redo log file system.