Friday, October 26, 2012

3 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


Log Guard Page:


  1. Rolex offers a wide range replica rolex cellini watches to suit any wrist. Explore the Rolex collection by selecting your favourite models, materials, bezels, dials and bracelets to find the watch that was made for you.

  2. Cartier is the renowned replica watches French jeweller and watchmaker with a history of pioneering replica cartier tank francaise watches design and a drive for excellence. The first references to Cartier wristwatches date back to 1888. Late in the 19th century it was pocket and chatelaine watches that were most popular, but Louis Cartier saw a future in timepieces that could be worn on the wrist.

  3. Following up on our earlier article, cheap arsenal jerseys we can today give you a first full look at thecheap club jerseys England Euro 2020 home kit by Nike.Also check out the England home kit history for an detailed overview of how the England shirt has evolved through the years.This is the Nike England Euro 2020 home jersey.



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates