log file sync and log file parallel wait event

log file syncasktom上的解释：

 

log file sync is a client waitevent.  It is the wait event your clients wait onwhen they say "commit".  It is the wait for LGWRto actually write their redo to disk and return back tothem.  You can "tune" this by making lgwr faster(no raid 5 for example) and committing less frequently andgenerating less redo (BULK operations generate less redo than rowby row do)

log filesync:
Question: I have "log file sync waits" in my top-5 timedevents.  How do I tune to reduce the log file syncwait events?

Answer: The log file sync wait occurs at theend of a transaction and the database writer (DBWR) must wait forthe log file sync. Oracle guru Steve Adams notesdetails on how Oracle processes log file sync waits:
"Before writing a batch of database blocks, DBWn finds the highesthigh redo block address that needs to be synced before the batchcan be written. DBWn then takes the redo allocation latch to ensurethat the required redo block address has already been written byLGWR, and if not, it posts LGWR and sleeps on a log file syncwait."

 

log file syncmetalink上解释：

 

"log file sync" Reference Note
When a user session(foreground process) COMMITs (or rolls back),the session's redo information needs to be flushed to the redologfile. The user session will post the LGWR to write all redorequired from the log buffer to the redo log file. When the LGWRhas finished it will post the user session. The user session waits on this wait event while waitingfor LGWR to post it back to confirm all redo changes are safely ondisk.

This may be described further as the time usersession/foreground process spends waiting for redo to be flushed tomake the commit durable. Therefore, we may think of these waits ascommit latency from the foreground process (or commit clientgenerally).

See Reducing Waits section below for more detailed breakdown ofthis wait event.

 

再来看看eygle写的log file sync等待事件：

 

当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redologfile中.
用户进程将通知LGWR执行写出操作,LGWR完成任务以后会通知用户进程.
这个等待事件就是指用户进程等待LGWR的写完成通知.

对于回滚操作，该事件记录从用户发出rollback命令到回滚完成的时间.

如果该等待过多，可能说明LGWR的写出效率低下，或者系统提交过于频繁.
针对该问题，可以关注:
log file parallel write等待事件
user commits,user rollback等统计信息可以用于观察提交或回滚次数

解决方案:
1.提高LGWR性能
尽量使用快速磁盘，不要把redo log file存放在raid 5的磁盘上
2.使用批量提交
3.适当使用NOLOGGING/UNRECOVERABLE等选项

可以通过如下公式计算平均redo写大小：

avg.redo write size = (Redo block written/redowrites)*512 bytes

如果系统产生redo很多，而每次写的较少，一般说明LGWR被过于频繁的激活了.
可能导致过多的redo相关latch的竞争,而且Oracle可能无法有效的使用piggyback的功能.

Log file parallel writeasktom上的解释：

Log file parallel write is writingredo records to the redo log files from the logbuffer.  The wait time is the wait for LGWR tocomplete multiple log file writes. Even though they are written inparallel, one needs to wait for the last I/O to be on disk beforethe parallel write is complete.

Parameters:
              P1 - No. of log members.
              P2 - No. of log blocks.
              P3 - No. of i/o requests

       files   Number of files writtento.
       blocks  Number of blocks to be written.
       requests Number of I/O requests.

P2 blocks will be written to each log member, in P3 number of
requests. i.e. If there were 2 requests, there would be two
physical writes per log member. The no. of log blocks will be
divided up between the requests.

IO done, this event is used by the IO slave processes (oracleInnn)when waiting for slave
IO processes to complete a request 

or by idleslave IO processes waiting for work.

This wait event is used in both IDLE and NON-IDLE conditions
so can be very misleading.