The Log_buffer parameter cannot be changed In 10g R2

Nov 27

Posted by

According to Oracle10g R2 Reference Guide LOG_BUFFER parameter is not modifiable any more. This is correct.

Guide is also saying that the default value is: 512 KB or 128 KB * CPU_COUNT, whichever is greater

This information is wrong (and the same documentation error is present in 11g R1 Reference Guide) .

According to Metalink note 351857.1 “The Log_buffer Cannot be Changed In 10g R2”, they changed the way how log buffer is allocated. Oracle 10g R2 combines fixed SGA and redo log buffer. Free space that is left after fixed sized SGA is allocated in the memory granule, Oracle dedicates to LOG_BUFFER.
This is why on all our systems log_buffer is around 14MB (our granule is 16MB, according to _ksmg_granule_size parameter, and fixed size SGA is around 2MB).

SQL> show release
release 1002000300
SQL> show sga

Total System Global Area 9697230848 bytes
Fixed Size                  2070776 bytes
Variable Size            1291847432 bytes
Database Buffers         8388608000 bytes
Redo Buffers               14704640 bytes

I think this is a good move by Oracle. A lot of awkward “rules of thumb” for LOG_BUFFER sizing are now obsolete.

Posted on 27.11.2007, in Oracle and tagged . Bookmark the permalink. 1 Comment.

  1. alesk | 27.11.2007 at 00:10

    ….and a bit different redo buffer size in 11gR1

    SQL> show release
release 1101000600
SQL> 
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
  2  from x$ksppi a, x$ksppcv b
  3  where a.indx = b.indx
  4  and a.ksppinm = '_ksmg_granule_size';

NAME                           VALUE                          DESCRIPTION
------------------------------ ------------------------------ ------------------------------
_ksmg_granule_size             16777216                       granule size in bytes

SQL> show sga

Total System Global Area 1185927168 bytes
Fixed Size                  2114336 bytes
Variable Size             754978016 bytes
Database Buffers          419430400 bytes
Redo Buffers                9404416 bytes

SQL> show parameter cpu_count

NAME                                 TYPE                 VALUE
------------------------------------ -------------------- ------------------------------
cpu_count                            integer              4

    With the same granule as in 10gR2 and with the similar fixed size SGA, the redo log buffer is substantially lower (9MB vs. 14MB). It seems that Oracle changed in some way the rule for buffer log allocation in 11g and/or the number of CPU is still important part of equation, the 10gR2 example instance has 8 CPU’s versus 4 CPU’s on 11g R1 instance.