1.看了文章：http://www.itpub.net/thread-926325-1-9.html，下面是摘录，相当精彩
关于设置Log Buffer Size的困惑
大家好，从Oracle的资料上看，Redo写的触发条件中有如下两条：
1）1/3Redo Buffer满
2）Log Buffer中有1M的脏数据时
而Oracle实际上是按照Min(1/3Redo Buffer, 1M)来确定写Redo File的，所以，是否可以得到如下一个结论：
Log_buffer的设置超过3M就没有什么意义了？请各位老大指正一下，谢谢
个人认为没有必要太在意log buffer的大小，10M左右就行了，按oracle的机制，3m就可以了，多了也没什么用，之所以比3m多一些，是考虑可能对瞬间产生大量的redo有缓冲作用吧（自己想当然，没有找到依据）。
最需要关注的应该是LGWR的写入速度，提高redo logfile所在磁盘读写速度才是关键。
----------------
The most in-depth analysis on this type of classical issues is done by Steve Adams (ixora.com.au). His articles won't be easy to read.
3M is a good number according to your logic. But as others said, if instantaneous redo burst happens on your database, using a bigger number is OK. I noticed when Oracle does TPC benchmark, their log_buffer is set to tens of MB.
But whether you need to increase or decrease it should be based on whether you have 'log buffer space' wait. A rule of thumb is to set log_buffer a little larger than the number below which you start to see that wait. Too big a number is said to cause too much 'log file sync' wait even when application commit rate is not too high.
In 10g, log_buffer is unlikely a small number as viewed in "show sga". It has its own algorithm to calculate redo buffers.
Yong Huang
---------------
哦，总结各位的观点：
1、大于3M的log buffer是为了防止瞬间产生较大的redo log
2、当出现log buffer space等待事件时，需要增大log buffer
3、当出现log file sync等待事件时，需要减小log buffer
我的理解对吗？
-----------------
Point 3 is not that simple. Most log file sync waits are caused by too frequent commits and so reducing log buffer won't have much effect.
With 10g, tuning log buffer becomes less important.
By the way, the reason having a >3M log buffer for instantaneous redo surge is because LGWR doesn't check the 3M threshold until it reaches its timer (3 seconds I think). So "instantaneous" means a very short time much shorter than 3 seconds.
Yong Huang
---------------------
根据以下LGWR写触发条件 ， log buffer设置 0.5m～3m 即可， 主要还是redo log file大小的调整 。
1. When a transaction commits
2. When the redo log buffer becomes one-third full
3. When there is more than a megabyte of changed records in the redo log buffer.
4. When timeout occurs(every 3 seconds)
5. check point
6. Before the DBWn writes modified blocks in the database buffer cache to the data files.
关于设置Log Buffer Size的困惑
大家好，从Oracle的资料上看，Redo写的触发条件中有如下两条：
1）1/3Redo Buffer满
2）Log Buffer中有1M的脏数据时
而Oracle实际上是按照Min(1/3Redo Buffer, 1M)来确定写Redo File的，所以，是否可以得到如下一个结论：
Log_buffer的设置超过3M就没有什么意义了？请各位老大指正一下，谢谢
个人认为没有必要太在意log buffer的大小，10M左右就行了，按oracle的机制，3m就可以了，多了也没什么用，之所以比3m多一些，是考虑可能对瞬间产生大量的redo有缓冲作用吧（自己想当然，没有找到依据）。
最需要关注的应该是LGWR的写入速度，提高redo logfile所在磁盘读写速度才是关键。
----------------
The most in-depth analysis on this type of classical issues is done by Steve Adams (ixora.com.au). His articles won't be easy to read.
3M is a good number according to your logic. But as others said, if instantaneous redo burst happens on your database, using a bigger number is OK. I noticed when Oracle does TPC benchmark, their log_buffer is set to tens of MB.
But whether you need to increase or decrease it should be based on whether you have 'log buffer space' wait. A rule of thumb is to set log_buffer a little larger than the number below which you start to see that wait. Too big a number is said to cause too much 'log file sync' wait even when application commit rate is not too high.
In 10g, log_buffer is unlikely a small number as viewed in "show sga". It has its own algorithm to calculate redo buffers.
Yong Huang
---------------
哦，总结各位的观点：
1、大于3M的log buffer是为了防止瞬间产生较大的redo log
2、当出现log buffer space等待事件时，需要增大log buffer
3、当出现log file sync等待事件时，需要减小log buffer
我的理解对吗？
-----------------
Point 3 is not that simple. Most log file sync waits are caused by too frequent commits and so reducing log buffer won't have much effect.
With 10g, tuning log buffer becomes less important.
By the way, the reason having a >3M log buffer for instantaneous redo surge is because LGWR doesn't check the 3M threshold until it reaches its timer (3 seconds I think). So "instantaneous" means a very short time much shorter than 3 seconds.
Yong Huang
---------------------
根据以下LGWR写触发条件 ， log buffer设置 0.5m～3m 即可， 主要还是redo log file大小的调整 。
1. When a transaction commits
2. When the redo log buffer becomes one-third full
3. When there is more than a megabyte of changed records in the redo log buffer.
4. When timeout occurs(every 3 seconds)
5. check point
6. Before the DBWn writes modified blocks in the database buffer cache to the data files.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7928987/viewspace-353134/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7928987/viewspace-353134/
扫一扫
613
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
