2 Redo Log Buffer

How to check the Redo Log Buffer size?

V$SGASTAT reports actual memory usage, where V$PARAMETER/show parameter reports the
initial parameter setting.  Base on the below query, it show the difference between show parameter and V$SGASTAT.  What I can find online, there are couple different reasons for the difference

• Guard pages
• Log buffer page headers (16 bytes per log buffer page).

Initial size

show parameter log_buffer

Current size

show sga

select * from v$sgainfo;

select name, bytes from v$sgastat where name = 'log_buffer';

select name, value from v$sga where name = 'Redo Buffers';

How to set the log Buffer size?

Log buffer size is control internal by oracle from 10g and later. Per Meta link note 351857.1, Oracle automatically size the log_buffer. Also, if we use AMM, the log buffer size is part of memory_target algorithm.
If you really need to change the log_buffer, you can still use alter system to change it. The parameter is not dynamic, so we need to set the scope to spfile and bounce the instance to make it effective.

alter system set log_buffer=XXX scope=spfile;

Log block size

Log buffer is divided by continuous serious of blocks. The block size is vary by OS platform. Below Query show the log block size.

select max(lebsz) from sys.x$kccle;

 

When the LGWR write the log buffer to the log file

• Every 3 secs , the LGWR will check the remaining space in the log buffer. if the free space is less than 1/3, the LGWR would write the log buffer to the online redo log file.The “1/3” threshold is control by _LOG_IO_SIZE
• When log buffer has more than 1 MB data
• Online redo log switch

Reference

Log Guard Page: http://www.ixora.com.au/notes/log_buffer_guard_pages.htm

http://www.dbasupport.com/forums/showthread.php?52976-Log-Buffer-Size/page2