Re: Redo log buffer size

oracle dba wrote:
> Hi all,
>
> According to books redo will to written to file after 1MB is full. So
> is there any way that our redo log buffer is equal to or more than
> 10MB.
> If yes then why ..........................
> if u know any metalink addr pls inform

Granule size can force the LOG_BUFFER value to 16MB, even if LOG_BUFFER is explicitly set to 1MB in the initialization parameters.

A quick search for granule size:
http://www.oracle.com/pls/db102/search?remark=quick_search&word=granule+size&tab_id=

The first link on the search results leads to the Oracle Database Administrator's Guide 10g Release 2:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm A quote from that documentation:
"Components and Granules in the SGA
The SGA comprises a number of memory components, which are pools of memory used to satisfy a particular class of memory allocation requests. Examples of memory components include the shared pool (used to allocate memory for SQL and PL/SQL execution), the java pool (used for java objects and other java execution memory), and the buffer cache (used for caching disk blocks). All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size. Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. Some platform dependencies may arise. For example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details.

You can query the V$SGAINFO view to see the granule size that is being used by an instance. The same granule size is used for all dynamic components in the SGA.

If you specify a size for a component that is not a multiple of granule size, Oracle Database rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, the database actually allocates 12 MB."

A search of LOG_BUFFER finds this page:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b25159/configbp.htm "2.2.2.7 Set LOG_BUFFER to At Least 8 MB For large production databases, set the LOG_BUFFER initialization parameter to a minimum of 8 MB. This setting ensures the database allocates maximum memory (typically 16 MB) for writing Flashback Database logs."

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i29756 "Server processes making changes to data blocks in the buffer cache generate redo data into the log buffer. LGWR begins writing to copy entries from the redo log buffer to the online redo log if any of the following are true: * The log buffer becomes one third full. * LGWR is posted by a server process performing a COMMIT or ROLLBACK. * DBWR posts LGWR to do so."
"On most systems, sizing the log buffer larger than 1M does not provide any performance benefit. Increasing the log buffer size does not have any negative implications on performance or recoverability. It merely uses extra memory."

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Nov 03 2006 - 21:00:27 CST