Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 5 March 2013

Contention Problem: Buffer Busy Waits/Log Buffer Space Wait

Check wait in some buffers in the buffer cache that multiple processes are attempting to access concurrently. If the contention is on 'data block' check for:
Select class, count, time
    From v$waitstat
    Where class in ('data block', 'undo header', 'undo block', 'segment header');
        SQL statements using unselective indexes.
        Consider using automatic segment-space management or increase free-lists.
Select event, total_waits, time_waited
    From v$system_event
    Where event in ('buffer busy waits');
Locally managed tablespaces (LMT) and automatic segment space management (ASSM) provide a new way to manage ‘freelists’ for individual objects in a database.
Without multiple freelists, every Oracle table and index had a single data block at the head of the table to manage free blocks for the object and provide data blocks for new rows created by any SQL insert statements. A buffer busy wait occurs when a data block is inside the data buffer cache but is unavailable because it is locked by another DML transaction.  When you want to insert multiple tasks into the same table, the tasks are forced to wait while Oracle assigned free blocks, one at a time. The below screen shot shows ‘freelists’ the table description.
With ASSM, Oracle claims to improve the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously, eliminating serialization for free space lookups. According to Oracle benchmarks, using bitmap freelists removes all segment header contention and allows for super-fast concurrent insert operations.
If the contention is on 'undo header'
        Consider using automatic segment-space management or add more rollback segments.
If the contention is on 'undo block'
        Consider using automatic segment-space management or make rollback segment sizes larger.
Select s.segment_name, s.segment_type, s.freelists, w.wait_time, w.seconds_in_wait, w.state
    from  dba_segments s, v$session_wait w
    where  w.event = 'buffer busy waits'
    and  w.p1 = s.header_file and  w.p2 = s.header_block;
If the contention is on 'segment header'
        Look for the segment and consider increase free-lists.
Check for waits to find a free buffer in the buffer cache and check if the I/O system is slow.
select    event, total_waits, time_waited
    from    v$system_event
    where    event in ('free buffer waits');
        Consider increase the size of the buffer cache if it is so small.
        Consider increase the number of DBWR process if the buffer cache is properly sized.
Log Buffer Space Wait
Log Buffer Space wait event occurs when server processes write data into the log buffer faster than the LGWR process can write it out.
The LGWR process begins writing entries to the online redo log file if any of the following conditions are true:
        The log buffer reaches the _log_io_size threshold. By default, this parameter is set to one third of the log buffer size.
        A server process performing a COMMIT o ROLLBACK posts to the LGWR process.
        The DBWR process posts to the LGWR process before it begins writing.
As the LGWR process writes entries to disk, user processes can reuse the space in the log buffer for new entries. If the Log Buffer is too small, user processes wait for Log Buffer Space until the LGWR flushes the redo information in memory to disk.
Oracle's default setting for LOG_BUFFER is the maximum size of 512k (.5m). Most of the time, the default setting is appropriate. If your applications insert, update or delete large amounts of data within the database, and user processes experience many Log Buffer Space waits, you may want to consider increasing the LOG_BUFFER parameter. However, careful analysis should be performed when changing this parameter. A LOG_BUFFER sized too large will cause the _log_io_size threshold also to be large. This may cause the LGWR process to write more data less often, causing yet another log-related Oracle wait event called Log File Sync.
For example, a LOG_BUFFER size of 10m will result in a _log_io_size threshold of over 3m. Therefore, the LGWR process will not begin writing until the 3m threshold is met, a checkpoint happens, or a commit or rollback is issued. If a commit or rollback is issued, the LGWR may take longer to write all the data from the buffer (up to last commit mark), especially if it hasn't yet reached the larger 3m threshold. The larger write at commit time, will result in individual processes waiting on the log file sync event. The log file sync wait event occurs when processes commit or rollback, then wait for the information to be flushed from the log buffer (memory) to the online redo log file (disk).
In fact there should be no log buffer space waits. Consider making the log buffer bigger if it is small or consider moving log files to faster disks such as striped disks.
Select    event, total_waits, total_timeouts, time_waited, average_wait
    from    v$system_event
    where    event = 'log buffer space';
Select    sid, event, seconds_in_wait, state
    from    v$session_wait
    where    event = 'log buffer space';
Select    name, value
    from     v$sysstat
    where    name in ('redo log space requests');
The pct_buff_alloc_retries should be zero or less than 0.01 (< 1%). If it is greater consider making the log buffer bigger. If it is greater consider moving the log files to faster disks such as striped disks.
Select v1.value as redo_buff_alloc_retries, v2.value as redo_entries,
          trunc(v1.value/v2.value,4) as pct_buff_alloc_retries
         from v$sysstat v1, v$sysstat v2
           where = 'redo buffer allocation retries'
           and = 'redo entries';
If there are log file switch waits because of log switches, it indicates disk I/O contention. Check that the redo log files are stored on separated and fast devices or consider increase the size of the redo log files.
Select event, total_waits, time_waited, average_wait
    from v$system_event
    where event like 'log file switch completion';
Check the number of checkpoints incompletes. Check the frequency of checkpoints in LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT, FAST_START_MTTR_TARGET. Check the size, number and location of the redo log groups.

Select event, total_waits, time_waited, average_wait
    From v$system_event
    Where event like 'log file switch (checkpoint incomplete)';
If log buffer switches waits for archive processes. Consider move archived log to faster devices. Consider the use of multiple archiver processes with LOG_ARCHIVE_MAX_PROCESSES initialization parameter.
Select event, total_waits, time_waited, average_wait
    From v$system_event
    Where event like 'log file switch (archiving needed)';
When sizing the LOG_BUFFER produces no additional benefit, you may want to look at other tuning opportunities to reduce the Log Buffer Space wait event.
Poor I/O performance:
The Log Buffer Space wait event could be an indication of slow disks and/or slow log file switches. Investigate moving the log files to faster disks such as file system direct I/O or raw devices. Also, look at improving the check pointing or archiving process for better overall throughput.
Application design:
If your application performs many commits or has heavy logging activity, try reducing the activity by using the NOLOGGING option where appropriate. Also, look for poor application behavior, such as, fetching and updating all columns in a table when only a few columns need changed.
Materialized views:
When using materialized views, verify that appropriate methods of refresh have been chosen. If possible, perform fast refreshes instead of complete refreshes. If using 10g and performing complete refreshes, review the setting of the ATOMIC_REFRESH parameter on the refresh statement. In 10g, the default changed to DELETE FROM the materialized view (causing redo creation) instead of using the TRUNCATE command (a nologging operation).
Note: If any of the scripts not working try to type again, this is due to word document or check the other post for same script. Click for More related post:

Tuning Oracle 9i/10g SGA Parameters

Scripts: Disk I/O, Events, Waits (Contention issues)



How to Tune Oracle 10g Database

Oracle 9i DBServer Tuning

DB Monitoring & Performance Script

Database Health Check Scripts

Dynamic Performance view for Tuning


  1. DreamHost is ultimately one of the best hosting company with plans for all of your hosting requirments.

  2. Looking for the Ultimate Dating Site? Create an account to find your perfect date.

  3. Quantum Binary Signals

    Professional trading signals sent to your mobile phone every day.

    Start following our signals NOW and profit up to 270% per day.