RelationalDBDesign RelationalDBDesign

Tuning Instance   «Prev  Next»
Lesson 9 Tune the common redo log operations
Objective Change parameters to relieve redo log contention.

Tune the Common Redo Log Operations

After we have properly sized the redo log buffer, the online redo logs, and the archived redo log file system, we next need to look for any contention that might occur as a result of our sizes or init.ora parameters.

Factors in redo log performance

The following factors influence the performance of the redo logs:
  1. The init.ora log parameters
  2. The size of the online redo log files
  3. The size of the archived redo log filesystem

Before a user process performs a block change, it must create the redo record in the log buffer. The allocation latch controls the allocation of space in the redo log buffer. The user process must acquire this latch and may copy while holding it if the size of the redo entry is less than the log_small_entry_max _size init.ora parameter. The default value is 80 bytes.

Determine the average redo size

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:

Average size of redo = redo size/ redo entries

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:
---------     = 687 bytes

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 If miss_ratio or IMMEDIATE_MISS_RATIO > 1 then  latch
PROMPT Contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
COLUMN "miss_ratio"           FORMAT .99
COLUMN "immediate_miss_ratio" FORMAT .99
SELECT substr(,1,30) name,
       (misses/(gets+.001))*100 "miss_ratio",
FROM v$latch l, v$latchname ln
WHERE l.latch# = ln.latch#
(misses/(gets+.001))*100 > .2
(immediate_misses/(immediate_gets+.001))*100 > .2
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.