Re: Redo log buffer size

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 6 Nov 2006 04:35:48 -0800
Message-ID: <1162816548.503815.277260@i42g2000cwa.googlegroups.com>

Niall Litchfield wrote:
> Charles Hooper wrote:
> > Granule size can force the LOG_BUFFER value to 16MB, even if LOG_BUFFER
> > is explicitly set to 1MB in the initialization parameters.
> >
> I think you'll find that only dynamically resizable components of the
> sga are allocated in granules and that LOG_BUFFER does not meet that
> requirement.
>
> regards
>
> Niall Litchfield
> Oracle DBA
> http://www,orawin.info/

I checked a couple of the databases that are running on a Windows 2003 x64 server, and one that is running on Windows 2000 (32 bit, identified as DB 4). One is operating with a log_buffer at 16MB, while the other two are operating with a log_buffer at 8MB. DB 2 does not explicitly specify the log_buffer in the initialization parameters while DB1 and DB 3 do specify log_buffer=2899456 in the spfile. DB 4 specifies log_buffer=1048576 in the spfile, but is apparently set to 6984704 bytes when the instance starts. I tried to make DB 2 consume as little memory as possible on a 64 bit platform, and tried to fit DB 4 into the 2GB per process limit of 32 bit Windows.

====DB 1====
SQL> SELECT VERSION FROM V$INSTANCE; VERSION

10.2.0.2.0

SQL> SHOW SGA Total System Global Area 7348420608 bytes

Fixed Size                  2064928 bytes
Variable Size             855641568 bytes
Database Buffers         6476005376 bytes
Redo Buffers               14708736 bytes

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='log_buffer';

NAME

VALUE

log_buffer
16268800

Inside the spfile:
log_buffer=2899456

====DB 2====
SQL> SELECT VERSION FROM V$INSTANCE; VERSION

10.2.0.2.0

SQL> SHOW SGA Total System Global Area 314572800 bytes

Fixed Size                  2053272 bytes
Variable Size             239078248 bytes
Database Buffers           67108864 bytes
Redo Buffers                6332416 bytes

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='log_buffer';

NAME

VALUE

log_buffer
8134144

spfile does not specify the log_buffer parameter

====DB 3====
SQL> SELECT VERSION FROM V$INSTANCE; VERSION

10.2.0.2.0

SQL> SHOW SGA Total System Global Area 524288000 bytes

Fixed Size                  2054872 bytes
Variable Size             230688040 bytes
Database Buffers          285212672 bytes
Redo Buffers                6332416 bytes

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='log_buffer';

NAME

VALUE

log_buffer
8134144

Inside the spfile:
log_buffer=2899456

====DB 4====
SQL> SELECT VERSION FROM V$INSTANCE; VERSION

10.2.0.2.0

SQL> show sga

Total System Global Area 1157627904 bytes

Fixed Size                  1290748 bytes
Variable Size             452988420 bytes
Database Buffers          696254464 bytes
Redo Buffers                7094272 bytes

SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='log_buffer';

NAME

VALUE

log_buffer
6984704

Inside the spfile:
log_buffer=1048576

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Nov 06 2006 - 06:35:48 CST