OCP考题解析_043:log file sync 和 log file parallel write

最新推荐文章于 2021-07-19 22:44:44 发布

iteye_14608

最新推荐文章于 2021-07-19 22:44:44 发布

阅读量125

点赞数

文章标签：数据库

sys@ORCL> select EVENT,TOTAL_WAITS,TIME_WAITED,TIME_WAITED_MICRO
  2         from v$system_event
  3        where event like '%log file%';

EVENT                          TOTAL_WAITS TIME_WAITED TIME_WAITED_MICRO
------------------------------ ----------- ----------- -----------------
log file sequential read                 8           4             35571
log file single write                    8           1              9462
log file parallel write                968        4672          46715069
log file sync                          300        3734          37336558

log file parallel write如果大于10ms就有可能I/O有问题
log file parallel write 跟提交木有关系，提交是 log file sync，不提交也会写redo log

log file sync等待事件是用户发出提交或回滚声明后,等待提交完成的事件,提交命令会去做日志同步,也就是写日志缓存到日志文件
在提交命令未完成前,用户将会看见此等待事件,注意,它专指因提交,回滚而造成的写缓存到日志文件的等待
当发生此等待事件时,有时也会伴随log file parallel write
因为此等待事件将会写日志缓存，如果日志的I/O系统较为缓慢的话,这必将造成log file parallel write 等待
当发生log file sync等待后,判断是否由于缓慢的日志I/O造成的,可以查看两个等待事件的等待时间
如果比较接近,就证明日志I/O比较缓慢或重做日志过多
这时,造成log file sync的原因是因为log file parallel write,可以参考解决log file parallel write的方法解决问题
如果log file sync的等待时间很高,而log file parallel write的等待时间并不高
这意味着log file sync的原因并不是缓慢的日志I/O,而是应用程序过多的提交造成的

log file sync常常和log file parallel write一般同时关注，大致的发生过程如下：

① server process接到提交请求
② server process通知LGWR写Redo entries到Redo log file
③ LGWR写Redo到文件
④ LGWR写完通知server process
⑤ server process收到写完成的通知，向用户端发送提交完成
从步①开始，server process开始等待log file sync，到步⑤结束
步③、步④，LGWR等待log file parallel write
log file sync是按会话累计的，而log file parallel write不是
所以，如果log file parallel write慢，导致的放大效应很明显，十个SP等一个LGWR进程写磁盘，log file sync的等待时间就被放大了十倍
那么，有时log file sync比log file parallel write大很大，便有两个原因，一是CPU紧张，二是同时提交的进程多
如果10个进程同时提交，每个进程等0.1秒，加起来就一共等了1秒，而log file parallel write只会有一个进程等待，就是LGWR

这两个等待事件属于I/O范畴，可结合vmstat和iostat查看I/O状况

OCP考题：

In your production database, the total waits and the time waited for log 
file parallel write are significantly high. While investigatingthe 
reason, you find that there are three redo log groups with two members 
in each group, and all redo log members are places on a single physical 
disk. What action would you take to minimize the waits?

A. Start the log writer slave processes 
B. Increase the number of redo log files 
C. Increase the size of the redo log buffer 
D. Place the redo log files on the different disks. 
E. Increase the number of log writer processes. 

Answer:D