20280523-Buffer busy waits

chenoracle

于 2024-12-04 21:49:53 发布

阅读量973

点赞数 15

文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/144250740

版权

在这里插入图片描述

一： Buffer busy waits 说明
二： Buffer bus y waits 解决思路
三： Buffer busy waits 重现过程
四： Buffer busy waits 官方文档

一： Buffer 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.

二： Buffer 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时，这种问题不会存在。

三： Buffer 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;

在这里插入图片描述

四： Buffer 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

欢迎关注我的微信公众号"IT小Chen"

20280523-Buffer busy waits

一： Buffer busy waits 说明

二： Buffer busy waits 解决思路

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

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

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

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

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

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

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

三： Buffer busy waits 重现过程

四： Buffer busy waits 官方文档

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

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

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

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