LGWR TRACE Warning: log write time

同学们大概偶尔也会在事务繁忙的Oracle数据库的LGWR日志进程的TRACE里找到如下的内容:

Warning: log write time 1730ms, size 155KB
*** 2011-09-12 22:56:05.407
Warning: log write time 810ms, size 1KB
*** 2011-09-12 22:56:06.990
Warning: log write time 1590ms, size 154KB
*** 2011-09-12 22:56:07.670
Warning: log write time 600ms, size 109KB
*** 2011-09-12 22:56:09.903
Warning: log write time 1790ms, size 190KB
*** 2011-09-12 22:56:11.704
Warning: log write time 1800ms, size 126KB
*** 2011-09-12 22:56:12.854
Warning: log write time 710ms, size 70KB

这些TRACE记录说明LGWR在写出REDO BUFFER到REDO LOGFILE联机日志文件时单次的日志写时间超过了500ms:

The above warning messages has been introduced in 10.2.0.4 patchset.

The warning message will be generated only if the log write time is more than 500 ms and it will be written to the lgwr trace file.

Solution

These messages are expected in a 10.2.0.4 database in case the log write is more than 500 ms.
This is a warning which means that the write process is not as fast as it intented to be.

So, probably you need to check if the disk is slow or not or for any potential OS causes.
If everything looks fine at the hardware level or OS level then you can safely ignore these messages.
The trace file can easily be deleted or truncated.

Also, according to Bug:7559549 , these trace can be disabled by setting event 10468 level 4.”

该TRACE信息由10.2.0.4 patchset引入,当LGWR写日志文件单次超过500ms时触发写”Warning: log write time “信息到lgwr trace,如果该信息频繁出现可能意为这潜在的OS IO问题(如果TOP等待事件中还有log file sync、gc buffer busy则值得注意该LGWR IO缓慢问题), 如果在硬件或OS层面都没有问题、且该日志记录出现并不频繁则可以忽略该问题,也可以通过设置10468 EVENT LEVEL 4来禁止写出”Warning: log write time “警告信息。

实际控制这里500ms的是_long_log_write_warning_threshold(threshold for long log write warning messages in ms)隐藏参数,该参数可以在线设置:

SQL> alter system set “_long_log_write_warning_threshold”=2000 ;

System altered.

Comments

  1. tonyliu says

    刘大,为什么我在执行命令的时候,报错了呢?SQL> alter system set “_long_log_write_warning_threshold”=2000 ;alter system set “_long_log_write_warning_threshold”=2000 *ERROR at line 1:ORA-02065: illegal option for ALTER SYSTEM什么地方不对,请刘大指点。。。

  2. tonyliu says

    刘大,我的出错版本是 10.2.0.5 的版本, 应该是11g之后才支持 alter system set “_long_log_write_warning_threshold”=2000 ;这种修改方式吧, 10g 里面不支持。

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号