Oracle Log Buffer -- Considerations Around Buffer Size, Redo Log Groups, and Redo Log Member Sizing
I was recently asked by a colleague "How big should I set my log buffer?"
The answer is, you shouldn't set it at all in Oracle 10g and above, it will be set automatically. See this link to Oracle documentation for more information on log buffers. Oracle Log Buffer Docs
Here's some additional considerations:
Redo log switches are a factor of the size of the redo logs and number of groups AND the log_biuffer. The redo log buffer gets flushed when you commit, it's 1/3 full or every 3 seconds. Any space you allocate to the redo log buffer above and beyond what gets used (based on the above constraints) is wasted log buffer. Therefore, set the redo log buffer to an appropriate (but not excessive) size and then adjust the size of the redo log memober files and the number of log groups to attain a healthy cycle on the redo logs.
To determine what healthy is, size the redo logs so that in addition to the current log, only one other group is ACTIVE. If your groups are too few, or the mmbers to small, you will end up with a situation where all your log files are current or active and you may get a
'private strand flush incomplete'
'Checkpoint not complete'
SELECT * FROM V$LOG;
and you will see output similar (perhaps to this):
niku> set lin 2000 niku> / GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME 1 1 431 1073741824 1 NO INACTIVE 6040091582 03-DEC-10 2 1 432 1073741824 1 NO INACTIVE 6041112547 04-DEC-10 3 1 433 1073741824 1 NO INACTIVE 6041954837 05-DEC-10 4 1 434 1073741824 1 NO CURRENT 6042824300 05-DEC-10 5 1 430 1073741824 1 NO INACTIVE 6039159571 02-DEC-10
In this database we have one CURRENT and no ACTIVE logs at this moment.