Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle log_buffer tips


By Burleson Consulting

 

The Oracle log buffer is a small area of RAM where updates (DML) are processed for roll-forward and flashback functionality.

For related redo tuning notes, see here for details on optimal redo log_buffer sizing, and here for overall redo log tuning tips.  For the whole story, see my book "Oracle Tuning: The Definitive Reference".   For more details on log_buffer sizing, see Bug 4930608 and MOSC Note 604351.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."

Note (10gr2 and beyond):  Per MOSC note 351857.1, starting in release 10.2 and beyond, Oracle will automatically size the log_buffer on your behalf and log_buffer cannot be changed dynamically.  The automatic log_buffer sizing is based on the granule size (as determined by to _ksmg_granule_size):

select
   a.ksppinm name,
   b.ksppstvl value,
   a.ksppdesc description
from
   x$ksppi a,
   x$ksppcv b
where
   a.indx = b.indx
and
   a.ksppinm = '_ksmg_granule_size';

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

 Also note that if you are  Oracle's Automatic Memory Management AMM (AMM is Not recommended for some databases), the log_buffer is part of the memory_target algorithm.

A bigger log_buffer can be better

There is some debate about the optimal size for the Oracle redo log buffer, a small region of RAM that initially keeps the "after images" of row updates.  In the 8.1.7 Oracle Reference manual, Oracle suggests keeping the size of the log buffer to a tiny value, under one megabyte:

[Log Buffer] Default value: Operating system specific. Maximum: 500K or 128K * CPU_COUNT, whichever is greater

However, MOSC note 216205.1 Database Initialization Parameters for Oracle Applications 11i, recommends a log_buffer size of 10 megabytes for Oracle Applications, a typical online database:

The log writer parameters control the size of the log buffer within the SGA and how frequently the redo logs are check pointed ( all dirty buffers written to disk to create a new recovery point).

A value of 10MB for the log buffer is a reasonable value for Oracle Applications and it represents a balance between concurrent programs and online users. The value of log_buffer must be a multiple of redo block size, normally 512 bytes.

Today, most large database use a log buffer between 5 meg to 10 meg.  It's important to note that log buffer shortages do not always manifest in the top-5 timed events, especially if there are other SGA pool shortages.  Here is an example of an Oracle 10g database with an undersized log buffer, in this example 512k (This is the database as I found it, and there was a serious data buffer shortage causing excessive disk I/O):

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                        % Total
Event                                 Waits    Time (s)   DB Time     Wait Class
------------------------------ ------------ ----------- --------- -----------
log file parallel write               9,670         291     55.67 System I/O
log file sync                         9,293         278     53.12 Commit
CPU time                                            225     43.12
db file parallel write                4,922         201     38.53 System I/O
control file parallel write           1,282          65     12.42 System I/O

For more notes on optimizing your Oracle redo log buffer size, click here.

Also see:


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.