Why Oracle Database buffer is busy waiting

Among the many Oracle problems, the most important one is the buffer busy wait (buffer busy wait) event.

Buffer busy waiting is the most common phenomenon in the I/O-bound Oracle system, especially in the first five busy (sequential/distributed) read systems in the Oracle STATSPACK report, for example, the first five scheduled events:

% Total event wait time (s) elapsed time --------------------------- -------------- --------------- db file sequential read 146, 48.54db file distributed read, 246 22.04 database buffer loading deadlock 6731,363 9.26CPU time 2,154 9347.83 parallel write of log files 19,157 8375.68

　

The main way to reduce the buffer wait is to reduce I/O in the system, which can be achieved through SQL using less block reads (block reads, such as adding indexes. Even for a relatively large db_cache_size, we can reduce the busy waiting time of the buffer zone.

To view the wait events of the entire system, we can refer to the v $ system_event performance view. This performance view provides the name of the wait event, the sum of wait events and time, and the average wait time for each event.

You can use the v $ waitstat view to query the type of the buffer that causes waiting. This view lists the Waits of each buffer type. COUNT is the total waiting of all classes, and TIME is the total waiting TIME of all classes, as shown below:

Select * from v $ waitstat; Class count time ------------------ ---------- data block19611131870278segment header 34535 159082 undo header233632 86239 undo block 1886

　

When a session accesses a buffer block, a buffer wait may occur. This busy waiting in the buffer zone may be caused by the following reasons:

The block may be read to the buffer by other sessions, so the session must wait until the reading of the block ends.

The session may have a buffer block that is inconsistent with the waiting session query.

Because the busy waiting in the buffer zone is caused by competition between different specific blocks, you can only make a judgment by identifying the causes of block conflicts and conflicts, adjustments include identifying and eliminating the cause of block competition.

The v $ session_wait performance view provides a way to identify the cause of the wait.

The column in the v $ session_wait view represents the following buffer busy wait events:

P1-Total number of waiting-related data files.

The number of data files in the P2-P1.

P3-code that describes the reason for waiting.

Here is an Oracle Data Dictionary query for these values:

select　 p1 "File #".　 p2 "Block #",　 p3 "Reason Code"from　 v$session_waitwhere　 event = 'buffer busy waits';

　

If the preceding query results show that a block is waiting, the following query displays the name and type of this block:

select 　 owner,　 segment_name,　 segment_typefrom 　 dba_extentswhere 　 file_id = &P1and 　&P2 between block_id and block_id + blocks -1;

　

Once this part is identified, the v $ segment_statistics performance view enables real-time monitoring of block horizontal statistics. This process allows DBA to identify issues related to the index of the Independent list.

You can also query dba_data_files to determine the file_name of the file involved in the waiting. The method is to use P1 in v $ session_wait.

Query the value of P3 (cause code) from v $ session_wait to find out the reason for the session waiting. The cause code ranges from 0 to 300 and can be decoded.

Changes that are generated in a SCUR or XCUR buffer and are not terminated.

0 blocks are read into the buffer.

100 we want to create a NEW block, but this block is currently read by another session.

110 we want to set the current block as shared, but this block is read by another session, so we have to wait until read () ends.

120 we want to obtain the current block, but other people have read this block into the buffer zone, so we can only wait for others' reading to end.

130 blocks are read by another session and no other coordinated blocks are found. Therefore, we must wait until the reading ends. This situation may also occur after a buffer deadlock. Therefore, the CR of the block must be read.

200 we want to create a new block, but others are using it, so we have to wait for others to finish using it.

210 The Session wants to read the blocks in SCUR or XCUR. If the block switching or session is in discontinuous TX mode, it may take a long time to wait.

220 query the current version of a block in the buffer zone, but someone uses this block in invalid mode, so we can only wait.

230 obtain a block in CR/CRX mode, but the change in the block starts and does not end.

231 CR/CRX scan found the current block, but the changes in the block started and did not end.

Cause code

As I said at the beginning, busy waiting in the buffer zone is the most common phenomenon in the I/O bound system. I/O competition caused by data block waiting is usually caused by multiple sessions repeatedly reading the same block when the same index is scanned. In this case, session 1 quickly scans the buffer blocks and then reads the blocks from the disk. When session 1 is waiting for the completion of Disk Read, other blocks scan for the same index and capture session 1 quickly, and want to read the same block from the disk. As a result, the buffer is busy waiting.

The following rules help resolve the situations mentioned when the competition is in progress:

Data block competition-identify and remove HOT blocks in programs by changing the value of PCTFREE or PCTUSED to reduce the number of data blocks.

Freelist block competition-increase the FREELISTS value. When using the Parellel server, make sure that each case has its own FREELIST GROUPs.

Segment header contention-add FREELISTS value and use FREELIST GROUPs.

Undo header block-increase the number of rollback segments.

Benefits

It is complicated and tricky to identify and solve the buffer wait. Oracle provides the v $ segment_statistics view to help monitor the busy waiting of the buffer zone. When you are able to correctly identify and correct the cause of the buffer wait, your efforts will be rewarded.

(