Latches and Tuning:Redo Log Buffer and Latches

1. The size of the redo log buffer is determined by the LOG_BUFFER parameter.

a.  Determine the size of the LOG_BUFFER by executing the following query:> select * from v$sga; b.  View the LOG_BUFFER setting in the init.ora file.

2.  There are three latches associated with the redo log buffer.

a.  Execute the following query to view the latches:> select name from v$latchname where name like 'redo%'; There will be more discussion about these latches later in the module.

3.  A number of parameters that effect latch performance have been made obsolete in Oracle8i. a.  Execute the following query to view the obsolete redo buffer parameters: > select ksppinm from x$ksppi where ksppinm like '_log%'; Of the parameters listed, _LOG_SIMULTANEOUS_COPIES  and _LOG_IO_SIZE are now obsolete.  LOG_SMALL_ENTRY_MAX_SIZE and LOG_ENTRY_PREBUILD_THRESHOLD are no longer available in Oracle8i. _LOG_SIMULTANEOUS_COPIES effects the redo copy latch, but since the redo copy is always acquired there is no need to set this parameter. _LOG_IO_SIZE effects the redo writing latch. b.  Execute the following query to view the redo related parameters that are still available in Oracle8i: > select name from v$system_parameter where name like 'log%'; You should see the parameters log_buffer, log_checkpoint_interval, and log_checkpoint_timeout. 4. There are 10 wait events associated with the redo log buffer and latches.

a.  View the wait events by executing the following query:> select name from v$event_name where name like 'log%'; Of the events that are listed, three are of primary importance: Log buffer space: a process is waiting for space in the log buffer Log file sync: the time it takes for LGWR to complete the write then post the requester. Log file parallel write: the main event waited while writing the redo records to the current log file. b. You can also gather event information from the V$SYSTEM_EVENT view by executing the following query: > select event,total_waits,total_timeouts,time_waited from v$system_event where event in ('log buffer space','log file sync','log file parallel write'); In a well tuned system redo-related waits should be none or minimal.  If one of the events is not listed in the output that means no event has occurred.

5.  There are 14 statistics associated with the redo log buffer and latches.

a.  Execute the following query to view the statistics:> select name from v$sysstat where name like 'redo%'; The most important statistics are: Redo Writes: number of times the log buffer is written. Redo Blocks Written: total number of blocks written. Redo Log Space Requests: number of times requested to allocate space in the log file. Redo Size, Redo Entries: how much redo is generated since instance statrup Redo Buffer Allocation Retries: total number of retries necessary to allocate space in the redo buffer.

6.  Contention for redo log buffers does not generally cause major performance problems on the database. Waits for the event log buffer space could indicate contention for the redo log buffers.  However, it may be more useful to find out the proportion of redo entries which had to wait for space to become available. a.  Execute the following query to determine the ratio: > select r.value, s.value, r.value/s.value from v$sysstat r, v$sysstat s where r.name = 'redo buffer allocation retries' and s.name = 'redo entries';; The ratio should be near zero. 7. Remember from the first practice that there are three latches associated with the redo log buffer; redo allocation, redo copy, and redo writing.  The tuning goal is to minimize contention and waits for these latches.

a.  To tune the latches, look at the latch statistics and the ratio of MISSES to GETS by executing the following query:> select n.name, gets, misses, immediate_gets, immediate_misses from v$latch l, v$latchname n where n.name in ('redo allocation','redo copy','redo writing') and n.latch#= l.latch#; If the ratio of MISSES to GETS exceeds 1%, or the ratio of IMMEDIATE_MISSES to (IMMEDIATE_MISSES+IMMEDIATE_GETS) exceeds 1%, there is latch contention. To return the ratios you can execute the following query.  However, you may receive a division by zero error. > select n.name, gets, misses, immediate_gets, immediate_misses, (misses/gets), (immediate_misses/(immediate_misses+immediate_gets)) from v$latch l, v$latchname n where n.name in ('redo allocation','redo copy','redo writing') and n.latch#= l.latch#;

8.  You can increase the number of redo copy latches by setting the parameter LOG_SIMULTANEOUS_COPIES. This parameter is a hidden parameter and must be set in the init.ora and the database must be restarted for it to take effect.

a.  One method for determing the number of redo copy latches is to query the V$LATCH_CHILDREN view.  There will be one latch for the value of _LOG_SIMULTANEOUS_COPIES.  If there is only one latch, you will have to query V$LATCH_PARENT as there will be no children.> select name from v$latch_children where name = 'redo copy'; b.  If you want to increase or decrease the number of redo copy latches, modify the parameter _LOG_SIMULTANEOUS_COPIES in the init.ora file.  Once you have done this, you can bounce the database and execute the query in step a.

9.  You can increase the number of redo writing latches by modifying the parameter _LOG_IO_SIZE.  This parameter is a hidden parameter and must be set in the init.ora and the database must be restarted for it to take effect. a.  One method for determing the number of redo copy latches is to query the V$LATCH_CHILDREN view.  There will be one latch for the value of _LOG_IO_SIZE.  If there is only one latch, you will have to query V$LATCH_PARENT as there will be no children. > select name from v$latch_children where name = 'redo writing'; b.  If you want to increase or decrease the number of redo copy latches, modify the parameter _LOG_IO_SIZE in the init.ora file.  Once you have done this, you can bounce the database and execute the query in step a.