log file
sync:
Question: I have "log file sync waits" in my top-5 timed
events.
Answer: The log file sync wait occurs at the
end of a transaction and the database writer (DBWR) must wait for
the log file sync.
"Before writing a batch of database blocks, DBWn finds the highest
high redo block address that needs to be synced before the batch
can be written. DBWn then takes the redo allocation latch to ensure
that the required redo block address has already been written by
LGWR, and if not, it posts LGWR and sleeps on a log file sync
wait."
log file sync metalink上解释：
"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 redo
logfile. The user session will post the LGWR to write all redo
required from the log buffer to the redo log file. When the LGWR
has finished it will post the user session. The user session waits on this wait event while waiting
for LGWR to post it back to confirm all redo changes are safely on
disk.
This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally).
See Reducing Waits section below for more detailed breakdown of
this wait event.
再来看看eygle写的log file sync 等待事件：
当一个用户提交(commits)或者回滚(rollback),session的redo信息需要写出到redo
logfile中.
用户进程将通知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/redo writes)*512 bytes
如果系统产生redo很多，而每次写的较少，一般说明LGWR被过于频繁的激活了.
可能导致过多的redo相关latch的竞争,而且Oracle可能无法有效的使用piggyback的功能.
Log file parallel write asktom上的解释：
Log file parallel write is writing redo records to the redo log files from the log buffer.
Parameters:
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
This wait event is used in both IDLE and NON-IDLE conditions
so can be very misleading.
插入表情