Resolving Different waits

1.           Resolving Buffer Busy Waits

Problem
Your database is experiencing a high number of buffer busy waits, based on the output from the AWR
report. You want to resolve those waits.
Solution
Oracle has several types of buffer classes, such as data block, segment header, undo header, and undo
block. How you fix a buffer busy wait situation will depend on the types of buffer classes that are causing
the problem. You can find out the type of buffer causing the buffer waits by issuing the following two
queries. Note that you first get the value of row_wait_obj# from the first query and use it as the value for
data_object_id in the second query.

select row_wait_obj#
from v$session
where event = 'buffer busy waits';
------------------------------------------------------------------------------
select owner, object_name, subobject_name, object_type
from dba_objects
where data_object_id = &row_wait_obj;

The preceding queries will reveal the specific type of buffer causing the high buffer waits. Your fix
will depend on which buffer class causes the buffer waits, as summarized in the following subsections.

Segment Header
If your queries show that the buffer waits are being caused by contention on the segment header, there’s
free list contention in the database, due to several processes attempting to insert into the same data
block—each of these processes needs to obtain a free list before it can insert data into that block. If you
aren’t already using it, you must switch from manual space management to automatic segment space
management (ASSM)—under ASSM, the database doesn’t use free lists. However, note that moving to
ASSM may not be easily feasible in most cases. In cases where you can’t implement ASSM, you must
increase the free lists for the segment in question. You can also try increasing the free list groups as well.

Data Block
Data block buffer contention could be related to a table or an index. This type of contention is often
caused by right-hand indexes, that is, indexes that result in several processes inserting into the same point, such as when you use sequence number generators to produce the key values. Again, if you’re
using manual segment management, move to ASSM or increase free lists for the segment.

Undo Header and Undo Block
If you’re using automatic undo management, few or none of the buffer waits will be due to contention
for an undo segment header or an undo segment block. If you do see one of these buffer classes as the
culprit, however, you may increase the size of your undo tablespace to resolve the buffer busy waits.

How it works

A buffer busy wait indicates that more than one process is simultaneously accessing the same data
block. One of the reasons for a high number of buffer busy waits is that an inefficient query is reading
too many data blocks into the buffer cache, thus potentially keeping in wait other sessions that want to
access one or more of those same blocks. Not only that, a query that reads too much data into the buffer
cache may lead to the aging out of necessary blocks from the cache. You must investigate queries that
involve the segment causing the buffer busy waits with a view to reducing the number of data blocks
they’re reading into the buffer cache.
If your investigation of buffer busy waits reveals that the same block or set of blocks is involved most
of the time, a good strategy would be to delete some of these rows and insert them back into the table,
thus forcing them onto different data blocks.
Check your current memory allocation to the buffer cache, and, if necessary, increase it. A larger
buffer cache can reduce the waiting by sessions to read data from disk, since more of the data will
already be in the buffer cache. You can also place the offending table in memory by using the KEEP POOL
in the buffer cache (please see Recipe 3-7). By making the hot block always available in memory, you’ll
avoid the high buffer busy waits.
Indexes that have a very low number of unique values are called low cardinality indexes. Low
cardinality indexes generally result in too many block reads. Thus, if several DML operations are
occurring concurrently, some of the index blocks could become “hot” and lead to high buffer busy waits.
As a long-term solution, you can try to reduce the number of the low cardinality indexes in your
database.
Each Oracle data segment such as a table or an index contains a header block that records
information such as free blocks available. When multiple sessions are trying to insert or delete rows from
the same segment, you could end up with contention for the data segment’s header block.
Buffer busy waits are also caused by a contention for free lists. A session that’s inserting data into a
segment needs to first examine the free list information for the segment, to find blocks with free space
into which the session can insert data. If you use ASSM in your database, you shouldn’t see any waits
due to contention for a free list.

***********************************************************

2.             Resolving Log File Sync Waits

Problem
You’re seeing a high amount of log file sync wait events, which are at the top of all wait events in your
database. You want to reduce these wait events.

Solution
The following are two strategies for dealing with high log file sync waits in your database.

• If you notice a very large number of waits with a short average wait time per wait,
that’s an indication that too many commit statements are being issued by the
database. You must change the commit behavior by batching the commits.
Instead of committing after each row, for example, you can specify that the
commits occur after every 500 rows.

• If you notice that the large amount of wait time accumulated due to the redo log
file sync event was caused by long waits for writing to the redo log file (high
average time waited for this event), it’s more a matter of how fast your I/O
subsystem is. You can alternate the redo log files on various disks to reduce
contention. You can also see if you can dedicate disks entirely for the redo logs
instead of allowing other files on those disks—this will reduce I/O contention
when the LGWR is writing the buffers to disk. Finally, as a long-term solution, you
can look into placing redo logs on faster devices, say, by moving them from a RAID
5 to a RAID 1 device.

How It Works
Oracle (actually the LGWR background process) automatically flushes a session’s redo information to
the redo log file whenever a session issues a COMMIT statement. The database writes commit records to
disk before it returns control to the client. The server process thus waits for the completion of the write
to the redo log. This is the default behavior, but you can also control the database commit behavior with
the COMMIT_WRITE initialization parameter but the COMMIT_WRITE parameter is an advanced parameter that has been deprecated in Oracle Database
11.2. Since it may have an adverse impact on performance, you may want to leave the parameter alone and rely on Oracle’s default commit behavior.

The session will tell the LGWR process to write the session’s redo information from the redo log
buffer to the redo log file on disk. The LGWR process posts the user session after it finishes writing the
buffer’s contents to disk. The log file sync wait event includes the wait during the writing of the log
buffer to disk by LGWR and the posting of that information to the session. The server process will have to
wait until it gets confirmation that the LGWR process has completed writing the log buffer contents out
to the redo log file.
The log file sync events are caused by contention during the writing of the log buffer contents to
the redo log files. Check the V$SESSION_WAIT view to ascertain whether Oracle is incrementing the SEQ# column. If Oracle is incrementing this column, it means that the LGWR process is the culprit, as it may
be stuck.

As the log file sync wait event is caused by contention caused by the LGWR process, see if you
can use the NOLOGGING option to get rid of these waits. Of course, in a production system, you can’t use
the NOLOGGING option when the database is processing user requests, so this option is of limited use in
most cases.
The log file sync wait event can also be caused by too large a setting for the LOG_BUFFER
initialization parameter. Too large a value for the LOG_BUFFER parameter will lead the LGWR process to
write data less frequently to the redo log files. For example, if you set the LOG BUFFER to something like 12MB, it sets an internal parameter, log_io_size, to a high value. The log_io_size parameter acts as a
threshold for when the LGWR writes to the redo log files. In the absence of a commit request or a
checkpoint, LGWR waits until the log_io_size threshold is met. Thus, when the database issues a COMMIT
statement, the LGWR process would be forced to write a large amount of data to the redo log files at
once, resulting in sessions waiting on the log file sync wait event. This happens because each of the
waiting sessions is waiting for LGWR to flush the contents of the redo log buffer to the redo log files.
Although the database automatically calculates the value of the log_io_size parameter, you can specify
a value for it, by issuing a command such as the following:

alter system set "_log_io_size"=1024000 scope=spfile;

System altered.