Describe the performance issues related to the redo log buffer.
Redo Log Buffer
The redo log buffer contains the Oracle redo logs that hold previous versions of Oracle transactions and are used to roll-forward the database in cases of disk failure.
When a database is in ARCHIVELOG mode, Oracle will write these redo logs onto a disk file with the ARCH background process.
Regardless of the ARCHIVELOG mode, Oracle must still manage the redo images and write them to the online redo logs, via the redo log buffer.
Redo logs and performance problems
There are several areas where redo log processing can cause a performance problem:
When the redo logs switch too frequently (> 2 per hour)
When the database experiences a high value for redo log space requests
When the archived redo log file system becomes full.
Since the redo log buffer is used to cache update, insert or delete activity, high update activity would be associated with redo log buffer activity. A high rate of redo log switching or high redo log space requests always indicates a problem.
The remedy is to increase the log_buffer init.ora parameter.
Detecting high values for redo log space requests
While we will explore these issues in detail in a later module, let us look at a simple query that can detect a high redo log space request. Note that it queries the V$SYSSTAT view. When you see high redo log space requests you can increase the init.ora parameter called log_buffer. To increase the size of the log buffer, you increase the log_buffer parameter in your init.ora file and re-start the Oracle instance.
This parameter determines the amount of memory to allocate for Oracle's redo log buffers. If there is a high amount of update activity, you should allocate more space in the log_buffer.
prompt REDO LOG BUFFER
prompt (should be near 0, else increase size of LOG_BUFFER in init.ora)
set heading off
column value format 999,999,999
from v$sysstat where name = 'redo log space requests';
Here is what the output from this query might look like:
REDO LOG BUFFER
(should be near 0, else increase size of LOG_BUFFER in init.ora)
redo log space requests 0
The next lesson concludes this module.
Redo Log - Exercise
Before you go on, click the Exercise link below to check your knowledge of basic SGA tuning concepts. Redo Log - Exercise