已索引
Wait until a buffer becomes available.
有4个原因会导致一个会话无法 pin 住一个 buffer，每个原因都有各自对应的等待事件。
- "buffer busy waits": A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.
- "read by other session": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from disk.
- "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.
- "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.
这儿我们以 "buffer busy waits" 为例说一下等待事件处理的通用方法。
在 MOS 中搜索 "buffer busy waits"，我们找到文档 1476043.1。现简单记录一下。
简要定义:
Buffer busy wait happens when a session wants to access a database block in the buffer cache, but it cannot as the buffer is "busy". This indicates that there are some buffers in the buffer cache that multiple processes are attempting to either access concurrently while its being read from disk or waiting for another session's block change to complete.
The two main cases where this can occur are the following:
1.Another session is reading the block into the buffer
2.Another session holds the buffer in an incompatible mode to our request
问题确认:
在 AWR 报告 Report Summary 中的 Top 10 Foreground Events by Total Wait Time 部分，明显比例的 DB time 被花费在该等待事件上。
降低等待：
Identify the segments where buffer busy waits contention occurring using the reference notes.
WAITEVENT: "buffer busy waits" Reference Note (Document 34405.1)
How to Identify The Segment Associated with Buffer Busy Waits (Document 413931.1)。该文档详细讲述了如何定位对象。在AWR报告中，在“Segment Statistics”的“Segments by Buffer Busy Waits”部分列出了the segments with the highest waits。
Identify the block type from v$waitstat or from statspack/awr(section: Buffer Wait Statistics).
在 AWR 报告的 Wait Statistics 下的 Buffer Wait Statistics 部分，可以看出该事件是发生在 segment header、data block 还是 undo block 等哪类对象上。针对不同类型的对象，1476043.1 提供了不同的处理办法。
Freelist blocks/segment header:
Concurrent INSERTs with a suboptimal freelist configuration can lead to buffer busy wait contention as multiple sessions attempt to insert data into the same block (because it appears on the freelist to them). Heavy INSERT activity by concurrent sessions can cause multiple sessions to attempt their insert into the same blocks because automatic segment space management (ASSM) is NOT used, AND there is only a single freelist, too few process freelists, and/or no freelist groups.
The best solution is to use ASSM since it is sometimes tricky to arrive at a correct freelist or freelist group setting. Adding process freelists will help remove contention as each process will map to separate blocks. Freelists can be added at any time without rebuilding the table. Adding freelist groups will also remove contention by mapping processes to other freelists. This is of greatest benefit in RAC environments where the freelist group block itself will be associated with an instance, but will still help in single instance environments as well. The table must be rebuilt to change the freelist group setting.
Data blocks:
Concurrent INSERTs or updates may see contention when a related index has a key that is constantly increasing (e.g., a key based on a sequence number). Index leaf blocks may see contention due to key values that are increasing steadily (using a sequence) and concentrated in a leaf block on the "right-hand side" of the index. Look at using reverse key indexes (if range scans aren't commonly used against the segment). A reverse key index will spread keys around evenly and avoid creating these hot leaf blocks. However, the reverse key index will not be usable for index range scans, so care must be taken to ensure that access is normally done via equality predicates.
Eliminate HOT blocks access from the application. Many concurrent physical reads against the same blocks will result in buffer busy waits as one session gets to do the actual physical read, and the others will be blocked by the buffer busy wait event until the read completes. This is usually an indication that the SQL statement must be tuned. Oracle's SQL Tuning Advisor can help tune specific SQL statements quickly and easily if you are licensed to use the Enterprise Manager Tuning Pack.
Undo header:
Use automatic undo management or add more rollback segments. The waits can be amplified greatly when physical reads are slow due to poor I/O subsystem performance.
处理热块的方法小结：
- segment header --- ASSM
- data block --- ASSM，反向索引
- undo header --- automatic undo management
- undo block --- 增大回滚段
