About redo log wait events
log file sync 、log file parallel write wait event
log file sync wait 默认是发生在前台进程发进commits时比如用户commit,DDL,递归操作发生在dictionary table 上的DML,同时rollback 也会导致比如执行user/application rollback,还有一些数据库特性导致的内部rollback transaction、kill session，cancelled queries,space allocation/ASSM problems etc..
下面是commit 操作logfile的一个理论上的流水线
1) user issues a commit
2) foreground processes posts LGWR
3) LGWR issues the physical write syscall
4) the physicall write syscall completes
5) LGWR posts the foreground processes
6) commit complete
A) FG post LGWR and goes to sleep,”log file sync” wait starts
B) LGWR wakes up ,gets onto CPU, issues the IO requests, LGWR goesto sleep,waiting for “log file parallel write”;
C) Hardware completes the IO and OS wakes up LGWR,LGWR gets onto CPU,Marks “log file parallel write” event complete and posts the FG
D) Foreground process is woken up by LGWR post,Foreground process gets onto CPU and completes the “log file sync” wait
大部分的log file sync 时间花费在了log file parallel write 上，另外在有些版本中log file parallel write使用的不恰当比如11.2.0.1 on Linux，logfile sync 在AWR 报告出现时应注意Avg wait (ms)列,log file syny avg wait应该在3ms 或更小,也可以参考v$event_histogram，对于log file parallel write 可以使用Unbuffered concurrent IO或者用RAW Devices。
log buffer space wait event
log buffer space wait不是commit问题,发生在redo log buffer刷出到disk.发生的原因Slow IO subsystem; CPU 繁忙没有足够的CPU TIME给LGWR，也有时发生在大事务执行期间,还有可能是log buffer 过小.
log file single wait event
通常发生在logfile header blcok 的读写上，原因有log switch 频繁，正常应该保持在30分钟左右，归档也会更新logfile 文件头。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。