Redo Log Buffer 优化.docx

xuexiacao123

恩墨学院

13页

7次

2025-10-29

5墨值下载

Sizing the Redo Log Buer

The default size of the redo log buffer is calculated as follows:

COPYMAX(0.5M, (128K * number of cpus))

Applications that insert, modify, or delete large volumes of data may require changing the

default size of the redo log buffer. Oracle recommends setting the redo log buffer size to

minimum of 8 MB. Set it to a minimum of 64 MB for databases using flashback

functionality and having 4GB or higher SGAs. Set it to a minimum of 256 MB if you are

using Oracle Data Guard with asynchronous redo transport and have a high redo

generation rate.

To determine if the size of the redo log buffer is too small, monitor the redo log buffer

statistics, as described in "Using Redo Log Buffer Statistics". You can also check if

the log buffer space wait event is a significant factor in the wait time for the database

instance. If it is not, then the log buffer size is most likely adequately-sized.

To size the redo log buffer:

1. Set the size of the redo log buffer by setting the value of the

LOG_BUFFER initialization parameter to the required size.

The value of this parameter is expressed in bytes.

Note

The size of the redo log buffer cannot be modified after instance startup.

Using Redo Log Buer Statistics

The REDO BUFFER ALLOCATION RETRIES statistic reflects the number of times a

user process waits for space in the redo log buffer. This statistic can be queried using

the V$SYSSTAT performance view.

You should monitor the redo buffer allocation retries statistic over a period while the

application is running. The value of this statistic should be near zero over an interval. If

this value increases consistently, then it means user processes had to wait for space in the

redo log buffer to become available. The wait can be caused by the redo log buffer being

too small or by checkpointing. In this case, consider one of the following options:

1. Increase the size of the redo log buffer, as described in

"Sizing the Redo Log Buffer"

2. Improve the checkpointing or archiving process

Example 13-5 shows a query of the V$SYSSTAT view for this statistic.

Example 13-5 Querying the V$SYSSTAT View

COPYSELECT name, value

FROM V$SYSSTAT

WHERE name = 'redo buffer allocation retries';

设置在 Oracle 19c 中设置 LOG_BUFFER 参数需要谨慎操作，下面为您详细介绍设置方法和

最佳实践。

1. 理解 LOG_BUFFER 参数

LOG_BUFFER 是 SGA 中用于缓存重做记录的内存区域，在以下情况发生时由 LGWR 进程

写入在线重做日志文件：

1. 提交事务时（commit）

2. 日志缓冲区满 1/3 时

3. 每 3 秒超时

4. DBWR 进程需要时

2. 检查当前 LOG_BUFFER 设置

-- 查看当前 LOG_BUFFER 大小

SELECT name, value/1024/1024 AS "Size_MB", isdefault

FROM v$parameter

WHERE name = 'log_buffer';

-- 查看 SGA 相关信息

SELECT * FROM v$sga;

-- 检查日志缓冲区分配情况

SELECT * FROM v$sgastat WHERE name = 'log_buffer';

-- 查看日志缓冲区空间使用统计

SELECT * FROM v$log_buffer_stat;

3. 评估当前性能状况

-- 检查日志文件同步等待事件

SELECT event, total_waits, time_waited, average_wait

FROM v$system_event

WHERE event = 'log file sync';

-- 检查日志缓冲区不足的等待

SELECT * FROM v$system_event

WHERE event LIKE 'log buffer space%';

-- 检查重做日志统计信息

SELECT name, value

FROM v$sysstat

WHERE name IN (

'redo entries',

'redo size',

'redo buffer allocation retries',

'redo log space requests'

of 13

5墨值下载

log_buffer优化

关注

评论