Friday, October 26, 2012

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

image

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';

image

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;

image

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;

 

image

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

2 comments:

  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.

    ReplyDelete
  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.

    ReplyDelete

 

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