Oracle 等待事件 buffer busy waits 官方解释，作用，如何使用及优化方法

本站中文解释

Oracle的buffer busy waits事件是指当引入被频繁访问的资源，而又没有足够的缓冲器可以存储所有的资源时会发生的一种等待。当每次访问被命中的缓冲区时，会发生buffer busy waits事件。

这是一种比较常见的事件，当应用程序执行表、索引或者其他的资源操作的时候都有可能发生buffer busy waits事件，此时系统就有可能进入缓冲区忙等待状态。

此类事件一般会发生在某些SQL语句或者存储过程中，比如当添加记录或修改记录时，若要从缓冲区中访问资源；当发生多个事务读取/写入一个缓冲区时，此时也有可能发生buffer busy waits事件；另外，当其他会话读取/更新表、索引时，也有可能发生缓冲区忙等待状态。

buffer busy waits事件一般发生的原因有：其他会话正在读取/更新记录，而没有空的缓冲区来存储；也有可能是缓冲区有多次拷贝会导致发生多个buffer busy waits事件，因为这时系统需要在缓存中拷贝文件；另外，由于长时间读/写某一块缓冲区而且多个读/写操作没有均衡，也可能导致buffer busy waits事件发生。

要解决buffer busy waits事件，首先可以通过增加buffer size来解决；另外可以重新定义表的索引和用正确的索引结构来减少buffer busy waits事件；也可以使用另一种存储表结构来减少对缓冲区的读写；另外，可以尽量减少事务的数量，减少对缓冲区的访问量。

总之，buffer busy waits事件是比较常见的一种事件，虽然它不会导致数据库系统崩溃，但可能会降低系统的性能。因此，我们要采取一些有效的措施来减少buffer busy waits事件，以确保系统可以正常运行。

官方英文解释

Wait until a buffer becomes available.

There are four reasons that a session cannot pin a buffer in the buffer cache, and a separate wait event exists for each reason:

1. “buffer busy waits”: A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.

2. “read by other session”: A session cannot pin the buffer in the buffer cache because another session is reading the buffer from disk.

3. “gc buffer busy acquire”: A session cannot pin the buffer in the buffer cache because another session is reading the buffer from the cache of another instance.

4. “gc buffer busy release”: A session cannot pin the buffer in the buffer cache because another session on another instance is taking the buffer from this cache into its own cache so it can pin it.

Prior to release 10.1, all four reasons were covered by “buffer busy waits.” In release 10.1, the “gc buffer busy” wait event covered both the “gc buffer busy acquire” and “gc buffer busy release” wait events.

Wait Time: Normal wait time is 1 second. If the session was waiting for a buffer during the last wait, then the next wait will be 3 seconds.

See Also:

Oracle Database
Performance Tuning Guide for more information about the potential causes of the buffer busy waits wait event