| 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:
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.
- The init.ora log parameters
- The size of the online redo log files
- 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:
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.
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.
