在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的原因。http://www.cndba.cn/dave/article/3144

在这篇MOS中还提到一点，可以尝试使用_high_priority_processes提高LGWR进程的优先级：http://www.cndba.cn/dave/article/3144http://www.cndba.cn/dave/article/3144

@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.

http://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://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/COMMIT_LOGGING.html
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/COMMIT_WAIT.htmlhttp://www.cndba.cn/dave/article/3144

commit_logging 参数：

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 参数：

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效果比较明显的情况。 老虎刘大师提供的分析数据如下：


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

在如果要求ACID的情况，不太可能将COMMIT_WAIT设置成NOWAIT，所以如果真要测试，可以尝试将commit_logging改成batch。http://www.cndba.cn/dave/article/3144

修改命令如下：http://www.cndba.cn/dave/article/3144

SQL> alter system set commit_logging=batch;
System altered.

SQL> alter system set commit_wait=nowait;
System altered.

SQL> show parameter commit_wait
NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
commit_wait                 string            NOWAIT

SQL> show parameter commit_logging
NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
commit_logging                 string            BATCH
SQL>

版权声明：本文为博主原创文章，未经博主允许不得转载。