Oracle database buffer busy Wait reason resolution

Among the many Oracle-related issues, one of the most important is the buffer busy wait (buffer busy waiting) event.

Buffer busy waiting is the most common phenomenon in i/o-bound Oracle systems, especially in the first five busy read (sequential/decentralized) systems reported by Oracle Statspack, such as the first 5 timed events:% 总和事件等待　 时间(s)消逝时间
--------------------------- ------------ ----------- -----------
db文件顺序读 2,5987,146　 48.54
db文件分散读25,5193,246　 22.04
库缓冲区载入死锁6731,3639.26
CPU时间　2,154　9347.83
日志文件平行写　 19,157　8375.68

The primary way to mitigate buffer busy waiting is to reduce I/O in the system, which can be achieved through SQL using less chunk reads (block reads, such as adding indexes). Even for a larger db_cache_size, we can reduce buffer busy waiting time.

In order to be able to view the entire system waiting events, we can look up the v$system_event performance view. This performance view provides the name of the waiting event, the sum of the events and the time, and the average wait time for each event.

You can query the type of buffer that caused the wait through the V$waitstat view. This view lists the wait for each buffer type, and count is the sum of all the waits for the class, and time is the sum of all the waiting times in this category, as follows: select * from v$waitstat;
　类　 COUNT　 TIME
------------------ ---------- ----------
data block19611131870278
segment header　34535 159082
undo header233632　86239
undo block　 1886　 1706

When a session accesses a block of buffers, it is possible to generate a buffer that is busy waiting. The generation of this buffer cache may be caused by the following reasons:

The block may be read to the buffer by other sessions, so the session must wait for the read end of the block.

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

Because buffer busy waiting is caused by competition between different blocks, you can only make judgments by identifying which blocks conflict and conflict, and the corresponding adjustments include identifying and eliminating block competition.

The V$session_wait Performance view provides a way to identify the reason for the wait generation.

The V$session_wait view column represents the buffer busy wait event as follows:

P1-The total number of files associated with the waiting data file.

The number of blocks of data files in the P2-P1.

P3-describes the code that waits for the cause.

Here is an Oracle data dictionary query for these values: select
　 p1 "File #".
　 p2 "Block #",
　 p3 "Reason Code"
from
　 v$session_wait
where
　 event = 'buffer busy waits';