oracle LGWR Trace Warning: Log Write Time

在10G中如果LGWR写出的时间超过500ms,LGWR的后台跟踪文件中将会记录一条警告信息。

如下所示：

[oracle@dbserver bdump]$ more dbserver_lgwr_13596.trc
/u01/admin/dbserver/bdump/dbserver_lgwr_13596.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle
System name:    Linux
Node name:      dbserver
Release:        2.6.9-51.ELsmp
Version:        #1 SMP Tue Mar 20 23:10:05 EDT 2007
Machine:        i686
Instance name: dbserver
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 13596, image: oracle@dbserver (LGWR)

*** SERVICE NAME:() 2011-05-06 21:27:43.086
*** SESSION ID:(1016.1) 2011-05-06 21:27:43.086
Maximum redo generation record size = 156160 bytes
Maximum redo generation change vector size = 150672 bytes
tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x10)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x10)
*** 2011-05-07 10:51:54.297
Warning: log write time 1010ms, size 1250KB
*** 2011-05-07 10:52:13.475
Warning: log write time 1060ms, size 1038KB
*** 2011-05-07 10:52:35.184
Warning: log write time 900ms, size 1024KB
*** 2011-05-07 10:53:15.978
Warning: log write time 1520ms, size 1029KB
*** 2011-05-07 10:55:40.894
Warning: log write time 1120ms, size 1028KB
*** 2011-05-07 10:56:03.352
Warning: log write time 1410ms, size 1027KB
*** 2011-05-07 10:56:21.855
Warning: log write time 1240ms, size 1030KB
*** 2011-05-07 10:56:40.694
Warning: log write time 640ms, size 1024KB
*** 2011-05-07 10:57:01.076
Warning: log write time 1230ms, size 1024KB
*** 2011-05-07 10:57:21.276
Warning: log write time 830ms, size 1030KB
*** 2011-05-07 10:59:08.513

*** 2009-10-19 13:13:51.534
Warning: log write time 720ms, size 3KB
*** 2009-10-20 10:33:27.372
Warning: log write time 510ms, size 2KB
*** 2009-10-21 02:00:01.159
Warning: log write time 10521085310ms, size 8KB
*** 2009-10-22 10:35:57.932
Warning: log write time 570ms, size 1KB
*** 2009-11-03 10:36:52.851
Warning: log write time 610ms, size 2KB
*** 2009-11-03 10:36:54.938
Warning: log write time 860ms, size 2KB
*** 2009-11-12 08:23:25.525
Warning: log write time 750ms, size 1KB

事实上不可能是这么长的等待，因此我想这是Oracle诊断输出的一个Debug信息，忘了去掉而已吧。

而在11G中ORACLE，跟踪文件中还附带了解决方法，如下所示：

[oracle@yikaiapp trace]$ more metisdb_lgwr_13085.trc
Trace file /u01/app/oracle/diag/rdbms/metisdb/metisdb/trace/metisdb_lgwr_13085.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/db11gr2
System name:    Linux
Node name:      yikaiapp
Release:        2.6.18-194.26.1.el5
Version:        #1 SMP Tue Nov 9 12:54:20 EST 2010
Machine:        x86_64
Instance name: metisdb
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 13085, image: oracle@yikaiapp (LGWR)

*** 2011-05-08 10:05:47.726
*** SESSION ID:(855.1) 2011-05-08 10:05:47.726
*** CLIENT ID:() 2011-05-08 10:05:47.726
*** SERVICE NAME:(SYS$BACKGROUND) 2011-05-08 10:05:47.726
*** MODULE NAME:() 2011-05-08 10:05:47.726
*** ACTION NAME:() 2011-05-08 10:05:47.726
 
Warning: log write elapsed time 1396ms, size 4KB
 (set event 10468 level 4 to disable this warning)
*** 2011-05-08 18:05:58.111
Warning: log write elapsed time 1682ms, size 10KB
 (set event 10468 level 4 to disable this warning)
*** 2011-05-08 21:05:56.083
Warning: log write elapsed time 2574ms, size 11KB
 (set event 10468 level 4 to disable this warning)
*** 2011-05-08 22:24:35.700
Warning: log write elapsed time 1640ms, size 1KB
 (set event 10468 level 4 to disable this warning)
*** 2011-05-09 08:44:05.553
*** 2011-05-09 08:44:05.719
Warning: log write elapsed time 2394ms, size 0KB
 (set event 10468 level 4 to disable this warning)

SQL> alter system set event= '10468 trace name context level 4' scope=spfile;

System altered.