Buffer Busy Waits in Oracle
Buffer busy waits is directly related to I/O in statspack report its also directly relate to “db file sequential read” and “db file scattered read”
Buffer busy wait happens for two reasons:
1. Another session is reading the block into the buffer
2. Another session holds the buffer in an incompatible mode to our request.
Understanding
In this, when we insert data into the block when it filled with multiple insert are full, then it go to next block of the free list of a table and so on.
When we access data multiple user start reading the table, full scan occurred and one user will actually read the block physically off disk, and cause other user will wait on buffer busy wait for the physical I/O to complete.
Statspack Reports
Top 5 Timed Events
% Total
Event Waits Time(s) Ela Time
------------------------- --------- ---------- --------
db file sequential read 22,598 12,357 38.44
db file scattered read 21,519 7,566 31.74
Solution
For reducing buffer busy waits, need to reduce the I/O on system.
1. For reducing I/O, need to optimize the SQL queries to access rows with fewer block reads.
2. Create index for more optimal read.
3. Adjusting the DB Write process
4. Adding free list in table and indexes
Note: If db_buffer_cache is high then also you get buffer busy waits.
Session Wait
If some block is locked by first session which is incompatible mode of request from second session then second session need to waits for first session.
To get this information, we have v$session_wait performance view
It will let us know what is being waited for and why the wait is occurring.
Query V$session_Wait view: Following three column help to identified the cause:
P1: Returned the file number for the data file involved in the wait.
P2: Blocked Number in file involved in wait.
P3: Reason code describing why the wait is occurring.
select p1 "File #", p2 "Block #",p3 "Reason Code"
from v$session_wait
where event = 'buffer busy waits';
Fetch the segment name and type from following query:
select owner,segment_name,segment_type
from dba_extents
where file_id = &P1
and &P2 between block_id and block_id + blocks -1;
It will provide the table or index name causing problem and for reason code parameter P3 will help.
You need to follow the following table to know the reasons:
| CODE | REASON |
| 0 | Block is being read into buffer cache. |
| 100 | Need to NEW the block, but the block is currently read by another session(most likely for undo). |
| 110 | Need to have CURRENT block either in shared or exclusive, but the block is being read into cache by another session so, we have to wait until it’s read is completed. |
| 120 | Get the block in current mode, but else session is currently reading it into the cache. The solution is to wait for the user to complete the read. This occurs during buffer lookup. |
| 130 | Block is being read by another session, no other suitable block image was found, so one must wait until the read is completed. This may also occur after a buffer cache assumes deadlock. The kernel cannot get a buffer in a certain amount of time and assumes a deadlock; therefore, it will read the CR version of the block. |
| 200 | Need to NEW the block, but someone else is using the current copy; therefore, one has to wait for that user to finish. |
| 210 | The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock, so it does not show up as waiting very long. In this case, the statistic exchange deadlocks is incremented, and the CPU for the buffer deadlock wait event is revealed. |
| 220 | During buffer lookup for a CURRENT copy of a buffer, the buffer has been found, but someone holds it in an incompatible mode, so one has to wait. |
| 230 | The systems is trying to get a buffer in CR/CRX mode, but a modification has started on the buffer that has not yet been completed. |
| 231 | CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed. |
| – | A modification is happening on a SCUR or XCUR buffer but has not yet completed. |
Oracle tasks repeatedly reading the same blocks, as when many Oracle sessions scan the same index.
For resolving each type of contention situations, following are few points:
Undo header contention: Increase the number of rollback segments.
Segment header contention: Increase the number of freelists and use multiple freelist groups, which can make a difference even within a single instance.
Freelist block contention: Increase the freelists value. Also, when using Oracle Parallel Server or Real Application Clusters, one must be certain that each instance has its own freelist groups.
Data block contention: Identify and eliminate hot blocks from the application via changing pctfree and or pctused values to reduce the number of rows per data block. Check for repeatedly scanned indexes. Since each transaction updating a block requires a transaction entry, we might increase the initrans value.