15.9 How do I…Determine the best size for the redo log buffer?

Problem

I accepted the default value for the LOG_BUFFER initialization parameter, which determines the size of the redo log buffer. How can I tell if the size of the redo log buffer is sufficient?

Technique

User processes that make database changes signal server processes to write redo entries into the redo log buffer. Oracle uses these entries during recovery operations to reconstruct database changes. Every time the Log Writer (LGWR) background process “wakes up,” it writes the contents of the redo log buffer to the disk-based redo log files. Specifically, it writes every redo entry in the buffer that user processes have copied there since the last LGWR operation. This frees redo log buffers for subsequent writes from user processes. More than one event can trigger LGWR, but at the very least, LGWR writes every three seconds.

Usually, LGWR is fast enough to keep up with user processes’ database changes. If it is not, then user processes may have to wait for free buffers in the redo log buffer, and this degrades performance.

The UTLESTAT report contains some statistics that can help you identify redo log buffer waits. Listing 15.11 contains the relevant section from the report.

Listing 15.11 An excerpt from the UTLESTAT report summarizing redo log buffer waits

Statistic Total Per Transact Per Logon
------------ ------------ ------------ ------------
redo entries 4686 16.33
60.08 redo log space
requests 2 .01 .03

The value in the Total column for the statistic redo log space requests should be close to zero. If it is not, then try increasing the value of the initialization parameter LOG_BUFFER until the statistic is near zero, indicating few waits for redo log buffers.

Alternatively, use the dynamic performance table V$SYSSTAT which appeared previously in Table 15.4.

Steps

1. Start SQL*Plus and connect as the WAITE user. Use the START command to load and execute the query in CHP15_15.SQL. The query and its result appear in Figure 15.11.

How It Works

CHP15_11.SQL queries the V$SYSSTAT dynamic performance view. If the value of the redo log space requests statistic is significantly different from 0, then increase the size of the LOG_BUFFER initialization parameter.

Comments

Another alternative involves the V$SYSTEM_EVENT dynamic performance view which appears in Table 15.9.

Table 15.9 The structure of the V$SYSTEM_EVENT table

Column Column Description
EVENT Event name
TOTAL_WAITS Total waits for EVENT
TOTAL_TIMEOUTS Total timeouts for EVENT
TIME_WAITED Time waited for EVENT (in hundredths of a second)
AVERAGE_WAIT Average time waited for EVENT (in hundredths of a second)

One of the events in the V$SYSTEM_EVENT table is log file space/switch. If the value of TOTAL_WAITS is significantly and consistently different than zero, then consider increasing the value size of the redo log buffer. Figure 15.12 contains the result of the SQL script contained in CHP15_16.SQL.

Note-The statistics in V$SYSTEM_EVENT will not be meaningful unless the TIMED_STATISTICS initialization parameter is set to TRUE. Also, if there are no waits for a particular event, then that event will not appear in the V$SYSTEM_EVENT dynamic performance view.

The query includes the timing information from the TIME_WAITED and AVERAGE_WAIT columns to put this tuning effort into perspective. Unless response time is extremely precious at your installation, redo log buffer waits usually will not degrade performance noticeably.