Database Reference
In-Depth Information
•
When
LGWR
is asked to switch log files
When the redo buffer gets one-third full or contains 1MB of cached redo log data
•
For these reasons, it will be a very rare system that will benefit from a redo buffer of more than a couple of tens
of megabytes in size. A large system with lots of concurrent transactions might benefit somewhat from a large redo
log buffer because while
LGWR
(the process responsible for flushing the redo log buffer to disk) is writing a portion of
the log buffer, other sessions could be filling it up. In general, a long-running transaction that generates a lot of redo
will benefit the most from a larger than normal log buffer, as it will be continuously filling up part of the redo log
buffer while
LGWR
is busy writing out some of it (we'll cover the phenomenon of writing uncommitted data at length in
Chapter 9). The larger and longer the transaction, the more benefit it could receive from a generous log buffer.
The default size of the redo buffer, as controlled by the
LOG_BUFFER
parameter, varies widely by operating system,
database version, and other parameter settings. Rather than try to explain what the most common default size is (there
isn't such a thing), I'll refer you to the documentation for your release of Oracle (the
Oracle Database Reference
guide).
My default
LOG_BUFFER
—given the instance we just started above with a 1.5GB SGA—is shown by the following query:
EODA@ORA12CR1> select value, isdefault
2 from v$parameter
3 where name = 'log_buffer'
4 /
VALUE ISDEFAULT
-------------------- ---------
7036928 TRUE
The size is about 7MB. The minimum size of the default log buffer is OS-dependent. If you'd like to find out what
that is, just set your
LOG_BUFFER
to 1 byte and restart your database. For example, on my Oracle Linux instance I see
the following:
EODA@ORA12CR1> alter system set log_buffer=1 scope=spfile;
System altered.
EODA@ORA12CR1> connect / as sysdba;
Connected.
SYS@ORA12CR1> startup force;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2288824 bytes
Variable Size 402654024 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7360512 bytes
Database mounted.
Database opened.
SYS@ORA12CR1> show parameter log_buffer
