Jun 22, 2024
Fixed SGA- Memory Structures

The fixed SGA is a component of the SGA that varies in size from platform to platform and from release to release. It is “compiled” into the Oracle binary itself at installation time (hence the name “fixed”). The fixed SGA contains a set of variables that point to the other components of the SGA, as well as variables that contain the values of various parameters. The size of the fixed SGA is something over which we have no control, and it is generally very small. Think of this area as a “bootstrap” section of the SGA—something Oracle uses internally to find the other bits and pieces of the SGA.

Redo Buffer

The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily, before it is written to disk. Since a memory-to-memory transfer is much faster than a memory-to-disk transfer, the use of the redo log buffer can speed up the database operation. The data will not reside in the redo buffer for very long. In fact, LGWR initiates a flush to disk of this area in one of the following scenarios:

•\ Every three seconds
•\ Whenever a COMMIT or ROLLBACK is issued
•\ When LGWR is asked to switch log files
•\ When the redo buffer gets one-third full or contains 1MB of cached redo log data

Oracle recommends setting the redo log buffer size to a minimum of 8MB. If you’re using flashback functionality and have an SGA greater than 4G, then Oracle recommends setting the log buffer to at least 64MB. If you’re using Data Guard with asynchronous redo transport and have a high redo generation rate, then Oracle recommends setting this to at least 256MB.

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 earlier with a 1.5GB SGA—is shown by the following query:

SQL> select value, isdefault from v$parameter where name = ‘log_buffer’;

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:

SQL> alter system set log_buffer=1 scope=spfile;
SQL> startup force;
SQL> show parameter log_buffer

The smallest log buffer I can really have, regardless of my settings, is going to be 1.680MB on this system.

Note For most database applications, the default value for the LOG_BUFFER parameter is sufficient. If you see a large number of waits associated with the log buffer space event, then consider increasing the LOG_BUFFER parameter.

More Details

Leave a Reply

Your email address will not be published. Required fields are marked *