Tune the Common Redo Log Operations

After sizing the redo log components, it's crucial to **monitor and address potential contention**. Here's a breakdown of why those factors are important and how to approach performance tuning:

1. init.ora Log Parameters:
   • log_buffer: This parameter defines the size of the redo log buffer in memory. A larger buffer can reduce contention for the allocation latch, as more processes can write redo entries concurrently. However, an excessively large buffer might not provide significant gains and could waste memory.
   • log_small_entry_max_size: As you mentioned, this parameter determines the maximum size of a redo entry that can be copied directly into the buffer while holding the allocation latch. Smaller entries copied this way reduce latch hold time and contention.
   • log_checkpoint_interval: This parameter controls how often checkpoints occur. Checkpoints write modified database buffers to disk and advance the checkpoint position in the online redo log files. Frequent checkpoints can reduce the time needed for instance recovery but might increase I/O activity.
   • log_checkpoint_timeout: Specifies the maximum time between checkpoints.
   • log_file_size: While you mentioned sizing the online redo logs, this parameter specifically sets their size. Proper sizing is crucial to avoid excessive log switching, which can impact performance.
2. Size of Online Redo Log Files:
   • Too small: Frequent log switches occur, leading to increased I/O and potential performance bottlenecks.
   • Too large: Can increase recovery time if a failure occurs.
3. Size of Archived Redo Log Filesystem:
   • Insufficient space: Can halt database operations as archive logs cannot be written, leading to transaction delays.
   • Inadequate I/O performance: Slow archiving can impact overall database performance.

Analyzing and Addressing Contention:

• Monitoring: Use Oracle's performance monitoring tools (like Statspack or AWR) to identify redo-related wait events, latch contention, and I/O performance issues.
• Latch Contention: If the "redo allocation" latch is a bottleneck, consider increasing log_buffer or log_small_entry_max_size.
• Log File Sync Waits: If waits for log file sync are high, review log_file_size and the I/O performance of the log file devices.
• Archive Log Performance: Monitor archive log generation rate and the time taken to archive logs. Address any bottlenecks in the archiving process, such as network speed or disk I/O.

By carefully tuning these parameters and addressing any performance bottlenecks, you can ensure that your redo log configuration is optimized for your workload.

You should set log_small_entry_max_size to a value smaller than the average value for your redo log entries. To determine the average size of your redo log entries, execute utlbstat-utlestat and use the information in report.txt to determine the average redo size as follows:

Here is an excerpt from report.txt:

Statistic                   Total        Per Transact Per Logon    Per Second
--------------------------- ------------ ------------ ------------ ------------
CPU used by this session           19931      3321.83      4982.75       205.47
.
.
.
redo blocks written                   84           14           21          .87
redo entries                          30            5          7.5          .31
redo size                          20615      3435.83      5153.75       212.53
redo small copies                     14         2.33          3.5          .14
redo synch time                        7         1.17         1.75          .07
redo synch writes                      5          .83         1.25          .05
redo wastage                        1727       287.83       431.75         17.8
redo write time                       16         2.67            4          .16
redo writes                            7         1.17         1.75          .07

In the example above, we see that the "redo size" (over the time between the BSTAT and the ESTAT) is 20,615. During this time, we see that there were 30 "redo entries". Hence, the average size of the redo is:

20,615
---------     = 687 bytes
    30

In this example, your log_small_entry_max_size should be at least 700 bytes. We also see the "redo small copies" entry. This indicates that out of 30 redo entries, only 14 were small copies. If the size of the redo is more than log_small_entry_max_size, then the user process must obtain a redo copy latch. After obtaining a redo copy latch the user process can release the allocation latch and then copy the redo while holding the redo copy latch.
The following query will detect any contention problems with the redo logs. If contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in the init.ora file.

PROMPT
PROMPT
PROMPT **********************************************************
PROMPT  LATCH SECTION
PROMPT **********************************************************
PROMPT If miss_ratio or IMMEDIATE_MISS_RATIO > 1 then  latch
PROMPT Contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
PROMPT
COLUMN "miss_ratio"           FORMAT .99
COLUMN "immediate_miss_ratio" FORMAT .99
SELECT substr(l.name,1,30) name,
       (misses/(gets+.001))*100 "miss_ratio",
       (immediate_misses/(immediate_gets+.001))*100
       "immediate_miss_ratio"
FROM v$latch l, v$latchname ln
WHERE l.latch# = ln.latch#
AND (
(misses/(gets+.001))*100 > .2
OR
(immediate_misses/(immediate_gets+.001))*100 > .2
)
ORDER BY l.name;
 
**********************************************************
LATCH SECTION
**********************************************************
If miss_ratio or IMMEDIATE_MISS_RATIO > 1 then  latch
Contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
NAME                           miss_ratio immediate_miss_ratio
------------------------------ ---------- --------------------
cache buffers chains           1.38                  .00

The next lesson explores how to tune redo log checkpoints.