wait event :log file sync & log file parallel write

Yong Huang · 发表于 2009-10-21 01:16

原帖由 liangpd 于 2009-10-19 21:32 发表
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.

Easy. Check v$session_event to see what sessions frequently experience 'log file sync' waits. Or just keep watching v$session_wait.

Supplement that with 'user commits' stats. That is, find the statistic# in v$statname (or v$sysstat) for 'user commits'. Check v$sesstat to see which session has very high increase in value for this statistic within a short time.

Yong Huang

lixunpeng · 发表于 2010-1-22 18:16

原帖由 Yong Huang 于 2009-10-21 01:16 发表

Easy. Check v$session_event to see what sessions frequently experience 'log file sync' waits. Or just keep watching v$session_wait.

Supplement that with 'user commits' stats. That is, find the statistic# in v$statname (or v$sysstat) for 'user commits'. Check v$sesstat to see which session has very high increase in value for this statistic within a short time.

Yong Huang

开发人员说为了保证交易被记录住，只能频繁提交。那么就只能提高磁盘io了吧？现在是5块盘的raid5，应该是用raid10存log了。

Yong Huang · 发表于 2010-1-23 04:33

原帖由 lixunpeng 于 2010-1-22 04:16 发表

开发人员说为了保证交易被记录住，只能频繁提交。那么就只能提高磁盘io了吧？现在是5块盘的raid5，应该是用raid10存log了。

Yes, RAID 10 is the best for online redo logfiles. RAID 5 is very bad for this purpose.

In rare cases, unusually high "log file sync" waits are due to bugs. Search on MOS.

Yong Huang

jowvid · 发表于 2010-4-24 21:46

看来真是IO瓶颈的问题?但还有其他的方法吗？例如，nologging 是否也可以减少log buffer 写入redo log？我记得按照Oracle的要求，一般一个归档要求20分钟以上。但是在频繁提交的事务要求下，实际上往往几分钟就产生switch logfile了。往往是这种原因造成的log file sync 等待吧？

[ 本帖最后由 jowvid 于 2010-4-24 21:51 编辑 ]

Yong Huang · 发表于 2010-4-25 00:52

If log write is sporadic instead of continuous, a large SAN cache helps.

Nologging helps if certain operations contribute to most of redo generation, such as CTAS, direct-path insert. Redo for regular DML won't be reduced. When the redo is reduced (for those certain operations), it won't even go into log buffer. (So it's not that 减少log buffer 写入redo log files.)

> 几分钟就产生switch logfile了。往往是这种原因造成的log file sync 等待吧?

No. Increasing the size of redo logfiles can prolong log file switch, but won't reduce log file sync.

Yong Huang