Buffer busy waits

Buffer busy waits

一： B uffer busy waits 说明

二： B uffer bus y waits 解决思路

三： B uffer busy waits 重现过程

四： B uffer busy waits 官方文档

一： B uffer busy waits 说明

--- 参考《 Oracle 内核技术解密》

进程在Buffer Cache中搜索buffer过程(逻辑读过程)：

(1)进程根据要访问的文件号、块号，计算HASH值。

(2)根据HASH值找到HASH Bucket。

(3)搜索Bucket后的链表，查找哪个BH是目标BH(Buffer Header)。

(4)找到目标BH，从中取出Buffer的BA(Buffer Address)。

(5)按BA访问Buffer。

其中：

每个HASH Bucket都保存一个指向Cache Buffers Cache链表(CBC链表)的链表头。

BH(Buffer Header)记录了具体的文件号，块号，BA，BUFFER Pin等信息。

第(3) 步 ) 搜索 Bucket 后的链表需要 Latch 保护。

第(4) 步 ) 访问 BH 中的 BA 需要 Latch 保护。

这个Latch就是Cache Buffer Chain Latch(简称CBC Latch)。

在访问CBC链表之前，先获取CBC Latch，查找对应的BH，并在找到的BH上加Buffer Pin，修改锁的状态(S共享,X独占)，修改完成后就可以进一步访问Buffer。

在开始访问Buffer的时候，CBC Latch已经释放了，Buffer的访问是在Buffer pin锁的保护下完成的。

访问完Buffer后，需要修改Buffer Pin锁，修改Buffer Pin时也需要CBC Latch保护，最后才可以释放Buffer Pin锁。

CBC Latch作用:保护链表，保护BH；

如果在找到BH后，无法马上申请到Buffer Pin时，就会出现 Buffer busy waits等待；

http://bbs.chinaunix.net/thread-3635879-1-1.html

进程之所以无法获得buffer header pin，是因为为了保证数据的一致性，同一时刻一个block只能被一个进程pin住进行存取，因此当一个进程需要存取buffer cache中一个被其他进程使用的block的时候，这个进程就会产生对该block的buffer busy waits事件。

例如：

会话A尝试修改内存中某个Buffer某一行数据时，发现会话B正在修改内存中的相同Buffer不同行数据时，会话A会出现Buffer busy waits等待；

http://www.itpub.net/thread-1801066-1-1.html

如果你的数据库里读极多，写极少，由于各个读之间的buffer pin是兼容的，都是s模式，因此不会产生任何的争用。
如果你的数据库里写极多，读极小，就会产生buffer busy waits等待，但是这种等待的代价比cbc latch的等待代价要小的多，latch的spin机制是非常耗cpu的，而buffer pin的管理本质上类似于enq 锁的机制，没有spin机制，不需要自旋耗费大量的cpu。
如果你的数据库是读写混合的场景，那么写会阻塞读，产生buffer busy waits，但是读不会阻塞写，不会产生这个等待。

Oracle 访问或修改 buffer 步骤：

1）依据数据块的地址计算出数据块所在的bucket
2）获得保护这个bucket的cbc latch
3）在这个链表上找寻我们需要的数据块，找到后，pin这个buffer（读取s，修改x）
4）释放cbc latch
5）读取/修改数据块的内容
6）获取cbc latch
7）unpin这个buffer
8）释放cbc latch

最后我们可以来一个总结了：
1）buffer busy waits是产生在buffer block上的等待，由于n个进程想以不兼容的模式获得buffer block的buffer pin，进而引起buffer busy waits等待。
2）buffer lock的管理模式非常类似enq锁的管理模式，先进先出，有队列去记录锁的拥有者和等待着。
3）写写，读写都会产生buffer busy wiats等待。写写的两个会话，都会产生buffer busy wiaits等待，而读写的两个会话，只有读的session会产生，因为它不能去简单的clone一个内存块，正在发生写的内存块发生克隆是不安全的
4）oracle为了解决cbc latch持有时间过长的问题，以每次访问buffer block的会话获取两次cbc latch，再配合在内存块上加buffer pin来解决这个问题。

说明：oracle并不是针对所有的内存块都采取两次获取cbc latch的机制，比如针对索引root,索引branch，唯一索引的叶子节点，都是采取的一次获取机制。

https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.

https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94465

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently.

二： B uffer busy waits 解决思路

1：查找buffer busy waits对应的对象及对象类型

(1) 查找对象号

SELECT row_wait_obj# FROM V$SESSION WHERE EVENT = 'buffer busy waits';

(2)  通过对象号，查找对应的对象信息

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;

或者通过SID查找对应块号，文件号，类型

select event, sid, p1, p2, p3

  from v$session_wait

 where sid in (69, 75)

   and event like '%buffer busy waits%';

---

P1: File ID

P2: Block ID

P3: Class ID

p1、p2参数和dba_extents进行联合查询得到block所在的segment名称和segment类型

(3) 查找对应的 SQL 信息

select sql_text

  from V$sqlarea

 where (address, hash_value) in

       (select sql_address, sql_hash_value

          from v$session

         where event like '%buffer busy waits%');

或

select sql_text

  from v$sql t1, v$session t2, v$session_wait t3

 where t1.address = t2.sql_address

   and t1.hash_value = t2.sql_hash_value

   and t2.sid = t3.sid

   and t3.event = 'buffer busy waits';

2：按照不同的对象类型，有不同的解决方案

http://bbs.chinaunix.net/thread-3635879-1-1.html

对于不同的等待块类型，我们采取不同的处理办法：
1.data segment header
进程经常性的访问 data segment header通常有两个原因

(1) 获取或修改process freelists信息

进程频繁访问process freelists信息导致freelist争用，我们可以增大相应的segment对象的存储参数freelist或者freelist groups；若由于数据块频繁进出freelist而导致进程经常要修改freelist，则可以将pctfree值和pctused值设置较大的差距，从而避免数据块频繁进出freelist；

(2)扩展高水位标记 

由于该segment空间消耗很快，而设置的next extent过小，导致频繁扩展高水位标记，解决的办法是增大segment对象的存储参数next extent或者直接在创建表空间的时候设置extent size uniform；

2. data block
某一或某些数据块被多个进程同时读写，成为热点块，可以通过如下这些办法来解决这个问题：
(1)降低程序的并发度，如果程序中使用了parallel查询，降低parallel degree，以免多个parallel slave同时访问同样的数据对象而形成等待降低性能；
(2)调整应用程序使之能读取较少的数据块就能获取所需的数据，减少buffer gets和physical reads；
(3)减少同一个block中的记录数，使记录分布于更多的数据块中，这可以通过若干途径实现：可以调整segment对象的pctfree值，可以将segment重建到block size较小的表空间中，还可以用alter table minimize records_per_block语句减少每块中的记录数；
(4)若热点块对象是类似自增id字段的索引，则可以将索引转换为反转索引，打散数据分布，分散热点块；

3.undo segment header
undo segment header争用是因为系统中undo segment不够，需要增加足够的undo segment，根据undo segment的 管理方法，若是手工管理模式，需要修改rollback_segments初始化参数来增加rollback segment，若是自动管理模式，可以减小transactions_per_rollback_segment初始化参数的值来使oracle自动增多rollback segment的数量

4.undo block
undo block争用是由于应用程序中存在对数据的读和写同时进行，读进程需要到undo segment中去获得一致性数据，解决办法是错开应用程序修改数据和大量查询数据的时间

小结：buffer busy waits事件是oracle等待事件中比较复杂的一个，其形成原因很多，需要根据p3参数对照Oracle提供的原因代码表进行相应的诊断，10g以后则需要根据等待的block类型结合引起等待时间的具体SQL进行分析，采取相应的调整措施

一：带有原因码130的数据块(类#1)争用

1)等待集中在数据块上，并且原因码是130，则意味着多个会话并发请求相同的数据块，但该数据块并不在缓冲存储器中，并且必须从磁盘读取。

2)当多个会话请求不在buffer cache中的相同数据块时，ORACLE为了防止每个会话进行相同的操作系统I/O调用。否则，这可能严重地增加系统I/O的数量，所以，ORACLE只允许一个会话执行实际的I/O，而其他的会话在buffer busy waits上等待块，执行I/O的会话在db file sequential read或db file scattered read等待事件上等待。

3)可在v$session视图中检查SESSION的注册时间，并且等待事件db file sequential(scattered) read和buffer busy waits等待相同的文件号和块号。

4)解决方法：优化SQL语句，尽可能地减少逻辑读和物理读；

二： 带有原因码220的数据块(类#1)争用

1)等待集中在数据块上，并且原因码是220，则意味着多个会话同时在相同的对象上执行DML(相同块中的不同行)。

2)如果数据块的尺寸较大(>=16K)，则可能强化这种现象，因为较大的块一般在每个块中包含更多的行。

3)减少这种情况的等待的方法：减少并发；减少块中行的数量；在另一个具有较小块尺寸的表空间中重新构建对象。

4)具体方法说明：

使用较大的PCTFREE重新构建表或索引；

使用alter table  minimize records_pre_block命令改变表以最小化每个块的最小行数

从ORACLE9i开始，可以在另一个具有较小块尺寸的表空间中移动或重新构建对象。

注：虽然这些方法可以最小化buffer busy waits问题，但它们无疑会增加全表扫描时间和磁盘空间利用率。

三： 数据段头(类#4)的争用

1)如果buffer busy waits的等待事件主要集中在数据段头(即表或索引段头，并且不是UNDO段头)上，这意味着 数据库中一些表或索引有高段头活动。

注：进程出于两个主要原因访问段头，一是，获得或修改FREELISTS信息；二是，为了扩展高水位标记(HWM)。

2)减少这种情况的等待的方法：

>> 对使用自由表进行段管理的表，增加确认对象的FREELISTS和FREELIST GROUPS(注：FREELIST GROUPS的增加也是必须的)；

>> 确保FCTFREE和PCTUSED之间的间隙不是太小，从而可以最小化FREELIST的块循环。

>> 下一区的尺寸不能太小，当区高速扩张时，建立的新区需要修改在段头中区映射表。可以考虑将对象移动到合理的、统一尺寸的本地管理的表空间中。

四： 撤销段头(类#17)的争用

1)如果buffer busy waits等待事件主要集中在撤销段头，这表明数据库中的回滚段过少或者是它们的区尺寸太小，从而造成对段头的频繁更新。如果使用ORACLE9I的由数据库系统管理UNDO段，就不需要处理这种问题，因为ORACLE会根据需要增加额外的的UNDO段。

2)可以创建并启用私有回滚段，以减少每个回滚段的事务数量。需要修改init.ora文件中的ROLLBACK_SEGMENTS参数。

3)如果使用公用回滚段可以减少初始化参数transactions_per_rollback_segment的值，ORACLE通过transactions/transactions_per_rollback_segment来获取公有回滚段的最小数量。

五： 撤销块的争用(类#18)

1)如果buffer busy waits等待事件主要集中在撤销块上，这表明有多个并发会话为保证一致性读同时查询更新的数据。

2)这是应用程序存在问题，当应用程序在不同时间内运行查询和DML时，这种问题不会存在。

三： B uffer busy waits 重现过程
---conn chenjch/a

---1  sid=69

declare

    j number;

begin

for i in 1..30000000 loop

    update t1 set id=id+0 where rowid='AAAR4jAAMAAAAh7AAA';

end loop;

end;

/

---2  sid=75

begin

for i in 1..30000000 loop

    update t1 set id=id+0 where rowid='AAAR4jAAMAAAAh7AAB';

end loop;

commit;

end;

/

---查看会话等待事件

select event, sid, p1, p2, p3

  from v$session_wait

 where sid in (69, 75)

   and event like '%buffer%';

select event, sid, p1, p2, p3

  from v$session_wait_history

 where sid in (69, 75)

   and event like '%buffer%';

---判断等待事件访问的块类型

select * from v$waitstat where count >0;

---查看引起‘buffer busy waits’事件的sql语句

select sql_text

  from V$sqlarea

 where (address, hash_value) in

       (select sql_address, sql_hash_value

          from v$session

         where event like '%buffer busy%');

---查看引起‘buffer busy waits’事件的块类型

select 'Segment Header' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  from dba_segments a, v$session_wait b

 where a.header_file = b.p1

   and a.header_block = b.p2

   and b.event = 'buffer busy waits'

union

select 'Freelist Groups' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  from dba_segments a, v$session_wait b

 where a.header_file = b.p1

   and b.p2 between a.header_block + 1 and

       (a.header_block + a.freelist_groups)

   and a.freelist_groups > 1

   and b.event = 'buffer busy waits'

union

select a.segment_type || ' block' class,

       a.segment_type,

       a.segment_name,

       a.partition_name

  from dba_extents a, v$session_wait b

 where a.file_id = b.p1

   and b.p2 between a.block_id and a.block_id + a.blocks - 1

   and b.event = 'buffer busy waits'

   and not exists (select 1

          from dba_segments

         where header_file = b.p1

           and header_block = b.p2);

---查看引起‘buffer busy waits’事件的segment

select owner,segment_name,segment_type,block_id from dba_extents where file_id=6

---查询含有t1的sql

select sql_text from v$sqlarea where sql_text like ‘%t1%’;

SELECT *

  FROM v$segment_statistics s

 WHERE s.statistic_name = 'buffer busy waits'

   AND s.owner = 'CHENJCH';

SELECT row_wait_obj#

  FROM V$SESSION

 WHERE EVENT = 'buffer busy waits';

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;

四： B uffer busy waits 官方文档

https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94465

Wait Event :buffer busy waits

General Area :Buffer cache, DBWR

Possible Causes :

Depends on buffer type. For example, waits for an index block may be caused by a primary key that is based on an ascending sequence.

Look for / Examine :

Examine V$SESSION while the problem is occurring to determine the type of block in contention.

10.3.1  buffer busy waits

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segmentheader, undo header, and undo block.

Check the following V$SESSION_WAIT parameter columns:

P1: File ID

P2: Block ID

P3: Class ID

10.3.1.1  Causes

To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:

SELECT row_wait_obj#

  FROM V$SESSION

 WHERE EVENT = 'buffer busy waits';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;

10.3.1.2  Actions

The action required depends on the class of block contended for and the actual segment.

10.3.1.2.1  segment header

If the contention is on the segment header, then this is most likely free list contention.

Automatic segment-space management in locally managed tablespaces eliminates the need to specify the PCTUSED, FREELISTS, and FREELISTGROUPS parameters. If possible, switch from manual space management to automatic segment-space management (ASSM).

The following information is relevant if you are unable to use ASSM (for example, because the tablespace uses dictionary space management).

A free list is a list of free data blocks that usually includes blocks existing in several different extents within the segment. Free lists are composed of blocks in which free space has not yet reached PCTFREE or used space has shrunk below PCTUSED. Specify the number of process free lists with the FREELISTS parameter. The default value of FREELISTS is one. The maximum value depends on the data block size.

To find the current setting for free lists for that segment, run the following:

SELECT SEGMENT_NAME, FREELISTS

  FROM DBA_SEGMENTS

 WHERE SEGMENT_NAME = segment name

   AND SEGMENT_TYPE = segment type;

Set free lists, or increase the number of free lists. If adding more free lists does not alleviate the problem, then use free list groups (even in single instance this can make a difference). If using Oracle RAC, then ensure that each instance has its own free list group(s).

See Also:

Oracle Database Concepts for information about automatic segment-space management, free lists, PCTFREE, and PCTUSED

10.3.1.2.2  data block

If the contention is on tables or indexes (not the segment header):

Check for right-hand indexes. These are indexes that are inserted into at the same point by many processes. For example, those that use sequence number generators for the key values.

Consider using ASSM, global hash partitioned indexes, or increasing free lists to avoid multiple processes attempting to insert into the same block.

10.3.1.2.3  undo header

For contention on rollback segment header:

If you are not using automatic undo management, then add more rollback segments.

10.3.1.2.4  undo block

For contention on rollback segment block:

If you are not using automatic undo management, then consider making rollback segment sizes larger.

https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

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.

https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=347530962988293&id=34405.1&_afrWindowMode=0&_adf.ctrl-state=gvalc855z_4

WAITEVENT: "buffer busy waits" Reference Note (文档 ID 34405.1)

***Checked for relevance on 14-Jun-2012***

"buffer busy waits" Reference Note

This is a reference note for the wait event  "buffer busy waits" which includes the following subsections:

·  Brief definition

·  Individual wait details  (eg: For waits seen in <>)

·  Systemwide wait details  (eg: For waits seen in <>)

·  Reducing waits / wait times

See  Note:61998.1 for an introduction to Wait Events.

Definition:

·  Versions:7.0 - 10.2  Documentation:  9.0

· This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy". The two main cases where this can occur are:

1. Another session is reading the block into the buffer

2. Another session holds the buffer in an incompatible mode to our request

Individual Waits:

   Parameters:

·  P1 =   file# (Absolute File# in Oracle8 onwards)

·  P2 =   block#

·  P3 =   id (Reason Code)/Block Class# in 10g

file# (Absolute File# in Oracle8 onwards)

This is the file number of the data file that contains the block that the waiting session wants.

block#

This is the block number in the above  file# that the waiting session wants access to.
See  Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair.

id (Reason Code)

The buffer busy wait event is called from different places in the Oracle code. Each place in the code uses a different "Reason Code" . These codes can differ between versions thus:

Buffer Busy Waits ID's and Meanings

  Wait Time:

Normal wait time is 1 second. If the session has been waiting for an exclusive buffer during the last wait then it waits 3 seconds this wait. The session will keep timing-out/waiting until it acquires the buffer.

   Finding Blockers:

Finding the blocking process can be quite difficult as the information required is not externalised. If  P3 (Reason Code) shows that the "buffer busy wait" is waiting for a block read to complete then the blocking session is likely to be waiting on an IO wait (eg: "db file sequential read" or "db file scattered read") for the same  file# and  block#.

If the wait is due to the buffer being held in an incompatible mode then it should be freed very soon. If not then it is advisable to contact Oracle Support and get 3  SYSTEMSTATE dumps at one minute intervals as the blocking session may be spinning. (Look for ACTIVE sessions with high CPU utilisation).

Systemwide Waits:

If the TIME spent waiting for buffers is significant then it is best to determine which segment/s is/are suffering from contention. The "Buffer busy wait statistics" section of the Bstat/estat or STATSPACK reports shows which block type/s are seeing the most contention. This information is derived from <> which can be queried in isolation:

  SELECT time, count, class

    FROM V$WAITSTAT

   ORDER BY time,count

  ;

This shows the  class of block with the most waits at the BOTTOM of the list.

Oracle Support may also request that the following query be run to show where the block is held from when a wait occurs:

  SELECT kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT"

    FROM x$kcbsw s, x$kcbwh w

   WHERE s.indx=w.indx

     and s."OTHER_WAIT">0

   ORDER BY 3

  ;

  Note: "OTHER_WAIT" is "OTHER WAIT" in Oracle8i (a space rather than an underscore)

Additional information regarding which files contain the blocks being waited for can be obtained from the internal <> thus:

  SELECT count, file#, name

    FROM x$kcbfwait, v$datafile

   WHERE indx + 1 = file#

   ORDER BY count

  ;

This shows the file/s with the most waits (at the BOTTOM of the list) so by combining the above of information we know what block type/s in which file/s are causing waits. The segments in each file can be seen using a query like:

  SELECT distinct owner, segment_name, segment_type

    FROM dba_extents

   WHERE file_id= &FILE_ID

  ;

If there are a large number of segments of the type listed then monitoring <> may help isolate which object is causing the waits.
Eg: Repeatedly run the following statement and collect the output. After a period of time sort the results to see which file & blocks are showing contention:

  SELECT p1 "File", p2 "Block", p3 "Reason"

    FROM v$session_wait

   WHERE event='buffer busy waits'

  ;

Note:
In the above query there is no reference to WAIT_TIME as you are not interested in whether a session is currently waiting or not, just what buffers are causing waits.

If a particular block or range of blocks keep showing waits you can try to isolate the object using the queries in  Note:181306.1.

One can also look at:

· Capturing session trace and noting the "buffer busy waits" may help - See  Note:62160.1.

Reducing Waits / Wait times:

As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that "buffer busy waits" for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) - the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk - the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in.

The following hints may be useful for particular types of contention - these are things that MAY reduce contention for particular situations:

Related:

Bug can cause "buffer busy waits" and latch contention in 817/901  Note:176129.1
Tracing User sessions  Note:62160.1 

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！