wait event :log file sync & log file parallel write

wing hong

<< 写logfile这个过程，log buffer可以继续被修改其他进程修改嘛？
At least in 8i, when the  server process  wants to write to the log buffer, it must have the "redo allocatiion" latch. However, when  LGWR wants to  write the log buffer to  the  log file, it also  requests the "redo  allocation"  latch.   Thus the LGWR will prevent other foreground process to allocate more space in the log buffer.

But the LGWR doesn't request the  "redo copy" latch.  There fore other  server  process still can  write to the other  area  of log  buffer.  LGWR will wait for other  server processes to finish the  copy.

( when  a  server  process needs to write to the  log buffer, it must have the  redo copy latch first, then get  the redo allocation latch , then release  redo allocation latch,  do the copy from its pga to  log buffer, then release  the redo copy latch  ) .

So in summary, Yes , it is possible for other server processes to continue to write to  log buffer , but only for  the  purpose of finishing the  previous request.

>> Log file sync很高得情况，降低log buffer有效嘛？
I think the most important point is to  increase the disk IO speed of  the  log file.  And ofcourse you should n't set the log buffer to a high value in the first place.  I doubt  there is much use of tuning the size of log buffer  if it  is not too high . But anyhow, if some one has good experience  on this , please post.

小新

###So in summary, Yes , it is possible for other server processes to continue to write to log buffer , but only for the purpose of finishing the previous request.###
thanks for your answer,wing hong.
我还是用中文习惯，呵呵
你的意思是server processes能够继续写log buffer是仅限那些已经在lgwr进行写之前就获得log copy latch的server processes吗？

Yong Huang

最初由 小新 发布
[B]###So in summary, Yes , it is possible for other server processes to continue to write to log buffer , but only for the purpose of finishing the previous request.###
thanks for your answer,wing hong.
我还是用中文习惯，呵呵
你的意思是server processes能够继续写log buffer是仅限那些已经在lgwr进行写之前就获得log copy latch的server processes吗？ [/B]

That's exactly what wing hong's message says.

BTW, I used to think LGWR writes the entire log buffer. Not sure why I thought so. That's not right. If your LGWR writes often, every write will be small so it's not that big a problem, although it still causes redo allocation latch to be held and a short log file sync wait for server processes.

The best way to make LGWR run faster is to use raw disks for logfiles and of course use the fastest disks. If you commit often as on OLTP, making a big log_buffer is not as bad as before. It just wastes a little bit memory.

Yong Huang

dazhoufei

正想深入了解这个问题用gogoole搜索，除了oracle官方网站，还是回到老家IYPUB ：）
都是大侠在讨论问题呀，学习！

qucongren

这个帖子不错,比较深入...顶一把

jason_xiong

总结：
1、凡是出现log file sync & log file parallel write的情况，大部份时间可以判定是IO有问题。
2、调整的办法，使用RAID 0＋1，使用光纤磁盘的存储设备，将几张常常要用到的大表分区、物化视图、想办法优化读写速度、减小LOG BUFFER的大小，缩短SCAN的时间
  （因为，ALTER或日志切换、满时都会触发LGWR写回磁盘，从全局来考虑性能调整更好，洪先生，您说对吗？肯请指教）

                                                                                            —xiong .  qiang

Yong Huang

原帖由 jason_xiong 于 2008-10-30 21:38 发表
总结：
1、凡是出现log file sync & log file parallel write的情况，大部份时间可以判定是IO有问题。
2、调整的办法，使用RAID 0＋1，使用光纤磁盘的存储设备，将几张常常要用到的大表分区、物化视图、想办法优化读写速度、减小LOG BUFFER的大小，缩短SCAN的时间
  （因为，ALTER或日志切换、满时都会触发LGWR写回磁盘，从全局来考虑性能调整更好，洪先生，您说对吗？肯请指教）

                                                                                            —xiong .  qiang

Comments on your summary.

1. Most of the time it's because applications unnecessarily commit. If the developers can change the code to do commit only when it has to, log file sync can drop a lot.

2. If you can afford RAID 0+1, why not do RAID 1+0, which has the additional resilience against disk crash?

Yong Huang

jason_xiong

大哥，您还是用中文吧！   不懂英文啊

liangpd

1. Most of the time it's because applications unnecessarily commit. If the developers can change the code to do commit only when it has to, log file sync can drop a lot.

你意思是Most of the time it's because applications unnecessarily commit, 那有什么办法从数据库这边找出那些应用有问题?到底是什么应用导致了high file log syn? txn.

lovecinder

一个帖子从2003年讨论到2009年，太NB了