Several init.ora parameters influence the behavior of the redo logs. However, the most obvious parameter is one that is not in the init.ora file. The ARCHIVELOG parameter is set with the ALTER DATABASE command. Oracle will use the ARCH server process to move the redo log files from the online redo log file system to the archived redo log directory.
In some databases, especially those that are loaded with batch jobs, they forego recoverability and run Oracle in NOARCHIVELOG mode.
NOARCHIVELOG mode will run faster because Oracle does not have the overhead of the ARCH process.View the code below to review the
redo log init.ora parameters:
Start archival process on SGA initialization
Number of buffers to allocate for archiving
Size of each archival buffer in log file blocks
Archival destination text string
archival destination text string
Archival destination format
Redo circular buffer size
Redo blocks checkpoint threshold
Maximum time interval between checkpoints in seconds
Calculate checksum for redo blocks when writing
Redo entries larger than this will acquire the redo copy latch
Number of simultaneous copies into redo buffer of copy latches
You can use Server Manager to display the current values for all redo log-related parameters with the "show parameters log" command. The 1) log_simultaneous_copies, and 2) log_buffer parameters can have profound impact on Oracle performance on computers with multiple CPUs (i.e. SMP or MPP), since multiple redo copy latches allow multiple processes to copy entries to the redo log buffer concurrently. The number of redo copy latches is determined by the init.ora parameter log_simultaneous_copies.
Log_simultaneous_copies is obsolete in Oracle 8.1.5 (Oracle8i). To see all of the obsolete init.ora parameters in Oracle8i, you can issue a query against the x$ksppo structure. View the Code below to see obsolete parameters.
If there is contention for the redo copy latch then more latches should be added by increasing the value of log_simultaneous_copies. This value should be set to double the number of CPUs on your server. For example, if your server has 12 processors, log_simultaneous_copies should be set to 24.
Held redo latches
The V$LATCH and V$SESSION_wait views give information about held redo log latches.
To check for redo copy latch contention, run the following script in the shown in the code below.
a.wait_time = 0
a.event = 'latch free'
a.p2 = b.latch#
b.name like 'redo copy';
a.addr = b.laddr(+)
a.latch# = c.latch#
c.name like 'redo copy'
order by a.latch#;
NAME GETS MISSES SLEEPS IMM_GETS IMM_MISS PID
---- ----- --------- ---------- ---------- ---------- -------
redo copy 1141 1115 1603 14146584 2702
Do not be concerned if you do not understand the functions of the
MISSES and SLEEPS,
columns. These are latch statistics that we will be covering in detail in a later module.
The following diagram shows 1) Redo_blocks_written, 2) Redo_log_space_wait_time, 3) Redo_buffer_allocation_retries
The V$SYSSTAT view contains a wealth of information about the redo log files. The most important statistics are
redo blocks written
redo entries linearized,
redo small copies,
redo log space wait time and
File I/O for the redo logs should be distributed by separating the types of files onto different disks. Redo logs should be located on disks that do not contain database files. This is because the datafile I/O is scattered, whereas redo log files are always written sequentially.
In the next lesson, we will examine the redo log wait statistics.
Redo Log Basics - Quiz
Before moving on to the next lesson, click the Quiz link below to check your understanding redo log init.ora parameters. Redo Log Basics - Quiz