oracle commit_log,Oracle log file sync 等待事件与 COMMIT_WAIT，COMMIT_LOGGING 参数说明

最新推荐文章于 2022-04-17 18:42:47 发布

学术期刊编辑

最新推荐文章于 2022-04-17 18:42:47 发布

阅读量737

点赞数

文章标签： oracle commit_log

在Oracle 数据库中，log file sync是一个非常常见的等待事件，导致该事件的原因主要有2个因素：一是commit提交过于频繁，二是redo log 对应的IO根不上。所以对于log file sync等待事件我们通常考虑的是用SSD 来提升IO，增加online redo log 文件的大小，或者从业务侧降低commit频率。

在MOS文章：RAC 环境中最常见的 5 个数据库和/或实例性能问题 (文档 ID 1602076.1)中也详细分析了log file sync的原因。https://www.cndba.cn/dave/article/3144

在这篇MOS中还提到一点，可以尝试使用_high_priority_processes提高LGWR进程的优先级：

@For Support Only: Renice LGWR to run at higher priority or run LGWR in RT class by adding LGWR to the parameter: _high_priority_processes=’VKTM|LMS|LGWR”. Consider doing this only if log file sync is high and scheduling delay of LGWR is found to be causing it. Be prepared to test it thoroughly.https://www.cndba.cn/dave/article/3144https://www.cndba.cn/dave/article/3144

在MOS文章：Alternative and Specialised Options as to How to Avoid Waiting for Redo Log Synchronization (文档 ID 857576.1) 中提到另外2个参数：COMMIT_WAIT和COMMIT_LOGGING 可以缓解这个问题。https://www.cndba.cn/dave/article/3144

commit_logging 参数：

https://www.cndba.cn/dave/article/3144

COMMIT_LOGGING = { IMMEDIATE | BATCH }

COMMIT_LOGGING is an advanced parameter used to control how redo is batched by Log Writer.

If COMMIT_LOGGING is altered after setting COMMIT_WAIT to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.

#COMMIT_LOGGING设置IMMEDIATE表示每个commit都触发redo条目写(默认缺省设置)；batch的话就是redo条目批量写，在大批量commit情况下会有性能提升表现。

commit_wait 参数：

https://www.cndba.cn/dave/article/3144

COMMIT_WAIT = { NOWAIT | WAIT | FORCE_WAIT }

Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.

# COMMIT_WAIT设置wait表示redo条目写入磁盘后才会回应服务进程，NOWAIT表示不等，此时如果数据库异常关闭，那么会影响ACID中持久性(D)。

If the parameter is set to FORCE_WAIT, the default behavior (immediate flushing of the redo log buffer with wait) is used. If this is a system setting, the session level and transaction level options will be ignored. If this is a session level setting, the transaction level options will be ignored. If COMMIT_WAIT is altered after it has been set to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.

在MOS文档：High Log File Sync Wait Due to LGWR with Slow io_submit() (文档 ID 2400987.1)中提到，可以设置：

SQL> ALTER [SYSTEM | SESSION] SET commit_logging=batch;

来缓解log file sync，但根据老虎刘大师的分析，batch 要配合nowait 使用效率才明显，如果只是修改成batch，效果不明显，但网上也有案例说只将commit_logging修改成batch效果比较明显的情况。老虎刘大师提供的分析数据如下：